MySQL对JSON数据内对象进行更新

雨巷迷途

已于 2024-07-30 15:31:50 修改

阅读量2k

点赞数 2

分类专栏：数据库文章标签： json mysql 后端数据库

于 2024-02-02 17:26:20 首次发布

本文链接：https://blog.youkuaiyun.com/yuxiangmitu/article/details/135996030

版权

数据库专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了如何在MySQL中使用UPDATE语句针对JSON结构数据进行插入和设置操作，包括JSON_EXTRACT、JSON_SET和JSON_INSERT函数的用法，以及注意事项，如JSON格式验证和MySQL版本限制。

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

-- 校验是否为JSON格式与要改的字段是否存在，存在更新，不存在插入
UPDATE
  表名
SET
  字段名 = 
  CASE WHEN
  JSON_VALID( 字段名 ) AND
  JSON_EXTRACT( 字段名, '$.字段里的对象名.对象内部字段名' ) IS NOT NULL THEN
  JSON_SET( 字段名, '$.字段里的对象名.对象内部字段名', 更新后的值 ) ELSE
  JSON_INSERT( 字段名, '$.字段里的对象名', JSON_OBJECT( '对象内部字段名', 更新后的值 ) )
  END
WHERE
  具体条件;

-- 校验是否为JSON格式与要改的字段是否存在，存在更新，不存在保持原状
UPDATE
  表名
SET
  字段名 = 
  CASE WHEN
  JSON_VALID( 字段名 ) = 1 AND
  JSON_EXTRACT( 字段名, '$.字段里的对象名.对象内部字段名' ) IS NOT NULL THEN
  JSON_SET( 字段名, '$.字段里的对象名.对象内部字段名', 更新后的值 ) ELSE
  字段名
  END
WHERE
  具体条件;

以上SQL用于更新JSON结构数据内部的对象，非对象的键值、对象多层嵌套同理，业务复杂时建议使用java代码转换为JSONObject处理。

例如存在 bill 账单表，定义了 text 类型的字段 bill_extension 以存储JSON数据，有一条数据为 {"customerCode": "a", "commodity": {"price": 10,"num":1}} ，把 commodity 对象里的 price 改为20。

UPDATE
  bill
SET
  bill_extension = 
  CASE WHEN
  JSON_VALID( bill_extension ) = 1 AND
  JSON_EXTRACT( bill_extension, '$.commodity.price' ) IS NOT NULL THEN
  JSON_SET( bill_extension, '$.commodity.price', 20 ) ELSE
  JSON_INSERT( bill_extension, '$.commodity', JSON_OBJECT('price',20) )
  END
WHERE
  id = 1;

注意点：

1.请根据具体业务情况替换代码的中文注释，并确认存储数据符合JSON格式。

2.MySQL5.7以下版本不支持JSON相关函数。

3.更新后JSON数据会按照一定规则重新排序，暂不了解具体细节，有知道的朋友欢迎指点。

4.JSON相关函数介绍推荐以下博客

【MySQL】对JSON数据操作（全网最全）_mysql json_0世界和平0的博客-优快云博客