MySQL5.7中的JSON基本操作指南
前言
因为项目需要，存储字段存储成了JSON格式，在项目中是将查询出来的值通过jackson转成相应的bean进行处理的，觉得不够简单方便。
MySQL从5.7版本开始就支持JSON格式的数据，操作用起来挺方便的。
建表
在新建表时字段类型可以直接设置为json类型，比如我们创建一张表：
mysql>CREATETABLE`test_user`(`id`INTPRIMARYKEYAUTO_INCREMENT,`name`VARCHAR(50)NOTNULL,`info`JSON);
json类型字段可以为NULL
插入数据：
mysql>INSERTINTOtest_user(`name`,`info`)VALUES('xiaoming','{"sex":1,"age":18,"nick_name":"小萌"}');
json类型的字段必须时一个有效的json字符串
可以使用JSON_OBJECT()函数构造json对象：
mysql>INSERTINTOtest_user(`name`,`info`)VALUES('xiaohua',JSON_OBJECT("sex",0,"age",17));
使用JSON_ARRAY()函数构造json数组：
mysql>INSERTINTOtest_user(`name`,`info`)VALUES('xiaozhang',JSON_OBJECT("sex",1,"age",19,"tag",JSON_ARRAY(3,5,90)));
现在查看test_user表中的数据：
mysql>select*fromtest_user;
+----+-----------+--------------------------------------------+
|id|name|info|
+----+-----------+--------------------------------------------+
|1|xiaoming|{"age":18,"sex":1,"nick_name":"小萌"}|
|2|xiaohua|{"age":17,"sex":0}|
|3|xiaozhang|{"age":19,"sex":1,"tag":[3,5,90]}|
+----+-----------+--------------------------------------------+
3rowsinset(0.04sec)
查询
表达式：对象为json列->'$.键',数组为json列->'$.键[index]'
mysql>selectname,info->'$.nick_name',info->'$.sex',info->'$.tag[0]'fromtest_user; +-----------+---------------------+---------------+------------------+ |name|info->'$.nick_name'|info->'$.sex'|info->'$.tag[0]'| +-----------+---------------------+---------------+------------------+ |xiaoming|"小萌"|1|NULL| |xiaohua|NULL|0|NULL| |xiaozhang|NULL|1|3| +-----------+---------------------+---------------+------------------+ 3rowsinset(0.04sec)
等价于：对象为JSON_EXTRACT(json列,'$.键')，数组为JSON_EXTRACT(json列,'$.键[index]')
mysql>selectname,JSON_EXTRACT(info,'$.nick_name'),JSON_EXTRACT(info,'$.sex'),JSON_EXTRACT(info,'$.tag[0]')fromtest_user; +-----------+-----------------------------------+-----------------------------+--------------------------------+ |name|JSON_EXTRACT(info,'$.nick_name')|JSON_EXTRACT(info,'$.sex')|JSON_EXTRACT(info,'$.tag[0]') |+-----------+-----------------------------------+-----------------------------+--------------------------------+ |xiaoming|"小萌"|1|NULL| |xiaohua|NULL|0|NULL| |xiaozhang|NULL|1|3| +-----------+-----------------------------------+-----------------------------+--------------------------------+ 3rowsinset(0.04sec)
不过看到上面"小萌"是带双引号的，这不是我们想要的，可以用JSON_UNQUOTE函数将双引号去掉
mysql>selectname,JSON_UNQUOTE(info->'$.nick_name')fromtest_userwherename='xiaoming'; +----------+-----------------------------------+ |name|JSON_UNQUOTE(info->'$.nick_name')| +----------+-----------------------------------+ |xiaoming|小萌| +----------+-----------------------------------+ 1rowinset(0.05sec)
也可以直接使用操作符->>
mysql>selectname,info->>'$.nick_name'fromtest_userwherename='xiaoming'; +----------+----------------------+ |name|info->>'$.nick_name'| +----------+----------------------+ |xiaoming|小萌| +----------+----------------------+ 1rowinset(0.06sec)
当然属性也可以作为查询条件
mysql>selectname,info->>'$.nick_name'fromtest_userwhereinfo->'$.nick_name'='小萌'; +----------+----------------------+ |name|info->>'$.nick_name'| +----------+----------------------+ |xiaoming|小萌| +----------+----------------------+ 1rowinset(0.05sec)
值得一提的是，可以通过虚拟列对JSON类型的指定属性进行快速查询。
创建虚拟列:
mysql>ALTERTABLE`test_user`ADD`nick_name`VARCHAR(50)GENERATEDALWAYSAS(info->>'$.nick_name')VIRTUAL;
注意用操作符->>
使用时和普通类型的列查询是一样:
mysql>selectname,nick_namefromtest_userwherenick_name='小萌'; +----------+-----------+ |name|nick_name| +----------+-----------+ |xiaoming|小萌| +----------+-----------+ 1rowinset(0.05sec)
更新
使用JSON_INSERT()插入新值，但不会覆盖已经存在的值
mysql>UPDATEtest_userSETinfo=JSON_INSERT(info,'$.sex',1,'$.nick_name','小花')whereid=2;
看下结果
mysql>select*fromtest_userwhereid=2;
+----+---------+--------------------------------------------+-----------+
|id|name|info|nick_name|
+----+---------+--------------------------------------------+-----------+
|2|xiaohua|{"age":17,"sex":0,"nick_name":"小花"}|小花|
+----+---------+--------------------------------------------+-----------+
1rowinset(0.06sec)
使用JSON_SET()插入新值，并覆盖已经存在的值
mysql>UPDATEtest_userSETinfo=JSON_INSERT(info,'$.sex',0,'$.nick_name','小张')whereid=3;
看下结果
mysql>select*fromtest_userwhereid=3;
+----+-----------+---------------------------------------------------------------+-----------+
|id|name|info|nick_name|
+----+-----------+---------------------------------------------------------------+-----------+
|3|xiaozhang|{"age":19,"sex":1,"tag":[3,5,90],"nick_name":"小张"}|小张|
+----+-----------+---------------------------------------------------------------+-----------+
1rowinset(0.06sec)
使用JSON_REPLACE()只替换存在的值
mysql>UPDATEtest_userSETinfo=JSON_REPLACE(info,'$.sex',1,'$.tag','[1,2,3]')whereid=2;
看下结果
mysql>select*fromtest_userwhereid=2;
+----+---------+--------------------------------------------+-----------+
|id|name|info|nick_name|
+----+---------+--------------------------------------------+-----------+
|2|xiaohua|{"age":17,"sex":1,"nick_name":"小花"}|小花|
+----+---------+--------------------------------------------+-----------+
1rowinset(0.06sec)
可以看到tag没有更新进去
删除
使用JSON_REMOVE()删除JSON元素
mysql>UPDATEtest_userSETinfo=JSON_REMOVE(info,'$.sex','$.tag')whereid=1;
看下结果
mysql>select*fromtest_userwhereid=1;
+----+----------+----------------------------------+-----------+
|id|name|info|nick_name|
+----+----------+----------------------------------+-----------+
|1|xiaoming|{"age":18,"nick_name":"小萌"}|小萌|
+----+----------+----------------------------------+-----------+
1rowinset(0.05sec)
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。