mysql5.7中json数据类型操作函数

未分类 , , 发表评论


在navicat12上自造数据做的一些测试,可以直接拷贝到navicat中执行。

-- CREATE TABLE test_json01(c1 VARCHAR(50) PRIMARY KEY,c2 JSON);
SELECT a.* from test_json01 a;
/*
-- 插入
*/
INSERT INTO test_json01 VALUES('001','{"name":"郭靖","age":"100","gongfu":"降龙十八掌"}');
INSERT INTO test_json01 VALUES('002','{"name":"欧阳峰","age":"100","gongfu":"蛤蟆功"}');
INSERT INTO test_json01 VALUES('003','{"name":"张无忌","age":"1000","gongfu":"乾坤大挪移"}');
INSERT INTO test_json01 VALUES('004','{"name":"宁中则","age":"15","gongfu":"独孤九剑"}');
-- 复杂json
INSERT INTO test_json01 VALUES('101','{"group":"product","age":"1000","name":["小昭","逍遥子","乌老大","童姥"]}');
INSERT INTO test_json01 VALUES('102','{"group":"datahandle","age":"1000","name":["毋江","杨靖"]}');
INSERT INTO test_json01 VALUES('201','[{"name":"郭靖","age":"100","gongfu":"降龙十八掌"},{"name":"欧阳峰","age":"100","gongfu":"蛤蟆功"}]');
INSERT INTO test_json01 VALUES('501','[{"group":"product","address":"xiaoyao","persons":[{"name":"小昭","age":"12","gongfu":["凌波微步","小无相功"]},{"name":"逍遥子","age":"18","gongfu":[{"gongfu1":"凌波微步"},{"gongfu2":"小无相功"}]}]},{"group":"dev","address":"shaolin","persons":[{"name":"周芷若","age":"12","gongfu":["易筋经","燃木刀法"]},{"name":"程灵素","age":"18","gongfu":[{"gongfu1":"拈花指"},{"gongfu2":"金刚不坏"}]},{"name":"莹莹","age":"11","gongfu":[{"gongfu1":"清心普善曲"},{"gongfu2":["影子鞭法","六合鞭法"]}]}]}]');

INSERT into test_json01 VALUES('601','adaff');-- 失败
INSERT into test_json01 VALUES('602','{}');-- 成功
INSERT into test_json01 VALUES('603','[]');-- 成功

-- 结论一:mysql的json类型可以存储由任意json_object和json_array组成的数据格式的数据。

/*
-- 查询
*/

SELECT a.* FROM test_json01 a;
-- 结论:通过查询结果可以看到,mysql会对存入的json_object做排序处理,而并不是插入的顺序
-- 如果使用like,则json类型会当作字符串处理
SELECT a.* FROM test_json01 a where a.c2 like '%age%';-- 有效
SELECT a.* FROM test_json01 a where a.c2 like '%郭%';
-- 以下无效
SELECT a.* FROM test_json01 a where a.c2 = '{"name":"郭靖","age":"100","gongfu":"降龙十八掌"}';
SELECT a.* FROM test_json01 a where a.c2 = '{"age":"100","name":"郭靖","gongfu":"降龙十八掌"}';
SELECT a.* FROM test_json01 a where a.c2 = '{"age": "100", "name": "郭靖", "gongfu": "降龙十八掌"}';
SELECT a.* FROM test_json01 a where a.c2 = '[]';
SELECT a.* FROM test_json01 a where a.c2 = '{}';

-- 函数JSON_OBJECT:构造函数
-- 结论:参数须为偶数个
SELECT JSON_OBJECT('name','郭靖','age','50');
-- 函数JSON_ARRAY:构造函数
SELECT JSON_ARRAY('name','郭靖','age','50');
-- $符是操作json的标识,json_object使用“.”,json_array使用下标"[0]",下标从0开始

-- 函数JSON_EXTRACT:从json文档抽取数据
-- 结论:数据越界不会异常,只会返回null
-- 返回的数据格式也是标准json格式,可以套用JSON_EXTRACT进行处理
SELECT a.c1,JSON_EXTRACT(a.c2,'$.name'),JSON_EXTRACT(a.c2,'$.name[0]'),JSON_EXTRACT(a.c2,'$.name[1]') FROM test_json01 a;
SELECT a.c1,JSON_EXTRACT(a.c2,'$[0]'),JSON_EXTRACT(a.c2,'$[1]'),JSON_EXTRACT(a.c2,'$[0].persons')FROM test_json01 a;
SELECT a.c1,JSON_EXTRACT(a.c2,'$[0]'),JSON_EXTRACT(JSON_EXTRACT(a.c2,'$[0]'),'$.name'),JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(a.c2,'$[0]'),'$.name'),'$[0]') FROM test_json01 a;
SELECT a.*,JSON_EXTRACT(a.c2,'$.name[1]'),JSON_EXTRACT(a.c2,'$.name[1].abc') FROM test_json01 a WHERE JSON_EXTRACT(a.c2,'$.name[1]') = '逍遥子';

