json字段定义和插入
创建一个带有json字段的表
create table test10 (
id int not null auto_increment comment 'id',
name varchar(64) null default "" comment 'name',
json_data json default null comment "json格式数据",
primary key(id)
)
插入记录
insert into test10(id,name,json_data) values(1,"name1",'{"age":1,"des":"des1"}');
insert into test10(id,name,json_data) values(2,"name2",'{"age":2,"des":"des1"}');
insert into test10(id,name,json_data) values(3,"name3",'{"age":3,"des":"des3"}');
虚拟列和索引
添加json格式的数据列可以方便的查询
- 并且虚拟列多占用了一份空间
- 虚拟列的数据来自其他列,虚拟列不能直接修改,只能查询
- 如果要修改只能改原始的字段
- 可以对虚拟列建立索引,提升查询速度
#添加实体列
#添加一个普通列
ALTER TABLE hl.test2 ADD name varchar(100) NOT NULL;
#添加一个json列
ALTER TABLE hl.test2 ADD json_data2 json NOT NULL;
# column 的作用
# generated always 的作用
# virtual 的作用
#添加虚拟列的语法[]里面的都可以省略,并且默认会被加上
ALTER TABLE hl.test2 ADD [column] v_age7 INT(2) [GENERATED ALWAYS] AS (json_data2->>"$.age") [VIRTUAL] NULL;
#最简单写法
ALTER TABLE hl.test2 ADD v_age7 INT(2) AS (json_data2->>"$.age");
#等价于
ALTER TABLE hl.test2 ADD v_age7 INT(2) AS (json_unquote(json_extract(`json_data2`,'$.age')));
#如果json字段的对应虚拟字段的值不能被转换,那么写入操作会被截断,然后报错
#估计写入过程是先写入json字段,然后同步带虚拟列,这时候如果装换出错会报错回滚
insert into test2(id,json_data1,json_data2,name) values(4,'{"age":"a1","age2":"a2"}','{"age":"a1","age2":"a2"}','name1');
虚拟列和普通列之间不能互转,虚拟转普通和普通转虚拟都会报下面的错误
'Changing the STORED status' is not supported for generated columns.
虚拟列数据来源不仅可以是json列,也可以引用普通字段
#虚拟列来自于普通列的装换
ALTER TABLE hl.test2 MODIFY COLUMN v_age10 VARCHAR(10) GENERATED ALWAYS AS (substring(name,0,51)) VIRTUAL NULL;
虚拟列修改
只能修改原始列的数据,不能直接修改,这种修改的修改数据量几乎是全量的,修改数据量偏大,更新大量数据的时候容易undo日志缓存区溢出
#虚拟列不能直接修改,异常信息:The value specified for generated column 'v_age2' in table 'test2' is not allowed.
update test2 set v_age2 = 11 where id =3;
#虚拟列只能通过修改原数据字段修改
update test2 set json_data2 = json_set(json_data2,'$.age',"22") where id =3;
虚拟列创建索引和普通字段创建索引一样,查询效果也几乎一样
备注:没有索引普通列查询速度比json列映射的虚拟列快
#普通列加索引
CREATE INDEX test2_name_IDX USING BTREE ON hl.test2 (name);
#虚拟列加索引
CREATE INDEX test2_v_age_IDX USING BTREE ON hl.test2 (v_age);
json字段的一部分也可以创建索引
语法: add index 索引名字( (cast( json表达式 as 数据类型 array)) );
数据类型可选:unsigned/signed/char(N)等
这个用的比较少,生效的场景比较少,不推荐,为啥呢,因为不容易看出哪些字段上有索引
注意:索引要索引命中必须在数据参数位置填入和索引定义一样的表达式,不能通过jsonPath修正,不然找不到
注意2:虽然没有定义虚拟列其实效果和定义虚拟列一样,需要考虑老数据数据类型转换是否兼容
注意3:
#表结构
#json_data我打算存json
#json_data2我打算存数组
CREATE TABLE `test2` (
`id` bigint NOT NULL AUTO_INCREMENT,
`json_data` json DEFAULT NULL,
`age` int GENERATED ALWAYS AS (json_unquote(json_extract(`json_data`,_utf8mb4'$.age'))) VIRTUAL,
`name` varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract(`json_data`,_utf8mb4'$.name'))) VIRTUAL,
`json_data2` json DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
#插入数据
INSERT into test2(json_data) values('{"name":"zhansan1","age":11}'),('{"name":"lisi1","age":12}'),('{"name":"wanwu1","age":13}');
#json字段上创建索引1
alter table test2 add index idx_age1((cast(json_data->'$.age' as unsigned array)));
#json字段上创建索引2,区别在于json提取表达式
alter table test2 add index idx_age2((cast(json_data->>'$.age' as unsigned array)));
#命中索引1
explain SELECT * from test2 where 2 MEMBER of(json_data->'$.age');
#名字索引2
explain SELECT * from test2 where 2 MEMBER of(json_data->>'$.age');
#命中索引1
explain SELECT * from test2 where json_overlaps(json_data->'$.age','2');
#命中索引2
explain SELECT * from test2 where json_overlaps(json_data->>'$.age','2');
#无法命索引,没有第三个参数
explain SELECT * from test2 where json_overlaps(json_data,'2');
#命中索引1
explain SELECT * from test2 where json_contains(json_data->'$.age',"2");
#命中索引2
explain SELECT * from test2 where json_contains(json_data->>'$.age',"2");
#无法命中索引,索引要索引命中必须在数据字段参数填入和索引定义一样的表达式
explain SELECT * from test2 where json_contains(json_data,"2",'$.age');
#不能命中索引
explain SELECT * from test2 where json_data->'$.age' = 2;
#数组字段上创建索引3
alter table test2 add index idx_3((cast(json_data2 as unsigned array)));
#数组字段上创建索引4
alter table test2 add index idx_4((cast(json_data2 as char(32) array)));
#命中索引3
explain SELECT * from test2 where 12 MEMBER of(json_data2);
#命中索引4
explain SELECT * from test2 where “12” MEMBER of(json_data2);
#优先命中索引3,如果没有索引3也能命中索引4
explain SELECT * from test2 where json_contains(json_data2,"12",'$');
#优先命中索引3,如果没有索引3也能命中索引4
explain SELECT * from test2 where json_overlaps(json_data2,'12');
#直接表达式都用不上索引,所以还是建虚拟列把,那样可以明确索引使用列
explain SELECT * from test2 where json_data2->'$' ="12";
explain SELECT * from test2 where json_data2->'$' =12;
json字段的修改和添加
全量修改
update users set json_data = '{"a":1}';
部分修改
-
json_insert
添加数据,不存在的才会插入,存在的忽略参数: json_insert(json,path1,value1,path2,value2...)
set @testData = '{ "a": 1, "b": [2, 3]}'; #a已经存在则忽略,c不存在则添加,结果:{"a": 1, "b": [2, 3], "c": "[true, false]"} select json_insert(@testData, '$.a', 10, '$.c', '[true, false]'); #上面插入的c是一个带引号的字符串,想要插入一个数组,必须进行转换,结果:{"a": 1, "b": [2, 3], "c": [true, false]} select json_insert(@testData, '$.a', 10, '$.c', cast('[true, false]' as json)); #空的父节点是会被忽略,{"a": 1, "b": [2, 3]} select json_insert(@testData, '$.a', 10, '$.c.c1', "c1"); #只能向直接子节点插入数据 select json_insert(@testData, '$.a', 10, '$.c', cast( '{"c1":"c1"}' as json ));
-
json_replace
修改数据,已经存在替换,不存在的值忽略。
参数: json_insert(json,path1,value1,path2,value2...)set @jsonData = '{"a":"1","b":2,"c":[1,2]}'; #结果{"a": 2, "b": 2, "c": ["a", 2]} select json_replace(@jsonData,'$.a',2,'$.c[0]',"a",'$.d',4);
-
json_set
添加或者修改数据,存在的修改,不存在添加
参数: json_set(json,path1,value1,path2,value2...)set @testData = '{ "a": 1, "b": [2, 3]}'; #a已经存在则忽略,c不存在则添加,结果:{"a": 1, "b": [2, 3], "c": "[true, false]"} select json_set(@testData, '$.a', 10, '$.c', '[true, false]'); #上面插入的c是一个带引号的字符串,想要插入一个数组,必须进行转换,结果:{"a": 1, "b": [2, 3], "c": [true, false]} select json_set(@testData, '$.a', 10, '$.c', cast('[true, false]' as json)); #不存在父路径的时候修改会被忽略,结果{"a": 10, "b": [2, 3]} select json_set(@testData, '$.a', 10, '$.c.c1', "c1"); #只能向直接子节点插入数据,结果{"a": 10, "b": [2, 3], "c": {"c1": {"c2": "c2"}}} select json_set(@testData, '$.a', 10, '$.c', cast('{"c1":{"c2":"c2"}}' as json));
-
json_array_append
向json数组追加数据,不是数组也能强行加一个元素然后变成数组,也可以不指定下标也能不指定下标
参数: json_array_append(json,path1,value1,path2,value2...)set @testData = '["a", ["b", "c"], "d"]'; #在数组第二个元素的数组中追加1,结果:["a", ["b", "c", 1], "d"] select json_array_append(@testData, '$[1]', 1); #结果:[["a", 2], ["b", "c"], "d"] select json_array_append(@testData, '$[0]', 2); #结果:["a", [["b", 3], "c"], "d"] select json_array_append(@testData, '$[1][0]', 3); #多个参数,结果:[["a", 1], [["b", 2], "c"], "d"] select json_array_append(@testData, '$[0]', 1, '$[1][0]', 2, '$[3]', 3); #对非数组对象也能添加,会强行转出数组 set @testData = '{"a": 1, "b": [2, 3], "c": 4}'; #往b中追加,结果:{"a": 1, "b": [2, 3, "x"], "c": 4} select json_array_append(@testData, '$.b', 'x'); #结果:{"a": 1, "b": [2, 3], "c": [4, "y"]} select json_array_append(@testData, '$.c', 'y'); set @testData = '{"a": 1}'; #结果:[{"a": 1}, "z"] select json_array_append(@testData, '$', 'z');
-
json_array_insert
只能向普通数组插入,并且必须指定下标,比json_array_append约束多set @testData= '{"a":[1]}'; #结果:[{"a": 1}, "z"] select json_array_insert(@testData, '$.a[0]', 'z'); #下面语法是可以的,结果#{"a": ["zz", "z", 1]} select json_array_insert(@testData, '$.a[0]', 'z', '$.a[1]', 'zz'); #下面的语法是错误的,但是json_array_append是可以的 select json_array_insert(@testData, '$', 'z'); #下面语法是可以的,结果[{"a": [1]}, "z"] select json_array_append(@testData, '$', 'z'); #下面的语法是错误的,但是json_array_append是可以的 select json_array_insert(@testData, '$.a', 'z'); #下面语法是可以的,结果{"a": [1, "z"]} select json_array_append(@testData, '$.a', 'z');
-
json_merge
合并的时候新旧值放到数组里面,支持一个值是数组一个值是子对象。
#对象合并重复的key变成数组,结果是{"id": 47, "age": [1, 2], "name": "x"} select json_merge_patch('{"name": "x","age":1}', '{"id": 47,"age":2}'); #对象个非对象合并,直接变成数组,结果是[1, 2, {"id": 47}] select json_merge('[1, 2]', '{"id": 47}'); #多个合并,结果:{"a": [1, 3, 5], "b": 2, "c": 4, "d": 6} select json_merge('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }'); #有null,也是变成数组,结果:{"a": 1, "b": [2, null]} select json_merge('{"a":1, "b":2}', '{"b":null}');
-
json_merge_preserve,和json_merge一样,mysql8.0.3以后建议使用这个json_merge_preserve
-
json_merge_patch
合并的时候用新的值覆盖旧的值,不一样的都会保留参数: json_merge_patch(json,json2,json3)
select json_merge_patch('{"name": "x","age":1}', '{"id": 47,"age":2}'); #都不是对象,取第二个,结果:true select json_merge_patch('1', 'true'); select json_merge_patch('[2, 2]', '[true, false]'); #第一个不是对象,取第二个 ,结果:{"id": 47} select json_merge_patch('[1, 2]', '{"id": 47}'); #第二个不是对象,取第二个 ,结果[1, 2] select json_merge_patch( '{"id": 47}','[1, 2]'); #第二个覆盖第一个,结果:{"a": 3, "b": 2, "c": 4} select json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }'); #多个合并,结果:{"a": 5, "b": 2, "c": 4, "d": 6} select json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }', '{ "a": 5, "d":6 }'); #第二个有null,会删除该key,结果:{"a": 1} select json_merge_patch('{"a":1, "b":2}', '{"b":null}'); #嵌套json也可以合并,结果:{"a": {"x": 1, "y": 2}} select json_merge_patch('{"a":{"x":1}}', '{"a":{"y":2}}');
-
json_remove
删除元素
格式:json_remove(json_doc, path1,path2,...)#结果是 {} select json_remove('{"a":1, "b":2}', '$.a', '$.b'); #结果是都是,{"b": 2} select json_remove('{"a":1, "b":2}', '$.a' ); select json_merge_patch('{"a":1, "b":2}', '{"a":null}' );
json字段的查询和数据获取相关的函数
很多json函数都是mysql8以后才支持的,使用select version();查询mysql版本
-
cast()
cast 可以把字符装换成json对象
格式:cast( json对象 as 类型)
值 描述 DATE 将value转换成'YYYY-MM-DD'格式 DATETIME 将value转换成'YYYY-MM-DD HH:MM:SS'格式 TIME 将value转换成'HH:MM:SS'格式 CHAR 将value转换成CHAR(固定长度的字符串)格式 SIGNED 将value转换成INT(有符号的整数)格式 UNSIGNED 将value转换成INT(无符号的整数)格式 DECIMAL 将value转换成FLOAT(浮点数)格式 BINARY 将value转换成二进制格式 JSON 将json格式 select cast( '{"age":4,"des":"des4"}' as json) from dual;
-
json_extract
提取json对象的属性,支持json字符串和json对象
格式:json_extract(json,path1,path2...)#获取对象属性 select json_extract('{"age":4,"des":"des4"}',"$.age") from dual; #获取数组元素 select json_extract("[1,2,3,4]","$[0]") from dual; #可以同时获取多个属性 select json_extract("[1,2,3,4]","$[2]","$[3]") from dual; #json_extract里面可以传入json对象或者json字符串 SELECT JSON_EXTRACT(json_data ,"$") from test10; SELECT JSON_EXTRACT('{"a":1}' ,"$") from test10; #-----------------------jsonPath的一些例子--------------------- #* 获取全部属性 select json_extract("[1,2,3,4]","$[*]") from dual; # n to m 获取范围 select json_extract("[1,2,3,4]","$[1 to 2]") from dual; #last表示最后一个 select json_extract("[1,2,3,4]","$[last-1 to last]") from dual; #jsonPath语法中有特殊字符的时候,可以用双""表示整体,下面的例子中“a c”是一个特殊的整体 set @queryData='{"a": 1,"a c": 4}'; select json_extract(@queryData, '$."a"'), json_extract(@queryData, '$."a c"')from dual; #支持父路径*号,$**.xxx select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
-
-->和-->>,可以用于json属性提取
区别在-->不会去掉引号,-->>会去掉引号
需要注意箭头表达式不适合原始字符串json,只适合json对象#测试数据,结果是: "jack" jack insert into test10(id,json_data) values(10,'{"empno": 1001, "ename": "jack"}'); #引号测试 select json_data->'$.ename', json_data->>'$.ename' from test10 where id = 10; #下面的写法是不行的,所以箭头表达式不适合原始字符串json,只适合json对象 select "{}"->'$', "{}"->>'$' from test10 where id = 10; #这也不行?明明是对象 select cast( @testData as json ) ->> "$.age";
-
json_unquote和json_quote
区别在于是否保留引号json_unquote可以和别的json提取函数和箭头表达式配合使用json_unquote可以接收json字符串或者是json对象
json_quote只能接受json字符串select json_unquote(json_data->'$.ename'), json_unquote(JSON_EXTRACT(json_data,"$.ename")) from test10 where id = 10; #都可接受json字符串,但是json_quote不能接受json对象 select json_unquote("{}"), json_quote("{}") from test10 where id = 10; #保存错 select json_quote(json_data) from test10 where id = 10;
-
json_contains_path
判断路径是否存在
格式:json_contains_path(json_doc,"one/all", path1,path2 …)set @testData = '{"a": 1, "b": 2, "c": {"d": 4}}'; #判断存在一个就行,结果是0 select json_contains_path(@testData, 'one', '$.a', '$.e'); #判断所有path都存在,结果是0 select json_contains_path(@testData, 'all', '$.a', '$.e');
-
json_contains
判断值是否存在,如果value是一个json,并且有多个值,那么是and的关系
格式:json_contains(json_doc,value,[path])#json_contains,判断值是否存在 set @testdata = '{"a": 1, "b": 2, "c": {"d": 4}}'; #可以查询单层对象 select json_contains(@testdata, '{"a":1}'); #可以查询多层嵌套的的对象 select json_contains(@testdata, '{"c":{"d": 4}}'); #可以指定查询path,不指定的时候默认是根 select json_contains(@testdata, '{"d": 4}',"$.c"); #查询普通的值 select json_contains(@testdata,"1", '$.a'); select json_contains("1", "1","$"); #可以查询数组 set @testdata= '[1, "a", 1.02]'; #可以不指定下标,表示任意元素 select json_contains(@testdata, '"a"'); #可以指定下标查询指定数组位置 select json_contains(@testdata, '"a"',"$[0]"); #结果是1 select json_contains(@testdata,'{"a":1,"b":"2"}'); #结果是0 select json_contains(@testdata,'{"a":1,"b":2}');
-
json_overlaps
比较两个json是否有重叠,只需要查询对象任意一个属性匹配就行,需要属性和值都相等,类似or
格式:json_overlaps(json,queryjson)select * from test10 where json_overlaps(json_data,'{"age":1,"des":"des3"}'); #等价于 select * from test10 where json_data ->> "$.age" = 1 or json_data ->> "$.des" = "des3"; #测试 set @jsonData = '{"age":1,"des":"des3"}'; #结果是1 select json_overlaps(@jsonData,'{"age":1,"des":3}'); #结果是1 select json_overlaps(@jsonData,'{"age":1,"des":2}');
-
json_arrayagg()
分组查询中,json_arrayagg可以把非分组字段聚合成数组#测试数据 CREATE TABLE `test1` ( `id` int NOT NULL AUTO_INCREMENT COMMENT 'id', `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT 'name', `key` varchar(64) DEFAULT NULL COMMENT 'json格式数据', `value` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO hl.test1(id, name, `key`, value)VALUES(1, '张三', 'key1', 'value1'); INSERT INTO hl.test1(id, name, `key`, value)VALUES(2, '张三', 'key2', 'value2'); INSERT INTO hl.test1(id, name, `key`, value)VALUES(3, '李四', 'key3', 'value3'); INSERT INTO hl.test1(id, name, `key`, value)VALUES(4, '李四', 'key4', 'value4'); #测试json_arrayagg SELECT name,json_arrayagg(`key`) as `kyes` from test1 group by name; SELECT name,json_arrayagg(`value`) as `values` from test1 group by name;
-
json_objectagg()
分组查询中,json_objectagg把非分组字段聚合成json对象SELECT name,json_objectagg(`key`,`value`) as a from test1 group by name;
-
json_keys
得到当前json的最外层所有key
格式:json_keys(json,[path])#结果:["a", "b"] select json_keys('{"a": 1, "b": {"c": 30}}'); #结果:["c"] select json_keys('{"a": 1, "b": {"c": 30}}', '$.b');
-
json_search
通过值去查询path
格式:json_search(json,"all/one","搜索的值")
测算表test10如图#结果是"$.des" select json_search(json_data,"all","des1") from test10; #结果是"$.ename" select json_search(json_data,"all","jack") from test10; #结果是null,不能查到数字? select json_search(json_data,"all",1) from test10;
-
json_value
提取json 的值和 json_extract的区别?
格式:json_value(json,path,returntype,“路径是空的时候的默认值”,“路径错误的时候的默认值”)select json_value(json_data,"$.des") from test10; #后面三个参数不能用? select json_value(json_data,"$.des","string","0","0") from test10;
-
member of
判断元素是否存在,和类似 exists 和contans有什么区别?
格式:xxx member of(json)#对json对象使用 select * from test10 where "des1" member of(json_data -> "$.des"); #对json数组使用 select * from test10 where 1 member of(json_data); select * from test10 where 1 member of(json_data->"$[0]") ;
-
json_depth
获取json的深度#空json是1 select json_depth("{}") from dual; #普通一层的json对象是2 select json_depth('{"a":1}') from dual; #对象里面在有数组是3层 select json_depth('{"a":1,"b":[1,2]}');
-
json_length
json_length()得到元素个数#结果4 select json_length('[1,2,3,4]') from dual; #结果0 select json_length('{}') from dual; #结果2 select json_length('{"a":1,"b":2}') from dual;
-
json_valid 有问题?
判断json是否有效,貌似只要是有两层引号就会认为有效# 结果是1 select json_valid("{}") from dual; # 结果是0 ,如果把a改成1 结果是1 select json_valid("a") from dual; # 结果是1 ? select json_valid('"a"') from dual; #结果也是1 ? SELECT JSON_VALID('false'), JSON_VALID('true'); #结果是 0 select json_valid('a') from dual; #结果:1 select json_valid('{"a": 1}'); #结果:0 select json_valid('hello'); #结果:1 select json_valid('"hello"');
-
json_type
得到类型#结果array select json_type('[1,2,3,4]') from dual; #结果 OBJECT select json_type('{}') from dual; #OBJECT select json_type('{"a":1,"b":2}') from dual; #STRING select json_type('""') from dual; #STRING select json_type('"1"') from dual;
有关列长度和记录长度
varchar的长度限制16383,是256*256/4=16384,每添加一个字段需要占用一个字符,16383是在只有一个varchar字段的时候的极限,16383不仅是一个字段的极限也是表记录的极限。
text的上限是65535,并且text是存在行记录以外的,所以它可以突破16383的极限。
相关文章json字效率和普通字段对比
https://www.cnblogs.com/cxygg/p/18293968
标签:test2,age,语法,json,mysql,data,id,select From: https://www.cnblogs.com/cxygg/p/18307956