写在开头
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);
关注下方名片『编程青衫客』
随时随地获取最新好文章!