一文彻底搞定MySQL中的JSON类型，效率飞起。

写在开头

JSON类型通常用于存储非结构化的数据或者一些可变的数据，比如存储日志、配置信息等。我们有时候需要将类似的信息保存到MySQL数据库中，但是MySQL作为关系型数据库，对于JSON这种非结构化存储存在一定的弊端

Mysql数据库对json数据类型的支持

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

相对字符类型，原生的 JSON 类型具有以下优势：

1. 在插入时能自动校验文档是否满足 JSON 格式的要求。
2. 数据快速解析，MySQL提供了多种函数，可以快速读取和操作JSON格式的数据

JSON数据的两种有效格式

1. JSON数组，包含在[]中的一系列值，例如: [1, 2, 'a', 'b']
2. JSON对象，包含在{}中的键值对，键和值之间用冒号分隔（键必须是字符串），键值对之间以逗号分隔，如: {'key1':'value1', 'key2':'value2'}

两中数据格式可以相互嵌套。

了解MySQL中JSON数据格式

• 测试数据

#DDL  
create table json_demo(
id_ int primary key,
val_ json);  
#插入数据
insert into json_demo values(1, '[1,2,"a","b"]');
insert into json_demo values(2, '{"key1":1, "key2":2, "key1":"value3"}');  
#查询
select * from json_demo;

小贴士
在JSON对象中，键是不能重复的，如果出现相同的键，规范化时后值会覆盖前值。JSON对象中重复的键key1保留了后出现的值"value3"。（MySQL 8.0.3之前保留先出现的值）。

• json_array、json_object、cast函数

json_array() 函数以将列值转换为JSON数组：

select json_array(TRUE, FALSE, NULL, 'TRUE', 'FALSE', 'NULL');

小贴士
JSON数据类型会对数据进行一些预处理，会自动将boolean类型转换为小写。 这里TRUE, FALSE, NULL在规范化过程中均被转换为小写，而字符串'TRUE', 'FALSE', 'NULL'则保持不变

json_object() 函数可以把字符串形式的键值对转换成JSON格式对象。

select json_object('key2',2, 'key1',1, 'key1',3);

小贴士
返回的结果中，key1后出现的值3覆盖了先出现的值1。
规范化后，JSON对象是按键排序的，本例中key1被排到的前面

由于键必须是字符串，即使以数字形式提供，在规范化的过程中也会被转换为字符串：

select json_object('key2',2, 'key1',1, 3, 'key1');

cast(… as json)会根据提供的字符串格式，转换为JSON数组或JSON对象：

select cast('[1,2,"abc"]' as json);    -- 转换为JSON数组
 
select cast('{"key1":1, "key2":2, "key1":3}' as json);    -- 转换为JSON对象

JSON数据类型的操作

使用JSON格式的最大好处是MySQL提供了一系列操作函数，可以快速对JSON类型的数据进行查询/更新，不必再对整个字符串进行格式解析，在处理JSON数据时简便许多。

这是目前的测试库中的数据：

JSON类型高频使用函数。

使用JSON格式的最大好处是MySQL提供了一系列操作函数，可以快速对JSON类型的数据进行查询/更新，不必再对整个字符串进行格式解析，在处理JSON数据时简便许多。

一 JSON数据查询

（一）： json_extract()函数

1：json_extract()函数可以从JSON格式的数据中快速取出指定值，语法如下：

• json_extract(js_array, '$[n]') 通过'$[n]'的形式取出JSON数组中编号为n的元素（编号从0开始）

select id_,json_extract(val_,'$[1]') from json_demo ;

小贴士
如果目标是JSON对象，则返回null（id为2的记录）

2：从JSON数组中取出范围值，语法如下：

• json_extract(js_array, '$[m to n]') 通过'$[m to n]'的形式取出JSON数组中编号m到n的所有元素（编号从0开始）

select id_, json_extract(val_, '$[1 to 2]') from json_demo where id_ = '1';

