数据库

MySQL5.7中的JSON基本操作指南
数据库 2024/11/5 佚名
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.dyhadc.com

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

暂无“MySQL5.7中的JSON基本操作指南”评论...
www.dyhadc.com 白云城资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源
一句话新闻
一口气升级7个大模型SaaS应用，百度智能云：突出一个“开箱即用” - 2024/11/5

这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。

一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。

另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。

稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！

昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。

这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。

而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网 更多链接
白云城资源网 Copyright www.dyhadc.com