-- 函数JSON_KEYS:查看json数据有哪些key
-- 结论:只能返回path路径下的首层的key,如果首层是json_array则返回null
-- 如果是一个空的json_object,则返回一个空的数组格式字符串,如果为null,则返回null
SELECT a.c1,a.c2,JSON_KEYS(a.c2) FROM test_json01 a;
SELECT a.c1,a.c2,JSON_KEYS(a.c2,'$[1]') FROM test_json01 a;
-- 函数JSON_DEPTH:查看json深度
SELECT a.*,JSON_DEPTH(a.c2) FROM test_json01 a;
SELECT a.*,JSON_DEPTH(JSON_EXTRACT(a.c2,'$.name')),JSON_DEPTH(JSON_EXTRACT(a.c2,'$[1]')) FROM test_json01 a;
-- 函数JSON_TYPE:查看json类型
-- 结论:配合JSON_DEPTH和JSON_KEYS应该可以做通用解析
SELECT a.*,JSON_TYPE(a.c2) FROM test_json01 a;
SELECT a.*,JSON_TYPE(JSON_EXTRACT(a.c2,'$.name')),JSON_TYPE(JSON_EXTRACT(a.c2,'$[1]')) FROM test_json01 a;
-- 函数JSON_VALID:判断是否有效的json格式
-- 结论:某些字符串也被认为是json格式
SELECT a.*,JSON_VALID(a.c1),JSON_VALID(a.c2) FROM test_json01 a;
-- 函数JSON_LENGTH:查看json的长度
-- 结论:此函数必须参数满足json格式,否则会sql异常
-- 如果参数为json_object,则返回key的个数,如果为json_array,则返回json_array长度
SELECT a.*,JSON_LENGTH(a.c1) FROM test_json01 a;-- 无效
SELECT a.*,JSON_LENGTH(a.c2) FROM test_json01 a;
SELECT a.*,JSON_LENGTH(a.c2),JSON_LENGTH(a.c2,'$[1]') FROM test_json01 a;
-- 函数JSON_CONTAINS:查询是否存在包括的数据
-- 结论:只能查询当前层级,所以使用范围于小like,效率大于like(猜测待验证)
SELECT a.*,JSON_CONTAINS('["name","age"]',a.c2) FROM test_json01 a;
SELECT a.*,JSON_CONTAINS('["郭靖","100"]',a.c2->'$.age') FROM test_json01 a;
-- 函数JSON_SEARCH:查询包含字符串的paths
-- 结论:使用'one'返回查到的第一个路径,使用‘all’返回一个路径的数组
-- 不能替代like
SELECT a.*,JSON_SEARCH(a.c2,'one','100'),JSON_SEARCH(a.c2,'all','100') FROM test_json01 a;
SELECT a.*,JSON_SEARCH(a.c2,'one','郭靖'),JSON_SEARCH(a.c2,'all','郭靖') FROM test_json01 a;
SELECT a.*,JSON_SEARCH(a.c2,'one','郭靖'),JSON_SEARCH(a.c2,'all','郭') FROM test_json01 a;
SELECT a.* FROM test_json01 a WHERE a.c2 like '%郭%';
SELECT a.*,JSON_SEARCH(a.c2,'one','凌波微步'),JSON_SEARCH(a.c2,'all','凌波微步') FROM test_json01 a;
SELECT a.* FROM test_json01 a WHERE JSON_SEARCH(a.c2,'one','郭靖') is not null;

/*
-- 修改
*/
-- ALTER TABLE test_json01 ADD COLUMN c3 json NULL AFTER c2;
-- UPDATE test_json01 set c3=c2;
SELECT a.* from test_json01 a;

