一文彻底搞定MySQL中的JSON类型，效率飞起。
一文彻底搞定MySQL中的JSON类型，效率飞起。
MySQL从5.7.8版本开始支持JSON数据类型，这为存储和处理非结构化数据提供了便利。本文将详细介绍MySQL中JSON数据类型的使用方法，包括数据格式、常用函数以及性能优化技巧。
MySQL对JSON数据类型的支持
JSON数据类型是MySQL 5.7.8开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 、TEXT或LONGTEXT ）来保存JSON数据。相对字符类型，原生的 JSON 类型具有以下优势：
- 在插入时能自动校验文档是否满足 JSON 格式的要求。
- 数据快速解析，MySQL提供了多种函数，可以快速读取和操作JSON格式的数据。
JSON数据的两种有效格式
- JSON数组，包含在[]中的一系列值，例如: [1, 2, 'a', 'b']
- JSON对象，包含在{}中的键值对，键和值之间用冒号分隔（键必须是字符串），键值对之间以逗号分隔，如: {'key1':'value1', 'key2':'value2'}
两种数据格式可以相互嵌套。
了解MySQL中JSON数据格式
测试数据
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数据转换函数
- 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被排到的前面。由于键必须是字符串，即使以数字形式提供，在规范化的过程中也会被转换为字符串。
- 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数据查询
json_extract()函数：从JSON格式的数据中快速取出指定值
从JSON数组中取出编号为n的元素
select id_,json_extract(val_,'$[1]') from json_demo;小贴士
如果目标是JSON对象，返回null（id为2的记录）。从JSON数组中取出范围值
select id_, json_extract(val_, '$[1 to 2]') from json_demo where id_ = '1';从JSON对象中取出单个值
select id_, json_extract(val_, '$.key1') from json_demo where id_ = '2';小贴士
'$.key’还可以替换为'$.*'表示返回所有的值。json_extract函数还有一种更简便的column->path写法，用’->‘或’->>'符号来替代json_extract()。注意符号->和->>的输出结果差异，->的结果是带引号的，如果想要的是不带引号的值，使用->>即可。json_keys()函数：返回JSON对象中顶层的键
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_array_append()函数：在JSON数组指定的位置添加新的数据
update json_demo set val_ = json_array_append(val_,'[ 0 ] ', 'x', '[0]','x','[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()函数：在JSON数组指定的位置添加新的数据
update json_demo set val_=json_array_insert(val_,'[ 0 ] ', 'x', '[0]','x','[0]','x', '[3]', 'y', '$[100]', 'z') where id_=1;
#插入了3个元素，JSON数组的元素从4个变成了7个
- json_insert()函数：对JSON数组或JSON对象新增元素
update json_demo set val_=json_insert(val_,'[ 0 ] ', 'x', '[0]','x','[0]','x', '[7]', 'y') where id_=1;
#对JSON数组操作时，第一个path ‘[ 0 ] ’ ，指定插 入 ‘ x ’ ，但 因 ‘ [0]‘已存在，因此忽略
#第二个path?’[ 4 ] ′ ，指定插 入 ‘ y ’ ，由于原数组只有 4 个元素，最大编号只 到 ‘ [4]‘，新增成功
JSON数据更新
- json_replace()函数：对JSON数据进行原地更新
update json_demo set val_=json_replace(val_,'[ 0 ] ', 'x', '[0]','x','[0]','x', '[1]', 'y') where id_=1;
#josn_replace函数通过path参数指定要更新的元素位置，例如'[0]'指定第1个元素，'[1]'指定第2个元素
#这里[5]指定更新了一个不存在的元素，因此忽略
- json_set()函数：同时完成对数据的更新和新增
update json_demo set val_=json_set(val_,'[ 0 ] ', '0', '[0]','0','[0]','0', '[1]', '1', '$[100]', 'z') where id_=1;
#第一个path参数'[0]’，匹配数组中第一个元素，将’x’更新为’0’
#第二个path参数’[1]’，匹配数组中第二个元素，将'y'更新为'1'
#第三个path参数'[100]'，匹配数组中第100个元素，由于不存在，新增到数组最后
JSON数据删除
- json_remove()函数：从JSON数据中删除指定的元素
select json_remove(val_,'[ 1 ] ', '[1]','[1]','[2]') from json_demo where id_=1;
#这里指定删除了第2，第3个元素，对应位置是’[ 1 ] ‘, ‘[1]‘,’[1]‘,’[2]’
#注意在删除’[ 1 ] ‘ 位置的元素后，所有后续元素编号都向前移动 1 位，因 此 ‘ [1]‘位置的元素后，所有后续元素编号都向前移动1位，因此'[1]‘位置的元素后，所有后续元素编号都向前移动1位，因此’[2]‘删除的是新’[ 3 ] ‘ 位置的元素 " b " ，而不是 原 ‘ [3]‘位置的元素"b"，而不是原'[3]‘位置的元素"b"，而不是原’[3]‘位置的元素"a"
判断元素是否存在
- json_contains_path()函数：测试指定的一个或多个path是否存在
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';
- json_search()函数：通过值来查询path
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%');
- value member of(json_array)函数：判断值是否包含在指定数组中
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);