json_extract(val, '$[1 to 2]')取出JSON数组中

2~3

范围内的元素。

3： 从JSON对象中取出单个值，语法如下：

• json_extract(js_object, '$.key') 通过'$.key'的形式取出JSON对象中键为key所对应的值

select id_, json_extract(val_, '$.key1') from json_demo where id_ = '2';

小贴士
'和.key'还可以替换为'和.'表示返回所有的值。
json_extract函数还有一种更简便的column->path写法，*用'->'或'->>'符号来替代json_extract()。
注意符号->和->>的输出结果差异，->的结果是带引号的，如果想要的是不带引号的值，使用->>即可。

select id_,val_->'$[1]' from json_demo where id_ = '1';  
select id_, val_->'$[1 to 2]' from json_demo where id_ = '1';  
select id_, val_->'$.key1' from json_demo where id_ = '2';

（二）： json_keys()函数

json_extract()函数返回的都是JSON对象的值，如果想返回键，则需要用json_keys()函数。json_keys()会以JSON数组的形式返回JSON对象中顶层的键，即将所有的键组成一个数组返回。语法如下：

• json_keys(json_doc [, path])，当提供path参数时（JSON对象嵌套），会返回指定path处元素的顶层键。

select json_keys('{"key1": "value1", "key2": {"a":"b"}}');

这里的顶层键有'key1','key2'，其中'key2'的值又是一个JSON对象（嵌套）,当没有指定path参数，这里只返回了顶层的键。

如果要返回key2值中的键，需要提供path参数'$.key2'

select json_keys('{"key1": "value3", "key2": {"a":"b"}}', '$.key2');

JSON数据新增

JSON对象的中的元素新增，可以通过json_arry_append()，json_array_insert()或json_insert()函数来完成。

这是当前的测试数据

（一）：json_array_append()函数

josn_arry_append() 会在JSON数组指定的位置添加新的数据，新增的数据与原位置的数据会合并为新的JSON数组（依然算1个元素），不会改变原JSON数据的元素个数。语法如下：

• json_arry_append(json_doc, path, val [,path, val] …)

update json_demo set val_ = json_array_append(val_,'$[0]','x', '$[3]','y') where id_=1;
# 在JSON数组第一个元素中增加一个'x'，第三个元素中增加一个'y'，数组的元素依然是4个
update json_demo set val_=json_array_append(val_,'$.key2','y') where id_=2;
#在JSON对象键key2的值中附件一个'y'

（二）： json_array_insert()函数

josn_arry_insert() 会在JSON数组指定的位置添加新的数据，与json_array_append()不同的是，新增的数据会作为一个独立的元素，此函数会改变JSON数组中元素的个数，函数语法为：

• json_arry_insert(json_doc, path, val [,path, val] …)

update json_demo set val_=json_array_insert(val_,'$[0]','x', '$[3]','y', '$[100]','z') where id_=1;
# 插入了3个元素，JSON数组的元素由4个增加至7个
# 第一个元素插入指定位置后，从这个位置开始，所有元素向后移动一位，这会改变后续元素编号，并影响后续的插入位置
# '$[0]'位置插入'x'后，所有元素后移一位，"a"的位置由'$[2]'变成了'$[3]'，因此函数第二个插入'$[3]'是插在"a"的前面，而不是原'$[3]'元素"b"的前面
# 函数第三个元素'$[100]'，超出了数组长度，因此附加在数组的最后

执行SQL后的数据：

（三）： json_insert()函数

json_insert() 函数可以对JSON数组或JSON对象新增元素，根据给定的path，如果元素不存在，则进行新增，如果元素已存在，则忽略，不做任何操作，即只新增不更新。语法为：

• json_insert(json_doc, path1, val1, [path2, val2 …])

update json_demo set val_=json_insert(val_,'$[0]','x','$[7]','y') where id_=1;
#对JSON数组操作时，第一个path '$[0]'，指定插入'x'，但因'$[0]'已存在，因此忽略
#第二个path '$[4]'，指定插入'y'，由于原数组只有4个元素，最大编号只到'$[3]'，新增成功

