MySQL使用JSON_ARRAYAGG批量更新users表titles字段报错求助

错误原因分析 #

你遇到的语法错误，大概率是因为直接在UPDATE语句的SET子句中使用JSON_ARRAYAGG聚合函数，但未通过子查询或预聚合的方式处理分组逻辑。MySQL 8.0的UPDATE语法不支持直接在SET中引用未经过分组关联的聚合函数，这类写法会导致解析器无法识别正确的关联逻辑。

正确实现步骤 #

1. 先为users表添加JSON类型的titles字段 #

如果还未执行添加字段操作，先执行这条语句：

ALTER TABLE users ADD COLUMN titles JSON;

2. 方案一：使用关联子查询更新 #

这种方式会遍历每个用户，通过子查询获取其关联的所有消息标题数组，适合需要统一处理所有用户（包括无消息的用户）的场景：

UPDATE users u
SET titles = COALESCE(
    (SELECT JSON_ARRAYAGG(m.title) FROM messages m WHERE m.user_id = u.id),
    JSON_ARRAY()
);

• COALESCE用于处理无关联消息的用户，将NULL替换为空JSON数组[]，如果不需要此逻辑，可以去掉COALESCE部分。

3. 方案二：使用JOIN结合预聚合查询更新 #

先通过子查询预聚合每个用户的消息标题数组，再关联users表更新，这种方式性能更优，适合数据量较大的场景：

-- 更新有消息关联的用户
UPDATE users u
JOIN (
    SELECT user_id, JSON_ARRAYAGG(title) AS title_array
    FROM messages
    GROUP BY user_id
) m ON u.id = m.user_id
SET u.titles = m.title_array;

-- 可选：为无消息的用户设置空数组
UPDATE users
SET titles = JSON_ARRAY()
WHERE titles IS NULL;

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