文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Mysql 5.7 新特性之 json 类型的增删改查操作和用法

2024-04-02 19:55

关注

近几年来,nosql大行其道,json更是火的一塌糊涂,作为数据库的元老,mysql在5.7版本中添加了对json数据的支持。这片博客主要介绍mysql json类型的增删改查等基本操作的用法。

首先创建一个数据库learnjson,并创建一张表json_start:

create table json_start (
	id int auto_increment PRIMARY KEY not null,
	person json not NULL
);

插入数据 :insert操作

在json_start表中我们添加了一个json类型的字段:person;下面我们就来插入一些数据,

json类型的数据插入方式和普通数据插入方式一样,不同的是插入的是json字符串:

INSERT INTO json_start VALUES(null,'{"coder":[{"name":"张三","age":"18","gender":"男"},{"name":"李四","age":"14","gender":"男"},{"name":"王五","age":"15","gender":"女"}],
	"teacher":[{"name":"刘二","age":"22","gender":"女"},{"name":"赵三","age":"23","gender":"男"},{"name":"王五","age":"15","gender":"女"}],
	"student":[{"name":"张三","age":"18","gender":"男"},{"name":"李四","age":"14","gender":"男"},{"name":"王五","age":"15","gender":"女"}]
}');

需要注意的是,在插入json类型的字符串时,mysql会对json字符串进行格式校验,如果格式有误,就无法插入,并且默认情况下,json数据的大小也是有限制的,默认大小和LONGTEXT ,LONGBLOB 一样,同时他还受限于mysql配置项:max_allowed_packet. 的限制(默认为4M).

我们观察数据库的结果:

数据已经添加到数据库中了,我们试着在插入几条数据:

INSERT INTO json_start VALUES(null,'{"handsome":[{"name":"山山","age":"18","gender":"男"},{"name":"张一山","age":"26","gender":"男"},{"name":"bug4j","age":"18","gender":"男"}],
	"beauty":[{"name":"刘亦菲","age":"18","gender":"女"},{"name":"xixi","age":"23","gender":"女"},{"name":"周冬雨","age":"15","gender":"女"}],
	"fatty":[{"name":"zhangqi","age":"38","gender":"男"},{"name":"pangci","age":"30","gender":"男"},{"name":"王五","age":"15","gender":"女"}]
}');
INSERT INTO json_start VALUES(null,
'[{"name":"奔驰","model":"C260","price":"200RMB"},
{"name":"劳斯莱斯","model":"定制版","price":"166RMB"},
{"name":"奥迪","model":"A6","price":"200000000RMB"}]'
);

结果:

你会发现,虽然最后一条记录的json内容和其他两条记录的内容完全不一样甚至结构都不一样,但是依然能插入成功,因为mysql在插入时只对格式进行校验,格式通过了,就当成字符串存储,并不会考虑与其他记录是否保持一致,事实上这么做没必要。但这同时引入了一个使用时需要注意的事项,就是在定义表的时候就必须尽可能的表以及字段的功能,数据结构明确清楚,并且避免一个字段多种数据混合存储。

更新记录:update 追加内容:

mysqljson数据跟新操作根据json数据的类型范围分为列表操作和对象操作两种类型,都可以直接说那个merge操作。

下面我们试着给第一条记录追加一个manager属性,很显然这是一个对象操作,执行语句如下:

UPDATE json_start set person = json_MERGE(person,'{"manager":[{"name":"laoda","age":"38","gender":"男"}]}') where id=1;

原理就是将原来的json对象和要添加的对象合并,稍后将会描述这其中的合并规则。执行结果如下:

再给第三条记录追加一个对象:

UPDATE json_start set person = json_merge(person,'[{"name":"大众","model":"k1","price":"600000000RMB"}]') where id=3;

 更新记录:追加内容到数组:

追加到数组可以使用JSON_APPEND()(mysql 5.7.9或更早的版本) 或者JSON_ARRAY_APPEND()方法,该操作将指定的对象添加到数组末尾,例如:

update json_start set person = json_ARRAY_APPEND(person,"$",JSON_OBJECT("name","玛莎拉蒂","model","ah","price","168RMB")) where id=3;

执行结果:

更新记录:update 修改json属性: 

