MySQL5.7中的JSON基本操作指南

前言

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

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

建表

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

?

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

json类型字段可以为NULL

插入数据：

?

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

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

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

?

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

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

?

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

现在查看test_user表中的数据：

?

1 2 3 4 5 6 7 8 9

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]'

?

1 2 3 4 5 6 7 8 9

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]')

?

1 2 3 4 5 6 7 8 9

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函数将双引号去掉

?

1 2 3 4 5 6 7

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)

也可以直接使用操作符->>

?

1 2 3 4 5 6 7

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

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

?

1 2 3 4 5 6 7

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类型的指定属性进行快速查询。

创建虚拟列:

?

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

注意用操作符->>

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

?

1 2 3 4 5 6 7

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

更新

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

?

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

看下结果

?

1 2 3 4 5 6 7

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()插入新值，并覆盖已经存在的值

?

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

看下结果

?

1 2 3 4 5 6 7

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()只替换存在的值

?

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

看下结果

?

1 2 3 4 5 6 7

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元素

?

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

看下结果

?

1 2 3 4 5 6 7

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)

总结

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

原文链接：https://segmentfault.com/a/1190000018612255