技术求助：基于T2表更新T1表列值（T1的COL2含多值）

假设T1的COL1和T2的COL11是关联匹配的键（用来定位需要同步的行），针对T1.COL2是多值的场景，分两种情况给出自动同步的方案：

场景1：T1.COL2是逗号分隔的字符串 #

MySQL 实现 #

通过触发器在T2更新后自动同步T1：

DELIMITER //
CREATE TRIGGER sync_t1_after_t2_update
AFTER UPDATE ON T2
FOR EACH ROW
BEGIN
    -- 重新拼接对应关联键的所有T2.COL21值，更新T1.COL2
    UPDATE T1
    SET COL2 = (
        SELECT GROUP_CONCAT(COL21 SEPARATOR ',')
        FROM T2
        WHERE T2.COL11 = T1.COL1
    )
    WHERE T1.COL1 = NEW.COL11; -- 只更新被修改的关联行
END //
DELIMITER ;

如果需要同步T2的插入/删除操作，复制上述触发器逻辑，创建AFTER INSERT和AFTER DELETE触发器即可。

PostgreSQL 实现 #

用聚合函数+触发器实现：

-- 先定义同步函数
CREATE OR REPLACE FUNCTION sync_t1_from_t2()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE T1
    SET COL2 = (
        SELECT string_agg(COL21, ',')
        FROM T2
        WHERE T2.COL11 = T1.COL1
    )
    WHERE T1.COL1 = NEW.COL11;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 绑定到T2的更新事件
CREATE TRIGGER t2_update_sync_t1
AFTER UPDATE ON T2
FOR EACH ROW
EXECUTE FUNCTION sync_t1_from_t2();

场景2：T1.COL2是JSON数组类型 #

MySQL 8.0+ 实现 #

利用JSON_ARRAYAGG生成JSON数组：

DELIMITER //
CREATE TRIGGER sync_t1_json_after_t2_update
AFTER UPDATE ON T2
FOR EACH ROW
BEGIN
    UPDATE T1
    SET COL2 = (
        SELECT JSON_ARRAYAGG(COL21)
        FROM T2
        WHERE T2.COL11 = T1.COL1
    )
    WHERE T1.COL1 = NEW.COL11;
END //
DELIMITER ;

PostgreSQL 实现 #

用json_agg生成JSON数组：

CREATE OR REPLACE FUNCTION sync_t1_json_from_t2()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE T1
    SET COL2 = (
        SELECT json_agg(COL21)
        FROM T2
        WHERE T2.COL11 = T1.COL1
    )
    WHERE T1.COL1 = NEW.COL11;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t2_update_sync_t1_json
AFTER UPDATE ON T2
FOR EACH ROW
EXECUTE FUNCTION sync_t1_json_from_t2();

额外说明 #

• 首次同步可以手动执行批量更新，比如MySQL：

  UPDATE T1
  SET COL2 = (
      SELECT GROUP_CONCAT(COL21 SEPARATOR ',')
      FROM T2
      WHERE T2.COL11 = T1.COL1
  );
  

• 确保T1.COL1和T2.COL11的关联关系准确，避免更新错误行。

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