阅读数:248 评论数:0

一、概述

二、JSON字段的增删改查

1、插入操作

（1）可以直接插入json格式的字符串。

mysql> create table t(c1 json);
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into t values('[1, "abc", null, true, "08:45:06.000000"]');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t values('{"id": 87, "name": "carrot"}');
Query OK, 1 row affected (0.01 sec)

（2）也可以使用函数json_object，注意key和value之间是逗号，而不是冒号。

insert into user_test(user_name, details) values
('lily', JSON_OBJECT("phone","18500001111", "sex",1,"age",46,"email","1233@qq.com", "address",JSON_OBJECT("country","CN","province","上海市","city","上海市"))),
('jack', JSON_OBJECT("phone","18600001111", "sex",0,"age",6,"email","123@163.com", "address",JSON_OBJECT("country","CN","province","上海市","city","上海市"))),
('andi', JSON_OBJECT("phone","18600001111", "sex",0,"age",15,"email","123@qq.com", "address",JSON_OBJECT("country","CN","province","浙江省","city","杭州市"))),
('curre',JSON_OBJECT("phone","118800001111", "sex",1,"age",24,"email","123@qq.com", "address",JSON_OBJECT("country","CN","province","广东省","city","广州市"))),
('clid', JSON_OBJECT("phone","115600001111", "sex",0,"age",19,"email","123@qq.com", "address",JSON_OBJECT("country","CN","province","广东省","city","深圳市"))),
('scout',JSON_OBJECT("phone","13800001111", "sex",1,"age",70,"email","123@qq.com", "address",JSON_OBJECT("country","CN","province","福建省","city","厦门市"))),
('kk', JSON_OBJECT("phone","16600001111", "sex",1,"age",30,"email","123@qq.com")),
('lulu', JSON_OBJECT("phone","16600001111", "sex",1,"age",30,"email","123@qq.com","address",JSON_OBJECT("country","CN","province","湖北省","city","武汉市")));

2、查询操作

（1）json_extract(json_doc,path[,path]...)

其中，json_doc 是 JSON 文档，path 是路径。该函数会从 JSON 文档提取指定路径（path）的元素。如果指定 path 不存在，会返回 NULL。可指定多个 path，匹配到的多个值会以数组形式返回。

首先看json数组

数组的路径是通过下标来表示的，第一个元素的下标是0。

mysql> select json_extract('[10, 20, [30, 40]]', '$[0]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]') |
+--------------------------------------------+
| 10                                         |
+--------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]','$[2][0]');
+--------------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]','$[2][0]') |
+--------------------------------------------------------------+
| [10, 20, 30]                                                 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

除此之外，还可通过[M to N]获取数组的子集。

mysql> select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
+-------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0 to 1]') |
+-------------------------------------------------+
| [10, 20]                                        |
+-------------------------------------------------+
1 row in set (0.00 sec)
 
# 这里的 last 代表最后一个元素的下标
mysql> select json_extract('[10, 20, [30, 40]]', '$[last-1 to last]');
+---------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[last-1 to last]') |
+---------------------------------------------------------+
| [20, [30, 40]]                                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)

也可通过[*]获取数组中的所有元素。

mysql> select json_extract('[10, 20, [30, 40]]', '$[*]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[*]') |
+--------------------------------------------+
| [10, 20, [30, 40]]                         |
+--------------------------------------------+
1 row in set (0.00 sec)

接下来，看看对象

对象的路径是通过key来表示的。

mysql> set @j='{"a": 1, "b": [2, 3], "a c": 4}';
Query OK, 0 rows affected (0.00 sec)
 
# 如果 KEY 在路径表达式中不合法（譬如存在空格），则在引用这个 KEY 时，需用双引号括起来。
mysql> select json_extract(@j, '$.a'), json_extract(@j, '$."a c"'), json_extract(@j, '$.b[1]');
+-------------------------+-----------------------------+----------------------------+
| json_extract(@j, '$.a') | json_extract(@j, '$."a c"') | json_extract(@j, '$.b[1]') |
+-------------------------+-----------------------------+----------------------------+
| 1                       | 4                           | 3                          |
+-------------------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

