如何在关联表变更时自动更新MySQL汇总表的JSON数据？

嘿，这个场景我之前做仪表盘优化时也碰到过！要实现关联表变更时自动更新汇总表的JSON数据，MySQL本身就有现成的工具能搞定，给你两个实用方案：

这是最直接的实时同步方式——给那3张关联表分别创建INSERT、UPDATE、DELETE触发器，只要关联表的数据有变化，就自动重新生成对应家庭的JSON并更新目标表。

举个例子（你可以根据自己的表结构替换字段和表名）：
假设关联表是users、user_family（用户-家庭关联表）、families，目标汇总表是dashboard_summary（包含family_id和user_data_json字段）。

先给users表写一个更新触发器：

DELIMITER //
CREATE TRIGGER trigger_update_dashboard_after_user_change
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    -- 找到当前用户所属的家庭ID
    DECLARE target_family_id INT;
    SELECT family_id INTO target_family_id FROM user_family WHERE user_id = NEW.id;

    -- 重新生成该家庭的用户JSON数据并更新汇总表
    UPDATE dashboard_summary ds
    SET user_data_json = (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'user_id', u.id,
                'username', u.username,
                'full_name', u.full_name,
                'family_role', uf.role
                -- 按需添加你需要的其他用户字段
            )
        )
        FROM users u
        JOIN user_family uf ON u.id = uf.user_id
        WHERE uf.family_id = target_family_id
    )
    WHERE ds.family_id = target_family_id;
END //
DELIMITER ;

按照同样的逻辑，给user_family和families表也创建对应的触发器——比如当用户更换家庭、新增/删除家庭成员时，都能触发汇总表的更新。

如果你的关联表有大量批量操作，或者不需要完全实时的同步，可以用MySQL的事件调度器定期刷新整个汇总表的JSON数据。

比如每天凌晨2点自动刷新所有家庭的用户数据：

-- 先确保事件调度器开启
SET GLOBAL event_scheduler = ON;

CREATE EVENT event_refresh_dashboard_summary
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
BEGIN
    UPDATE dashboard_summary ds
    SET user_data_json = (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'user_id', u.id,
                'username', u.username,
                'full_name', u.full_name,
                'family_role', uf.role
            )
        )
        FROM users u
        JOIN user_family uf ON u.id = uf.user_id
        WHERE uf.family_id = ds.family_id
    );
END;

• 性能优化：如果数据量很大，触发器每次都全量查询可能有点影响，可以考虑只更新变更的家庭（像方案1那样），而不是全表更新；另外可以给关联字段加索引，加速JSON生成的查询。
• 事务一致性：触发器会跟着原操作的事务一起执行，所以如果原操作回滚，触发器的更新也会回滚，不用担心数据不一致。
• JSON结构维护：后续如果要调整JSON里的字段，记得同步修改触发器或事件里的JSON_OBJECT内容。
• 先测后用：一定要在测试环境先验证触发器/事件的效果，比如新增一个用户，看看汇总表的JSON是不是自动更新了，避免直接在生产环境踩坑。

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