MySQL中JSON数据类型详解

概要及优点

JSON数据类型是MySQL5.7.8开始支持的。在此之前，只能通过字符类型（CHAR、VARCHAR或TEXT）来保存JSON文档。

JSO数据类型具有的优势：

1. 在插入时能自动校验文档是否满足JSON格式的要求。
2. 优化了存储格式，无需读取整个文档就能快速访问某个元素的值。
3. 节省网络带宽，结合索引还能降低磁盘IO消耗。

JSON定义

JSON 是 JavaScript Object Notation（JavaScript 对象表示法）的缩写，是一个轻量级的，基于文本的，跨语言的数据交换格式。易于阅读和编写。

JSON的基本数据类型：

• 数值：十进制数，不能前导0，可以为负数或小数，还可以为e或E表示的指数。
• 字符串：字符串必须用双引号括起来。
• 布尔值：true、false。
• 数组：一个由零或多个值组成的有序序列。每个值可以为任意类型。数组使用方括号[ ]括起来，元素之间用逗号，分隔。

[1,"abc",null,true,"11:05:00.000000",{"id":1}]

• 对象：一个由零或者多个键值对组成的无序集合。其中键必须是字符串，值可以为任意类型。对象使用花括号 { } 括起来，键值对之间使用逗号，分隔，键与值之间用冒号：分隔。

{"db":["mysql","oracle"],"id":123,"info":{"age":20}}

• 空值：null。

JSON字段的增删改查操作

插入操作

1）直接插入JSON格式的字符串

# 创建测试表
create table mytest(id BIGINT ,other json);

# 插入表数据
insert into mytest values(1,'[1, "abc", null, true, "00:00:00.000000"]');

2）使用函数，常用的有JSON_ARRAY()和JSON_OBJECT()

• JSON_ARRAY()：用于构造JSON数组

# 使用json_array()函数插入
insert into mytest values(2,json_array(2,'abc',null,true,now()));

• JSON_OBJECT()：用于构造JSON对象

# 使用json_object()函数插入
insert into mytest values(3,json_object('name','abc','time',now()));

注意：

MySQL5.7.x版本，如果插入键重复则会使用第一个键对应的值。

json_object('key1',10,'key2',20,'key1',30) 结果：{"key1":10,"key2":20}

MySQL8.0.x版本，如果插入键重复会使用最后出现的键对应的值。

json_object('key1',10,'key2',20,'key1',30) 结果：{"key1": 30,"key2":20}

查询操作

以此数据为查询元数据

1）JSON_EXTRACT(json_doc,path[,path] ...)

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

数组的路径：

• 通过下标来表示的。第一个元素的下标是0。

# 使用json_extract函数进行查询，通过下标来表示的。
select json_extract(other,'$[0]') as val from mytest;

• 通过[ M to N ]获取数组的子集。
  • MySQL5.7.x版本不支持，本人亲测。
  • MySQL8.0.x版本可以试一试，本人没有测试。

# 使用json_extract函数进行查询，通过[M to N] 获取数组的子集。
select json_extract(other,'$[0 to 1]') as val from mytest;

# 使用$[last-1 to last]，这里的 last 代表最后一个元素的下标
select json_extract(other,'$[last-1 to last]') as val from mytest;

• 通过$[*]，获取数组中的所有元素.

# 使用$[*]，获取数组中的所有元素
select json_extract(other,'$[*]') as val from mytest;

对象的路径：

• 通过KEY来表示的

# 如果 KEY 在路径表达式中不合法（譬如存在空格），则在引用这个 KEY 时，需用双引号括起来。

# 使用json_extract函数进行查询对象，通过 KEY 来获取属性值
select json_extract(other,'$.name') as val from mytest where id = 3;

select json_extract(other,'$."test a"') as val from mytest where id = 3;

select json_extract(other,'$.testArr[0]') as val from mytest where id = 3;

注意：以上两种语法，在MySQL5.7.x版本不支持，可以在MySQL8.0.x版本试一试。

• 通过.*获取对象中的所有元素

