一文彻底搞定MySQL中的JSON类型，效率飞起。
写在开头
JSON类型通常用于存储非结构化的数据或者一些可变的数据，比如存储日志、配置信息等。我们有时候需要将类似的信息保存到MySQL数据库中，但是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数据格式
-
测试数据
#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);
关注下方名片『编程青衫客』
随时随地获取最新好文章！
-
-
-
-
-
-
-
-
-
-
-
-
-
-