除此之外，还可通过.*获取对象中的所有元素。

mysql> select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');
+--------------------------------------------------------+
| json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*') |
+--------------------------------------------------------+
| [1, [2, 3], 4]                                         |
+--------------------------------------------------------+
1 row in set (0.00 sec)
 
# 这里的 $**.b 匹配 $.a.b 和 $.c.b
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

（2）column->path（查询出来的value有双引号）

包括后面的的column->>path，都是语法糖，在实际使用的时候都会转化为JSON_EXTRACT。

可以使用$符号来代替整个json值。

column->path等同于JSON_EXTRACT(column,path)，只能指定一个path。

create table t(c2 json);
 
insert into t values('{"empno": 1001, "ename": "jack"}'), ('{"empno": 1002, "ename": "mark"}');
 
mysql> select c2, c2->"$.ename" from t;
+----------------------------------+---------------+
| c2                               | c2->"$.ename" |
+----------------------------------+---------------+
| {"empno": 1001, "ename": "jack"} | "jack"        |
| {"empno": 1002, "ename": "mark"} | "mark"        |
+----------------------------------+---------------+
2 rows in set (0.00 sec)
 
mysql> select * from t where c2->"$.empno" = 1001;
+------+----------------------------------+
| c1   | c2                               |
+------+----------------------------------+
|    1 | {"empno": 1001, "ename": "jack"} |
+------+----------------------------------+
1 row in set (0.00 sec)

（3）column->>path（查询出来的value没有双引号）

以下三者是等价的。

JSON_UNQUOTE(JSON_EXTRACT(column,path))

JSON_UNQUOTE(column->path)

column->>path

mysql> select c2->'$.ename',json_extract(c2, "$.ename"),json_unquote(c2->'$.ename'),c2->>'$.ename' from t;
+---------------+-----------------------------+-----------------------------+----------------+
| c2->'$.ename' | json_extract(c2, "$.ename") | json_unquote(c2->'$.ename') | c2->>'$.ename' |
+---------------+-----------------------------+-----------------------------+----------------+
| "jack"        | "jack"                      | jack                        | jack           |
| "mark"        | "mark"                      | mark                        | mark           |
+---------------+-----------------------------+-----------------------------+----------------+
2 rows in set (0.00 sec)

（4）JSON_INSERT(json_doc,path,val[,path,val]...)

仅当指定位置或指定key的值不存在时，才执行插入操作。另外，如果指定的path是数组下标，且json_doc不是数组，该函数首先将json_doc转化为数组，然后再插入新值。

mysql> select json_insert('1','$[0]',"10");
+------------------------------+
| json_insert('1','$[0]',"10") |
+------------------------------+
| 1                            |
+------------------------------+
1 row in set (0.00 sec)
 
mysql> select json_insert('1','$[1]',"10");
+------------------------------+
| json_insert('1','$[1]',"10") |
+------------------------------+
| [1, "10"]                    |
+------------------------------+
1 row in set (0.01 sec)
 
mysql> select json_insert('["1","2"]','$[2]',"10");
+--------------------------------------+
| json_insert('["1","2"]','$[2]',"10") |
+--------------------------------------+
| ["1", "2", "10"]                     |
+--------------------------------------+
1 row in set (0.00 sec)
 
mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select json_insert(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
+----------------------------------------------------+
1 row in set (0.00 sec)

（5）json_set(json_doc,path,val[,path,val]...)

替换已经存在的值，如果指定的位置或指定的key的值不存在，会执行插入操作，如果存在，则执行更新操作。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select json_set(@j, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------+
| json_set(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}    |
+-------------------------------------------------+
1 row in set (0.00 sec)

上面使用的是select并不会更新数据库中的字段，如果要更新数据库中的字段，需要使用update语句：

update t_json	set content = JSON_SET(content,'$.id',1);
update t_json	set content = JSON_SET(content,'$.name','jack');
update t_json	set content = JSON_SET(content,'$.wife',JSON_OBJECT('name','zhang','age',24));

（6）json_replace(json_doc,path,val[,path,val]...)

替换已经存在的值。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select json_replace(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| json_replace(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}                              |
+-----------------------------------------------------+
1 row in set (0.00 sec)

（7）json_remove(json_doc,path[,path]...)

删除json文档指定位置的元素。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select json_remove(@j, '$.a');
+------------------------+
| JSON_REMOVE(@j, '$.a') |
+------------------------+
| {"b": [2, 3]}          |
+------------------------+
1 row in set (0.00 sec)
 
mysql> set @j = '["a", ["b", "c"], "d", "e"]';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select json_remove(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d", "e"]         |
+-------------------------+
1 row in set (0.00 sec)
 
mysql> select json_remove(@j, '$[1]','$[2]');
+--------------------------------+
| JSON_REMOVE(@j, '$[1]','$[2]') |
+--------------------------------+
| ["a", "d"]                     |
+--------------------------------+
1 row in set (0.00 sec)
 
mysql> select json_remove(@j, '$[1]','$[1]');
+--------------------------------+
| JSON_REMOVE(@j, '$[1]','$[1]') |
+--------------------------------+
| ["a", "e"]                     |
+--------------------------------+
1 row in set (0.00 sec)

（8）json_pretty

如果查询结果需要美化一下json的格式，可以使用json_pretty函数。

select user_name,JSON_PRETTY(details) details from user_test;

（9）使用json中的数据作为查询条件

select user_name, details from user_test
where details -> '$.address.city' = '上海市';

（10）json_contains， 用于查询json中是否有数据

select * from user_test where JSON_CONTAINS(details,'30','$.age');

（11）json_contains_path， 判断json的key是否存，存在返回1，不存在返回0

参数只能是one和all，one表示至少存在一个key，all表示同时存在

select id,user_name,details ->> '$.email', JSON_CONTAINS_PATH(details, 'one', '$.email') 
from user_test;

三、对json字段创建索引

同text、blob字段一样，json字段不允许直接创建索引。

对文档中的元素进行查询，就需要用于mysql 5.7引入的虚拟列及函数索引。

# C2 即虚拟列
# index (c2) 对虚拟列添加索引。
create table t ( c1 json, c2 varchar(10) as (JSON_UNQUOTE(c1 -> "$.name")), index (c2) );
 
insert into t (c1) values  ('{"id": 1, "name": "a"}'), ('{"id": 2, "name": "b"}'), ('{"id": 3, "name": "c"}'), ('{"id": 4, "name": "d"}');
 
mysql> explain select * from t where c2 = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | c2            | c2   | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
 
mysql> explain select * from t where c1->'$.name' = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | c2            | c2   | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

四、将存储json字符串的字段升级为JSON字段

1、使用json_valid函数找出不满足json格式要求的文档。

mysql> select * from t where json_valid(c1) = 0;
+----+---------------------+
| id | c1                  |
+----+---------------------+
|  4 | {"id", "name": "d"} |
+----+---------------------+
1 row in set (0.00 sec)

2、处理不满足json格式要求的文档。

mysql> update t set c1='{"id": "4", "name": "d"}' where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3、将text字段修改为json字段

mysql> select * from t where json_valid(c1) = 0;
Empty set (0.00 sec)
 
mysql> alter table t modify c1 json;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

五、使用json时的注意事项

1、默认值

设置默认值时通过小括号括起来，否则的话，还是会提示json字段不允许设置默认值。

mysql> create table t(c1 json not null default (''));
Query OK, 0 rows affected (0.03 sec)
 
mysql> create table t(c1 json not null default '');
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'c1' can't have a default value

2、不允许直接创建索引，可以创建函数索引

3、JSON列的最大在小和longblb、longtext一样，都是4G.

4、插入时，单个文档的大小受到max_allowed_packet的限制，该参数最大是1G。