select json_extract(other,'$.*') as val from mytest where id = 3;

# 网上其他示例：

# 这里的 $**.b 匹配 $.a.b 和 $.c.b
select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');

# 结果：
[1, 2]

2）column->path

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

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

注意：在MySQL5.7.x版本不支持，可以在MySQL8.0.x版本试一试。

select other->"$name" from mytest where id = 3;

• column->>path

同 column->path 类似，只不过其返回的是字符串。以下三者是等价的。

• JSON_UNQUOTE( JSON_EXTRACT(column, path) )
• JSON_UNQUOTE(column -> path)
• column->>path

select other->'$.name',json_extract(c2, "$.name"),json_unquote(c2->'$.name'),c2->>'$.name' from mytest;

注意：在MySQL5.7.x版本不支持，可以在MySQL8.0.x版本试一试。

修改操作

1）JSON_INSERT(json_doc, path, val[, path, val] ...)

插入新值。

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

# 示例一：
select json_insert('1','$[0]',"10");

# 结果：
1

# 示例二：
select json_insert('1','$[1]',"10");

# 结果：
[1,"10"]

# 示例三：
select json_insert('["1","2"]','$[2]',"10");

# 结果：
["1", "2", "10"]

2）JSON_SET(json_doc, path, val[, path, val] ...)

插入新值，并替换已经存在的值。

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

set @j = '{ "a": 1, "b": [2, 3]}';

# 示例：
select json_set(@j, '$.a', 10, '$.c', '[true, false]');

#结果：
{"a": 10, "b": [2, 3], "c": "[true, false]"} 

3）JSON_REPLACE(json_doc, path, val[, path, val] ...)

替换已经存在的值。

set @j = '{ "a": 1, "b": [2, 3]}';

# 示例：
select json_replace(@j, '$.a', 10, '$.c', '[true, false]');

# 结果：
{"a": 10, "b": [2, 3]}

删除操作

1）JSON_REMOVE(json_doc, path[, path] ...)

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

# 数据格式：{"name":"123","time":"234"}
# 会删除other字段中的name属性及对应的值
select json_remove(other, '$.name') from mytest where  id = 3;


# 数据格式：other: ["a", ["b", "c"], "d", "e"]
# 会删除other字段中数组下标为1的值
select json_remove(other, '$.[1]') from mytest;

# 结果：
["a", "d", "e"]

# 会删除other字段中数组下标为1的值，再根据新的结果，删除下标为2的值
select json_remove(@j, '$[1]','$[2]') from mytest;

# 结果：
["a", "d"] 

# 会删除other字段中数组下标为1的值，再根据新的结果，删除下标为1的值
select json_remove(@j, '$[1]','$[1]') from mytest;

# 结果：
["a", "e"] 

如何对JSON字段创建索引？

同 TEXT，BLOB 字段一样，JSON 字段不允许直接创建索引。

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

# 创建表及索引
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"}');

加索引查询结果分析：

explain select * from t where c2 = 'a';

explain select json_extract(c1,'$.id') from t where c1->'$.name' = 'a';

以上可见，虚拟列和json类型的列结果分析是一样的。

不加索引查询结果分析：

explain select * from t where c2 = 'a';

explain select json_extract(c1,'$.id') from t where c1->'$.name' = 'a';

注意：在创建虚拟列时需指定 JSON_UNQUOTE，将 c1 -> "$.name" 的返回值转换为字符串。

使用JSON时的注意事项

1. 再MySQL8.0.13之前，不允许对BLOB、TEXT、GEOMETRY、JSON字段设置默认值。从MySQL8.0.13开始，取消了这个限制。
2. 不允许直接创建索引，可创建函数索引。
3. JSON列的最大大小和LONGBLOB（LONGTEXT）一样，都是4G.
4. 插入时，单个文档的大小受到max_allowed_packet的限制，该参数最大是1G.

小知识：

max_allowed_packet:指mysql服务器端和客户端再一次传送数据包的过程当中最大允许的数据包大小。