执行SQL后的数据：

JSON数据更新

JSON数据的更新，你可以把JSON作为一个字符串，更新完后再整体赋值回去，但这种方法在JSON对象较大的时候可能效率较低，且解析成本也高。

这是当前的测试数据

（一）： json_replace()函数

json_replace() 函数可以对JSON数据进行原地（in-place update）更新，即用新的值替换旧值，此函数仅对已存在的值进行更新，对不存在的值直接忽略，即只更新不新增。语法为：

• json_replace(json_doc, path1, new_val1, [path2, new_val2 …])

update json_demo set val_=json_replace(val_,'$[0]','x','$[1]','y','$[5]','z') where id_=1;
#josn_replace函数通过'$[0]'和'$[1]'指定第1，2个元素
#这里$[5]指定更新了一个不存在的元素，因此忽略

执行后数据：

（二）： json_set()函数

如果需要同时进行更新和新增（例如数据同步），利用json_set() 函数可以同时完成对数据的更新和新增，对于已存在的元素更新，不存在的元素新增，语法为：

• json_set(json_doc, path1, new_val1, [path2, new_val2 …])

update json_demo set val_=json_set(val_,'$[0]','0','$[1]','1','$[5]','5') where id_=1;
#第一个path参数'$[0]'，匹配数组中第一个元素，将'x'更新为'0'
#第二个path参数'$[1]'，匹配数组中第二个元素，将'y'更新为'1'
#第三个path参数'$[100]'，匹配数组中第100个元素，由于不存在，新增到数组最后

执行后数据：

JSON数据删除

这是当前的测试数据

（一）： json_remove()函数

json_remove() 函数从JSON数据中删除指定的元素，语法为：

• json_remove(json_doc, path1 [,path2 …])

select json_remove(val_,'$[1]','$[2]') from json_demo where id_=1;
#这里指定删除了第2，第3个元素，对应位置是'$[1]','$[2]'
#注意在删除'$[1]'位置的元素后，所有后续元素编号都向前移动1位，因此'$[2]'删除的是新'$[3]'位置的元素"b"，而不是原'$[3]'位置的元素"a"

执行后数据：

判断元素否存在

某些时候，我们并不需要提取数值，而仅仅想知道某个元素是否存在（例如作判断条件时）。

当前的测试数据

（一）： json_contains_path()函数

josn_contains_path()函数可以用来测试指定的一个或多个path是否存在。语法为：

• json_contains_path(json_doc, one_or_all, path [,path …])，当path存在时返回1，不存在时返回0（不是null）。

小贴士
该函数第二个参数one_or_all有2种取值，代表2种测试行为：
one： 至少有1个path存在即返回1。
all： 所有path都存在才返回1。

select json_contains_path(val_, 'one', '$[0]', '$[100]') from  json_demo where id_ = '1' ;

select json_contains_path(val_, 'all', '$[0]', '$[100]') from  json_demo where id_ = '1' ;

判断指定path在对象中是否存在：

select json_contains_path(val_, 'one', '$.key1', '$.key100') from  json_demo where id_ = '2' ;

select json_contains_path(val_, 'all', '$.key1', '$.key100') from  json_demo where id_ = '2' ;

（二）： json_search()函数

json_search()函数可以通过值来查询path，如果存在则返回其具体的path，不存在则返回null。语法为：

