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',13245323345);
+-------------------------------------------------------------------+
| json_object('names','David','adress','Beijing','Tel',13245323345) |
+-------------------------------------------------------------------+
| {"Tel": 13245323345, "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,13240133398); #如果键值对中的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> 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": 15046464563, "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": 15046464563, "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