-- 函数JSON_SET:操作json插入或修改数据
-- 结论:可见json类型数据的修改也是做整体替换,josn_set只是改造原数据的一个函数
-- 如果key存在,则进行替换,如果key不存在,则进行添加
-- 使用单引号方式加入的json_array数据,是以字符串类型存入的,所以需要经过转换才能加入json_array数据
UPDATE test_json01 a set a.c3 = JSON_SET(a.c3,'$.name','章攀','$.sex','男') WHERE a.c1 = '001';
UPDATE test_json01 a set a.c3 = JSON_SET(a.c3,'$.name','王静','$.sex','["男","女"]') WHERE a.c1 = '002';
UPDATE test_json01 a set a.c3 = JSON_SET(a.c3,'$.name','王静','$.sex','{["男","女"]}') WHERE a.c1 = '002';
UPDATE test_json01 a set a.c3 = JSON_SET(a.c3,'$.name','王静','$.sex',JSON_ARRAY('男','女')) WHERE a.c1 = '002';
-- 函数JSON_INSERT:插入
-- 结论:不存在才插入,存在则不操作
-- 不会自动增加路径
SELECT a.c2,JSON_INSERT(a.c2,'$.name.name1','郭','$.age','200','$.sex','男') FROM test_json01 a;
-- 函数JSON_REPLACE:替换
-- 结论:存在则替换,不存在不操作
-- 不会自动增加路径
SELECT a.c2,JSON_REPLACE(a.c2,'$.name.name1','郭','$.age','200','$.sex','男') FROM test_json01 a;
-- 函数JSON_APPEND:已废弃
SELECT a.c2,JSON_APPEND(a.c2,'$.name','abc') FROM test_json01 a;-- 无效
-- 函数JSON_ARRAY_APPEND:追加数据
-- 结论:在路径的后面追加数据,如果不是array,则先自动封装成json_array再追加
-- 支持追加json_object数据,如果以json_object格式写,则以字符串追求
-- 支持追加json_array,但是作为数组元素追加,而非拼接数据
SELECT a.c2,JSON_ARRAY_APPEND(a.c2,'$.name','abc') FROM test_json01 a;
SELECT a.c2,JSON_ARRAY_APPEND(a.c2,'$.name','{"a":"b"}') FROM test_json01 a;
SELECT a.c2,JSON_ARRAY_APPEND(a.c2,'$.name', json_object('abc','edf')) FROM test_json01 a;
SELECT a.c2,JSON_ARRAY_APPEND(a.c2,'$.name', json_array('abc','edf')) FROM test_json01 a;
UPDATE test_json01 a set a.c3 = JSON_ARRAY_APPEND(a.c2,'$.name','abc') WHERE a.c1 = '003';
-- 函数JSON_ARRAY_INSERT:插入数据
-- 结论:sql需要路径为json_array的路径,并在json_array的下标的前面插入数据
-- 如果给出的路径非json_array,则不做操作,如果给出的下标超出长度,则在最后插入
SELECT a.c2,JSON_ARRAY_INSERT(a.c2,'$.name','abc') FROM test_json01 a WHERE a.c1='001';-- 无效
SELECT a.c2,JSON_ARRAY_INSERT(a.c2,'$.name','abc') FROM test_json01 a WHERE a.c1='101';-- 无效
SELECT a.c2,JSON_ARRAY_INSERT(a.c2,'$.name[1]', json_array('abc','edf')) FROM test_json01 a;
SELECT a.c2,JSON_ARRAY_INSERT(a.c2,'$.name[3]','abc') FROM test_json01 a;
-- 函数JSON_MERGE:合并json
SELECT a.*,JSON_MERGE(a.c2,a.c3) FROM test_json01 a;
SELECT a.*,JSON_MERGE(a.c2,a.c3,'{"笑":"书"}') FROM test_json01 a;
SELECT a.*,JSON_MERGE(a.c2,a.c3,JSON_OBJECT('笑书','神侠')) FROM test_json01 a;
SELECT a.*,JSON_MERGE(a.c2,a.c3,json_array('笑书','神侠','倚碧鸳')) FROM test_json01 a;
-- 函数JSON_REMOVE:移除数据
-- 结论:如果存在则移除,支持多个路径
-- 如果是json_object,则默认为json_array[0]
SELECT a.c2,JSON_REMOVE(a.c2,'$.name','$.name.name1','$[1]') FROM test_json01 a;
SELECT a.c2,JSON_REMOVE(a.c2,'$[0].name','$.name[2]') FROM test_json01 a;
SELECT a.c2,JSON_REMOVE(a.c2,'$[1].name','$.name[2]') FROM test_json01 a;
SELECT a.c2,JSON_REMOVE(a.c2,'$.name[2]') FROM test_json01 a;
SELECT a.c2,JSON_REMOVE(a.c2,'$[3]') FROM test_json01 a;

/*
-- 删除
*/
-- DELETE FROM test_json01 where c1='501';

发表评论

邮箱地址不会被公开。 必填项已用*标注

昵称 *