mysql提供一个非常灵活的本地方法:json_set(doc,path,value),其中doc为要修改的json列,path为要修改的属性的对象定位表达式,value为新值,例如:

UPDATE json_start set person = json_set(person,'$.coder[0].name','张三三三三三') where id = 1

结果为:

 需要注意的是,当使用json_set()方法去修改一整个对象或者数组时,mysql或将value当成字符串来解析保存,当你的value中包含”时,或自动进行转义,这样存储起来很浪费空间,同时也会产生问题,如果需要修改整个对象,建议使用Json_object(key,value,key2,value2....)来格式化你的对象,例如:

UPDATE json_start set person = json_set(person,'$.coder[0]',JSON_OBJECT("age",18,"name","张sisisisi","gender","男")) where id = 1;
UPDATE json_start set person = json_set(person,"$[0]",JSON_OBJECT("age",18,"name","张wuwuwuuwu","gender","男")) where id = 3;

执行结果为:

删除记录: 

删除记录使用JSON_REMOVE(doc,path1,path2,...)方法,例如:

update json_start set person = json_REMOVE(person,"$[0]") where id = 3;
update json_start set person = json_REMOVE(person,"$.coder[0]") where id = 1;

执行结果为:

查询记录:

查询所有的属性key,使用json_keys(doc)方法:

select id,json_keys(person) from json_start

结果如下:

查询指定的记录中的某个属性或者值,使用Json_extract(doc,path)方法,例如:

select id,json_extract(person,"$.coder[0].name") from json_start where id = 1

结果如下:

或者:

select id,json_extract(person,"$.coder[0]") from json_start where id = 1

当然,你也可以使用通配符‘*’来实现多值查询,例如:

select id,json_extract(person,"$.coder[*].name") from json_start where id = 1
UNION All
select id,json_extract(person,"$.*[*].name") from json_start where id = 1

 结果如下:

需要注意的是,查询返回的结果始终是一个包含查询结果的数组!

扩展: 

创建一个表 t1,basic_info 字段为JSON类型:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `basic_info` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

一、插入一条数据:

INSERT INTO `t1`(`id`, `basic_info`) VALUES (1, '{\"age\": 9, \"name\": \"小明\", \"class\": 3}');

1、查找 JSON 中的某个字段:

比如查询 id=1 的 basic_info 中的 name 字段,可以用以下两种方式:

select basic_info->'$.name' from t1 where id =1;
//或者
select JSON_EXTRACT(basic_info, '$.name') from t1 where id =1;

mysql> select basic_info->’$.name’ from t1 where id =1;
+———————————+
| basic_info->’$.name’ |
+———————————+
| “小明” |
+———————————+

或者:

mysql> select JSON_EXTRACT(basic_info, ‘$.name’) from t1 where id =1;
+——————————————————+
| JSON_EXTRACT(basic_info, ‘$.name’) |
+——————————————————+
| “小明” |
+——————————————————+
1 row in set (0.04 sec)

2、根据JSON中的某个字段查询表中记录:

比如查询name为小明的记录:

select * from t1 where basic_info->'$.name' = '小明';
//或者
select * from t1 where JSON_EXTRACT(basic_info,'$.name') = '小明';

检索JSON数据的方法,文档:https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

二、修改 JSON

修改JSON中字段的方法,文档:https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

1、修改 JSON 中的某个字段:

比如我们修改 id =1 的 basic_info 中的age为 10 岁:

可以使用JSON_REPLACE() 或者 JSON_SET() 函数:

update t1 set basic_info = JSON_REPLACE(basic_info, '$.age', 10) where id =1;
//或者
update t1 set basic_info = JSON_SET(basic_info, '$.age', 10) where id =1;

2、往 JSON 中插入一个新的字段:

比如往 basic_info 中插入一个性别“gender”字段:

可以使用JSON_INSERT() 或者 JSON_SET() 函数:

update t1 set basic_info = JSON_INSERT(basic_info, '$.gender', '男') where id =1;
//或者
update t1 set basic_info = JSON_SET(basic_info, '$.gender', '男') where id =1;

JSON_SET(),JSON_INSERT() 和JSON_REPLACE() 函数的区别:

还有一些其他的函数,详见对应官方文档。

到此这篇关于Mysql 5.7 新特性之 json 类型的增删改查的文章就介绍到这了,更多相关Mysql json 类型的增删改查内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