mysql json类型和虚拟列总结

最新推荐文章于 2025-03-01 15:27:43 发布

一枚小荔枝

最新推荐文章于 2025-03-01 15:27:43 发布

阅读量184

点赞数

文章标签： mysql json 数据库

字段定义和插入

创建一个带有json字段的表
插入记录

虚拟列和索引

添加json格式的数据列可以方便的查询
虚拟列和普通列之间不能互转，虚拟转普通和普通转虚拟都会报下面的错误
虚拟列数据来源不仅可以是json列，也可以引用普通字段
虚拟列修改
虚拟列索引
不创建虚拟列，也可以对json结构创建索引

数据的写入和修改

全量修改
部分修改

json_insert
json_replace
json_set

数组修改

json_array_append
json_array_insert

数据合并

json_merge
json_merge_preserve
json_merge_patch

删除数据

json_remove

数据的查询和获取

数据提取函数

cast()
json_extract
-->和-->>，可以用于json属性提取
json_unquote和json_quote

查询相关函数

json_contains_path
json_contains
json_overlaps
member of

分组聚合

json_arrayagg()
json_objectagg()

其他信息获取

json_keys
json_search
json_value
json_depth
json_length
json_valid
json_type

有关列长度和记录长度
相关文章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');

虚拟列和普通列之间不能互转，虚拟转普通和普通转虚拟都会报下面的错误

虚拟列数据来源不仅可以是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_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格式

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对象

其他信息获取

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如图

mysql json类型和虚拟列总结_Data

#结果是"$.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;