• json_search(json_doc, one_or_all, search_str [escape_char [,path]…)

小贴士
第二个参数one_or_all控制返回path的搜索行为：
one： 返回第一个匹配的path，当找到第一个匹配的path时搜索即终止。
all： 返回所有匹配的path。
在提供search_str时，可以用%或_来代替任意多个或单个字符（和like中用法一样）。但是如果要匹配'%'或'_'字符本身，则要加上转义字符，即后面的参数escape_str，省略该参数或提供null值，则默认为\

set @array1 = '[1,2, 1, "abc", "abd", "xyz"]';
 
select json_search(@array1, 'one', 'abc');

set @object1 = '{"key1":"abc", "key2":"abd"}';
 
select json_search(@array1, 'all', 'ab%');

如何查询指定位置上是否是某值

select @array1 = '[1,2, 1, "abc", "abd", "xyz"]';
 
select json_search(@array1,'all','abc',null,'$[3]');
 # 第三个元素是否包含 abc 
select json_search(@array1,'all','abc',null,'$[4]');
 # 第四个元素是否包含 abc 

（三）： value member of(json_array)

对于JSON数组，如果只是想知道某个值是否存在，还可以用value member of(json_array)来判断值是否包含在指定数组中（MySQL 8.0.17后可用），包含返回1，不包含则返回0：

select val_, 2 member of(val_), 'xyz' member of(val_),'x' member of(val_) from json_demo where id_=1;

JSON数据与索引

在MySQL的表中，JSON类型的列通常无法直接建立索引，你可以用虚拟生成列(Virtual Generated Columns），并根据该列来建立间接索引。但是在MySQL8.0.17版本后，对于JSON数组（JSON对象不行），可以建立多值索引（Multi-valued Index）。

（一）： 生成列索引（Generated Column Index）

对于JSON数据类型需要建立索引，可以对将经常查询的元素提取出来，作为一个虚拟的生成列，并在该列上建立索引，查询时通过虚拟列上索引即可快速定位数据。虚拟列的语法是：

col_name data_type [GENERATED ALWAYS] AS (expr)
 [VIRTUAL | STORED] [NOT NULL | NULL]
 [UNIQUE [KEY]] [[PRIMARY] KEY]
 [COMMENT 'string']

• expr： 是列的生成表达式，需要依赖其他列计算。
• virtual： 代表该列不实例化，不消耗存储空间，每次用到该列时计算。
• stored： 代表实例化存储，消耗存储空间，且每次更新其依赖列时，都会同时更新虚拟列数据。

是不是感觉晦涩难懂，接着往下看这个例子帮助我们理解下。↓
下面示例中，b是根据a生成的虚拟列，并且在b上建立了索引idx：

create table index_demo(
a json,
b int generated always as (a->"$.id"),
key idx(b));
#插入三条数据
insert into index_demo(a) 
values
('{"id":1, "name":"Vincent"}'),
('{"id":2, "name":"Victor"}'), 
('{"id":3, "name":"Grace"}');

这样就可以通过b列查询时即可利用索引提速，快速定位记录：

explain select * from person where b=2;

（二）： 多值索引（Multi-valued Index）

MySQL 8.0.17版本引入了多值索引，可以直接对JSON类型列创建索引，但是仅限JSON数组。在传统二级索引中，一个索引记录对应一条数据记录。但在多值索引中，会根据JSON数组中的值建立多个索引，同时指向这一条记录，其底层原理依然是通过虚拟列完成的。

在对JSON数组列建立索引前，需要先用cast(… as type array)将其由JSON数组类型转换为SQL数组类型

create table index_demo2(
a json,
key idx((cast(a as unsigned array)))
);
 
insert into index_demo2 
values
('[1, 2, "3", 4]'), 
('[5, "6", 7]'),
('[8, 9, 10]');

小贴士
由于这里限制的是unsigned array，因此json数组中元素必须可以转换为数字，例如插入字符"a"则会报错。（unsigned 属性表示无符号类型，即只能存储正数和零，不能存储负数） 在建立多值索引后，member of(), json_contains()函数在where条件中则可以利用多值索引来加速查询。

这里查询a列中包含3的记录，通过执行计划可以发现member of()函数使用了多值索引idx：

select * from t where 3 member of(a);
 
explain select * from t where 3 member of(a);

关注下方名片『编程青衫客』

随时随地获取最新好文章！