数据库

MySQL5.7中的JSON基本操作指南
数据库 2024/12/27 佚名
3 2

前言

因为项目需要，存储字段存储成了JSON格式，在项目中是将查询出来的值通过jackson转成相应的bean进行处理的，觉得不够简单方便。

MySQL从5.7版本开始就支持JSON格式的数据，操作用起来挺方便的。

建表

在新建表时字段类型可以直接设置为json类型，比如我们创建一张表：

mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info` JSON);

json类型字段可以为NULL

插入数据：

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');

json类型的字段必须时一个有效的json字符串

可以使用JSON_OBJECT()函数构造json对象：

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17));

使用JSON_ARRAY()函数构造json数组：

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));

现在查看test_user表中的数据：

mysql> select * from test_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]} | 
+----+-----------+--------------------------------------------+
3 rows in set (0.04 sec)

查询

表达式： 对象为json列->'$.键', 数组为json列->'$.键[index]'

mysql> select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user; 
+-----------+---------------------+---------------+------------------+ 
| name | info->'$.nick_name' | info->'$.sex' | info->'$.tag[0]' | 
+-----------+---------------------+---------------+------------------+ 
| xiaoming | "小萌"  | 1  | NULL  | 
| xiaohua | NULL  | 0  | NULL  | 
| xiaozhang | NULL  | 1  | 3  | 
+-----------+---------------------+---------------+------------------+ 
3 rows in set (0.04 sec)

等价于：对象为JSON_EXTRACT(json列 , '$.键') ，数组为JSON_EXTRACT(json列 , '$.键[index]')

mysql> select name, JSON_EXTRACT(info, '$.nick_name'), JSON_EXTRACT(info, '$.sex'), JSON_EXTRACT(info, '$.tag[0]') from test_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    | 
+-----------+-----------------------------------+-----------------------------+--------------------------------+ 
3 rows in set (0.04 sec)

不过看到上面"小萌"是带双引号的，这不是我们想要的，可以用JSON_UNQUOTE函数将双引号去掉

mysql> select name, JSON_UNQUOTE(info->'$.nick_name') from test_user where name='xiaoming'; 
+----------+-----------------------------------+ 
| name | JSON_UNQUOTE(info->'$.nick_name') | 
+----------+-----------------------------------+ 
| xiaoming | 小萌    | 
+----------+-----------------------------------+ 
1 row in set (0.05 sec)

也可以直接使用操作符-

mysql> select name, info-'$.nick_name' from test_user where name='xiaoming';
+----------+----------------------+ 
| name | info-'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | 小萌   | 
+----------+----------------------+ 
1 row in set (0.06 sec)

当然属性也可以作为查询条件

mysql> select name, info-'$.nick_name' from test_user where info->'$.nick_name'='小萌'; 
+----------+----------------------+ 
| name | info-'$.nick_name' | 
+----------+----------------------+ 
| xiaoming | 小萌   | 
+----------+----------------------+ 
1 row in set (0.05 sec)

值得一提的是，可以通过虚拟列对JSON类型的指定属性进行快速查询。

创建虚拟列:

mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info-'$.nick_name') VIRTUAL;

注意用操作符-

使用时和普通类型的列查询是一样:

mysql> select name,nick_name from test_user where nick_name='小萌'; 
+----------+-----------+ 
| name  | nick_name | 
+----------+-----------+ 
| xiaoming | 小萌  | 
+----------+-----------+ 
1 row in set (0.05 sec)

更新

使用JSON_INSERT()插入新值，但不会覆盖已经存在的值

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;

看下结果

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name | info          | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
| 2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | 小花  | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

使用JSON_SET()插入新值，并覆盖已经存在的值

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;

看下结果

mysql> select * from test_user where id=3; 
+----+-----------+---------------------------------------------------------------+-----------+ 
| id | name  | info               | nick_name | 
+----+-----------+---------------------------------------------------------------+-----------+ 
| 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90], "nick_name": "小张"} | 小张  | 
+----+-----------+---------------------------------------------------------------+-----------+ 
1 row in set (0.06 sec)

使用JSON_REPLACE()只替换存在的值

mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;

看下结果

mysql> select * from test_user where id=2; 
+----+---------+--------------------------------------------+-----------+ 
| id | name | info          | nick_name | 
+----+---------+--------------------------------------------+-----------+ 
| 2 | xiaohua | {"age": 17, "sex": 1, "nick_name": "小花"} | 小花  | 
+----+---------+--------------------------------------------+-----------+ 
1 row in set (0.06 sec)

可以看到tag没有更新进去

删除

使用JSON_REMOVE()删除JSON元素

mysql> UPDATE test_user SET info = JSON_REMOVE(info, '$.sex', '$.tag') where id=1;

看下结果

mysql> select * from test_user where id=1; 
+----+----------+----------------------------------+-----------+ 
| id | name  | info        | nick_name | 
+----+----------+----------------------------------+-----------+ 
| 1 | xiaoming | {"age": 18, "nick_name": "小萌"} | 小萌  | 
+----+----------+----------------------------------+-----------+ 
1 row in set (0.05 sec)

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。

标签：
mysql,json类型,mysql,json,mysql,json查询
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
无争山庄资源网 Copyright www.whwtcm.com

评论“MySQL5.7中的JSON基本操作指南”

暂无“MySQL5.7中的JSON基本操作指南”评论...
www.whwtcm.com 无争山庄资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
无争山庄资源网 Copyright www.whwtcm.com