MySQL如何实现基于JSON文档主键的原子合并更新JSON列？

完全懂你的痛点——MySQL自带的JSON_MERGE_PATCH和JSON_MERGE_PRESERVE都是按数组索引来合并的，根本不认识你数组里的substanceId作为唯一标识，所以要么把旧项覆盖没了，要么重复添加，完全达不到你要的「更新匹配项、保留未修改项、新增新项」的效果。

好在MySQL 8.0+提供了JSON_TABLE函数，能把JSON数组拆成关系型的行数据，这样我们就能用SQL的分组、排序逻辑来实现基于substanceId的原子更新，全程在数据库端完成，不用客户端来回折腾，也避免了批量更新时的锁问题。

直接上解决方案，假设你的表叫your_table，存储JSON数组的列叫substances，下面是可直接复用的UPDATE语句：

UPDATE your_table t
SET substances = (
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'substanceId', ranked.substanceId,
            'text', ranked.text
        )
        -- 可选：保持原有项的顺序，新增项放最后
        ORDER BY CASE 
            WHEN ranked.priority = 1 THEN (
                SELECT idx 
                FROM JSON_TABLE(t.substances, '$[*]' COLUMNS(
                    idx FOR ORDINALITY,
                    substanceId INT PATH '$.substanceId'
                )) o 
                WHERE o.substanceId = ranked.substanceId
            ) 
            ELSE 9999 
        END
    )
    FROM (
        SELECT 
            substanceId, 
            text,
            priority,
            -- 按substanceId分组，优先保留更新数据（优先级2）
            ROW_NUMBER() OVER (PARTITION BY substanceId ORDER BY priority DESC) AS rn
        FROM (
            -- 原有数据：优先级设为1（低优先级）
            SELECT 
                substanceId, 
                text, 
                1 AS priority
            FROM JSON_TABLE(t.substances, '$[*]' COLUMNS(
                substanceId INT PATH '$.substanceId',
                text VARCHAR(255) PATH '$.text'
            )) o
            UNION ALL
            -- 更新数据：优先级设为2（高优先级）
            SELECT 
                substanceId, 
                text, 
                2 AS priority
            FROM JSON_TABLE(
                '[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]',
                '$[*]' COLUMNS(
                    substanceId INT PATH '$.substanceId',
                    text VARCHAR(255) PATH '$.text'
                )
            ) u
        ) combined
    ) ranked
    -- 只保留每个substanceId的最高优先级行
    WHERE ranked.rn = 1
)
-- 这里加你的过滤条件，比如批量更新多行
WHERE id IN (1, 2, 3);

原理拆解： #

1. JSON_TABLE拆分数组：把原JSON数组和待更新的JSON数组都拆成关系型行，每行包含substanceId、text和优先级标记。
2. 合并并标记优先级：用UNION ALL把原有数据（优先级1）和更新数据（优先级2）合并，更新数据的优先级更高。
3. 窗口函数筛选有效行：用ROW_NUMBER()按substanceId分组，每组只保留优先级最高的行——也就是如果某个substanceId有更新数据，就用更新后的内容，否则保留原有内容。
4. 重新聚合为JSON数组：用JSON_ARRAYAGG把筛选后的行重新组合成JSON数组，还可以通过ORDER BY保持原有项的顺序，新增项放在最后。

动态参数优化（适合应用程序调用） #

如果要在应用中动态传入更新的JSON数据，可以用预处理语句，避免硬编码：

PREPARE update_substances_stmt FROM '
UPDATE your_table t
SET substances = (
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            ''substanceId'', ranked.substanceId,
            ''text'', ranked.text
        )
        ORDER BY CASE 
            WHEN ranked.priority = 1 THEN (
                SELECT idx 
                FROM JSON_TABLE(t.substances, ''$[*]'' COLUMNS(
                    idx FOR ORDINALITY,
                    substanceId INT PATH ''$.substanceId''
                )) o 
                WHERE o.substanceId = ranked.substanceId
            ) 
            ELSE 9999 
        END
    )
    FROM (
        SELECT 
            substanceId, 
            text,
            priority,
            ROW_NUMBER() OVER (PARTITION BY substanceId ORDER BY priority DESC) AS rn
        FROM (
            SELECT substanceId, text, 1 AS priority
            FROM JSON_TABLE(t.substances, ''$[*]'' COLUMNS(
                substanceId INT PATH ''$.substanceId'',
                text VARCHAR(255) PATH ''$.text''
            )) o
            UNION ALL
            SELECT substanceId, text, 2 AS priority
            FROM JSON_TABLE(?, ''$[*]'' COLUMNS(
                substanceId INT PATH ''$.substanceId'',
                text VARCHAR(255) PATH ''$.text''
            )) u
        ) combined
    ) ranked
    WHERE ranked.rn = 1
)
WHERE id = ?';

-- 示例参数：更新的JSON和目标行ID
SET @update_json = '[{"substanceId": 182, "text": "substance_name_182_new"}, {"substanceId": 184, "text": "substance_name_184"}]';
SET @target_id = 1;

EXECUTE update_substances_stmt USING @update_json, @target_id;
DEALLOCATE PREPARE update_substances_stmt;

这个方案全程在MySQL端执行，是原子操作，批量更新多行时也不需要客户端加锁，效率和安全性都比「先查后改」高得多。

内容的提问来源于stack exchange，提问作者Tom Raganowicz