mysql json_object排序_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