mysql 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;
数据的写入和修改
全量修改
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函数都是mysql8以后才支持的，使用select version();查询mysql版本
数据提取函数
cast()
cast 可以把json字符串装换成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}');
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_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;
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字效率和普通字段对比
能耍的时候就一定要耍,不能耍的时候一定要学。
--天道酬勤,贵在坚持posted on 2024-07-17 17:36 zhangyukun 阅读(179) 评论(0) 编辑 收藏 举报
