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

Saggitarxm

已于 2023-04-12 18:21:54 修改

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： json mysql 数据库 json_set

于 2023-02-11 17:55:20 首次发布

本文链接：https://blog.youkuaiyun.com/xuezhangjun0121/article/details/128986404

MySQL 专栏收录该内容

57 篇文章

订阅专栏

文章介绍了如何在MySQL中对JSON类型字段进行检索和修改。通过示例展示了使用JSON_EXTRACT和JSON_REPLACE等函数查询和更新JSON对象的特定字段，同时解释了JSON_SET和JSON_INSERT的区别，前者能替换或添加值，后者只插入不替换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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

二、修改 JSON

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;

SON_SET()，JSON_INSERT() 和JSON_REPLACE() 函数的区别：

JSON_SET() 支持替换现有值，并且支持添加不存在的值。

JSON_INSERT() 插入值而不替换现有值。

JSON_REPLACE() 仅替换现有值。