MySQL 5.7中如何通过单条查询修改JSON数组内所有对象的指定属性

好问题！你遇到的问题其实是MySQL 5.7 JSON函数的局限性导致的——MySQL 5.7的JSON_SET并不支持$."*"这种通配符路径来批量修改数组里的所有元素，这个灵活的路径匹配是MySQL 8.0.4之后才加入的特性。不过我们可以通过一些技巧用单条查询实现你的需求，具体方案如下：

实现方案 #

核心思路是先把JSON数组拆分成单个元素，逐个修改content属性后再重新聚合为数组，最后更新回原表。这里需要借助一个数字序列来遍历数组的每个索引：

UPDATE posts p
JOIN (
    SELECT 
        id,
        JSON_ARRAYAGG(
            JSON_SET(
                JSON_EXTRACT(comments, CONCAT('$[', idx, ']')),
                '$.content',
                'Lorem ipsum ...'
            )
        ) AS updated_comments
    FROM posts
    -- 生成索引序列，覆盖你comments数组的最大可能长度，比如最多有5条评论就写到4
    JOIN (
        SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    ) AS indices
    ON idx < JSON_LENGTH(comments)
    GROUP BY id
) AS updates ON p.id = updates.id
SET p.comments = updates.updated_comments;

代码解释 #

• 索引序列生成：内层的indices子查询生成从0开始的连续数字，你需要根据业务中comments数组的最大长度来扩展这个序列（比如数组最多有10条评论，就需要添加到SELECT 9）。
• 遍历数组元素：通过JSON_LENGTH(comments)过滤掉超出当前数组长度的索引，避免处理不存在的元素。
• 修改单个元素：用JSON_EXTRACT取出对应索引的评论对象，再用JSON_SET替换它的content属性为目标字符串。
• 重新聚合数组：JSON_ARRAYAGG会把修改后的单个评论对象重新组合成完整的JSON数组。
• 更新原表：通过JOIN关联原表和更新后的结果集，把新的数组写回comments列。

注意事项 #

如果你的comments数组长度不确定且可能非常大，手动扩展索引序列会比较麻烦，这种情况下建议用存储过程来动态生成索引序列，但如果是单条查询的话，上面的方案完全可以满足需求。

内容的提问来源于stack exchange，提问作者pécé