MySQL JSON类型字段的查找与更新

电玩女神 2021-09-21 16:12 893阅读 0赞

MySQL 提供了丰富的函数用于 JSON 类型字段的查找与更新，详见[官方文档][Link 1]。

简单记一下使用方法，使用的mysql版本：5.7.28-log：

创建一个表 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}');

### 一、检索 JSON： ###

**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][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][Link 1]

**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() 函数的区别：

*  JSON\_SET() 支持替换现有值，并且支持添加不存在的值。
 *  JSON\_INSERT() 插入值而不替换现有值。
 *  JSON\_REPLACE() 仅替换现有值。

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

[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
[https_dev.mysql.com_doc_refman_8.0_en_json-search-functions.html]: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html