MySQL8中如何依据toUserId更新JSON数组内指定JSON对象的字段

实现逻辑 #

你需要定位JSON数组中同时匹配toUserId为指定值、deliveryStatus为0的元素，将对应元素的deliveryStatus修改为1，可通过MySQL 8内置的JSON函数组合完成操作。

单匹配场景代码示例 #

假设存储群聊消息的表名为group_message，存储用户投递回执的JSON数组字段名为receipt_info，更新语句如下：

UPDATE group_message
SET receipt_info = JSON_SET(
    receipt_info,
    -- 替换路径属性为要更新的deliveryStatus
    REPLACE(
        JSON_UNQUOTE(JSON_SEARCH(receipt_info, 'one', 160, NULL, '$[*].toUserId')),
        '.toUserId',
        '.deliveryStatus'
    ),
    1
)
WHERE
    -- 可额外添加消息的过滤条件，如msg_id = 'xxx'
    JSON_CONTAINS(
        receipt_info,
        JSON_OBJECT('toUserId', 160, 'deliveryStatus', 0)
    ) = 1;

语句说明 #

1. JSON_SEARCH函数：查找数组中第一个toUserId为160的元素路径，返回值格式为"$[2].toUserId"
2. REPLACE函数：把路径中末尾的.toUserId替换为.deliveryStatus，得到需要更新的字段路径
3. JSON_SET函数：按照生成的路径，将对应deliveryStatus的值设置为1
4. WHERE条件的JSON_CONTAINS：只有数组中存在符合toUserId=160、deliveryStatus=0的元素时才执行更新，避免无匹配时修改字段数据

业务优化建议 #

如果这类回执更新操作频率较高，不推荐使用JSON字段存储回执数据：

• JSON字段每次更新都需要重写整个数组内容，数据量较大时性能损耗非常明显
• 无法直接为JSON数组内的字段创建索引，匹配筛选的效率远低于关系型表结构
  建议拆分为独立的回执关联表group_message_receipt，字段可设计为msg_id、to_user_id、delivery_status、delivery_date、seen、seen_date，后续更新、查询操作直接操作关联表即可，性能和可维护性都有明显提升。

内容的提问来源于stack exchange，提问作者SYED MUSTAFA HUSSAIN