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