首页 前端知识 一文彻底搞定MySQL中的JSON类型,效率飞起。

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

2024-11-10 09:11:01 前端知识 前端哥 161 866 我要收藏

写在开头

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);

 

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

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

转载请注明出处或者链接地址:https://www.qianduange.cn//article/20153.html
标签
评论
发布的文章
大家推荐的文章
会员中心 联系我 留言建议 回顶部
复制成功!