mysql中json_object函数的使⽤？
需求说明:
今天看了json_object函数的使⽤，在此记录下使⽤过程
操作过程:
1.使⽤json_object函数将⼀个键值对列表转换成json对象
mysql> select json_object('names','David'); #将⼀个键值对转换成json对象
+------------------------------+
| json_object('names','David') |
+------------------------------+
| {"names": "David"} |
+------------------------------+
1 row in set (0.00 sec)
mysql> select json_object('names','David','adress','Beijing'); #将两个键值对转换成json对象
+-------------------------------------------------+
| json_object('names','David','adress','Beijing') |
+-------------------------------------------------+
| {"names": "David", "adress": "Beijing"} |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_object('names','David','adress','Beijing','Tel',132********);
+-------------------------------------------------------------------+
| json_object('names','David','adress','Beijing','Tel',132********) |
+-------------------------------------------------------------------+
| {"Tel": 132********, "names": "David", "adress": "Beijing"} |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_object('names','David','adress','Beijing','Tel'); #json_object中的参数如果是奇数个也会报错
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'
mysql> select json_object('names','David','adress','Beijing',NULL,132********); #如果键值对中的key是NULL则会报错
ERROR 3158 (22032): JSON documents may not contain NULL member names.
mysql> select json_object('names','David','adress','Beijing','Tel',1324053333,'names','Mike'); #如果键值对中，存在多个key值相同，那么后⾯的key就会被丢弃，只保留第⼀个出现的key +---------------------------------------------------------------------------------+
| json_object('names','David','adress','Beijing','Tel',1324053333,'names','Mike') |
+---------------------------------------------------------------------------------+
| {"Tel": 1324053333, "names": "David", "adress": "Beijing"} |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_object('names','David', 'adress','Beijing','Tel',1324053333,'names','Mike'); #如果key,values之间有空格也会将空格丢弃，主要是为了提升查的性能
+-------------------------------------------------------------------------------------+
| json_object('names','David', 'adress','Beijing','Tel',1324053333,'names','Mike') |
+-------------------------------------------------------------------------------------+
| {"Tel": 1324053333, "names": "David", "adress": "Beijing"} |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql中selectmysql> select json_object('names','David', 'adress', 'Beijing','Tel',1324053333,'names','Mike');
+-----------------------------------------------------------------------------------------+
| json_object('names','David', 'adress', 'Beijing','Tel',1324053333,'names','Mike') |
+-----------------------------------------------------------------------------------------+
| {"Tel": 1324053333, "names": "David", "adress": "Beijing"} |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_object('names','David', 'adress', 'Beijing','Tel',1324053333, 'names','Mike');
+--------------------------------------------------------------------------------------------+
| json_object('names','David', 'adress', 'Beijing','Tel',1324053333, 'names','Mike') |
+--------------------------------------------------------------------------------------------+
| {"Tel": 1324053333, "names": "David", "adress": "Beijing"} |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
备注:json_object函数的作⽤，就是将⼀列键值对转换为json对象，同时呢，如果是奇数个参数,key为NULL都会报错，如果有多个key，相同的，则会将后⾯的key给丢弃掉，即使两个key对应的value不同.
2.使⽤json_object向表中插⼊数据
mysql> insert into tab_json values (null,json_object('names','David','adress','Beijing','Tel',1324053333,'names','Mike'));
Query OK, 1 row affected (0.01 sec)
mysql> select * from tab_json;
+----+--------------------------------------------------------------------------+
| id | data |
+----+--------------------------------------------------------------------------+
| 1 | {"tel": 150********, "passcode": "654567"} |
| 2 | {"tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} |
| 3 | {"Tel": 1324053333, "names": "David", "adress": "Beijing"} |
+----+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)
3.基于⼀个表的数据，向json字段插⼊值
mysql> create table info(name varchar(30),address varchar(20),tel int); #创建⼀个普通表,⽤于存放基础信息
Query OK, 0 rows affected (0.08 sec)
mysql> insert into info values ('Jack','Zhongguo',1323394);
Query OK, 1 row affected (0.00 sec)
mysql> insert into info values ('Tobbo','Meiguo',132333394);
Query OK, 1 row affected (0.01 sec)
mysql> insert into info values ('Hnana','Riben',3403234);
Query OK, 1 row affected (0.01 sec)
mysql> select * from info;
+-------+----------+-----------+
| name | address | tel |
+-------+----------+-----------+
| Jack | Zhongguo | 1323394 |
| Tobbo | Meiguo | 132333394 |
| Hnana | Riben | 3403234 |
+-------+----------+-----------+
3 rows in set (0.00 sec)
mysql> insert into tab_json select null,json_object('name',name,'address',address,'tel',tel) from info; #将info表中的数据通过json_object转换成json类型Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tab_json;
+----+--------------------------------------------------------------------------+
| id | data |
+----+--------------------------------------------------------------------------+
| 1 | {"tel": 150********, "passcode": "654567"} |
| 2 | {"tel": 189776542, "name": "David", "olds": "12", "address": "Hangzhou"} |
| 3 | {"Tel": 1324053333, "names": "David", "adress": "Beijing"} |
| 4 | {"tel": 1323394, "name": "Jack", "address": "Zhongguo"} |
| 5 | {"tel": 132333394, "name": "Tobbo", "address": "Meiguo"} |
| 6 | {"tel": 3403234, "name": "Hnana", "address": "Riben"} |
+----+--------------------------------------------------------------------------+
6 rows in set (0.00 sec
⽂档创建时间:2018年6⽉6⽇15:43:19
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论