MySQL表字段比较及通过Trigger更新关联列的实现求助

根据你的需求，我们需要实现两个核心功能：一次性初始化更新所有行的关联字段，以及通过触发器在数据变更时自动维护这些字段。以下是分步实现方案：

1. 先定义表结构与测试数据 #

假设你的表名为 dosen_relations，先创建表并插入测试数据：

CREATE TABLE dosen_relations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_dosen INT,
    id_dosen2 INT,
    tetangga1 INT,
    tetangga2 INT
);

-- 插入你的测试数据
INSERT INTO dosen_relations (id, id_dosen, id_dosen2, tetangga1, tetangga2) VALUES
(1, 1, 2, null, null),
(2, 2, 3, null, null),
(3, 1, 3, null, null),
(4, 4, 5, null, null),
(5, 4, 6, null, null),
(6, 6, null, null, null);

2. 一次性初始化更新所有行 #

我们可以通过 JOIN 和窗口函数，找到每行的关联行并填充 tetangga1 和 tetangga2：

-- 先清空旧的关联数据
UPDATE dosen_relations SET tetangga1 = NULL, tetangga2 = NULL;

-- 计算并更新关联字段
UPDATE dosen_relations t
JOIN (
    SELECT
        t.id AS target_id,
        x.id AS related_id,
        -- 给每个目标行的关联行排序，取前两个
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY x.id) AS rn
    FROM dosen_relations t
    JOIN dosen_relations x ON t.id != x.id
        AND (
            -- 匹配规则：共享任意一个id_dosen或id_dosen2
            x.id_dosen = t.id_dosen
            OR x.id_dosen2 = t.id_dosen
            OR x.id_dosen = t.id_dosen2
            OR x.id_dosen2 = t.id_dosen2
        )
) rel ON t.id = rel.target_id
SET
    tetangga1 = CASE WHEN rel.rn = 1 THEN rel.related_id ELSE tetangga1 END,
    tetangga2 = CASE WHEN rel.rn = 2 THEN rel.related_id ELSE tetangga2 END;

执行后，表数据会完全符合你给出的预期结果。

3. 创建触发器自动维护关联字段 #

为了在插入或修改数据时自动更新关联字段，我们可以创建存储过程 + 触发器的组合：

步骤3.1：创建处理更新的存储过程 #

这个存储过程只会更新受影响的行（而非全表），提升效率：

DELIMITER //

CREATE PROCEDURE update_related_tetangga(IN modified_id INT)
BEGIN
    DECLARE dosen1 INT;
    DECLARE dosen2 INT;
    
    -- 获取被修改行的两个id值
    SELECT id_dosen, id_dosen2 INTO dosen1, dosen2 FROM dosen_relations WHERE id = modified_id;
    
    -- 清空所有关联行的旧关联数据
    UPDATE dosen_relations 
    SET tetangga1 = NULL, tetangga2 = NULL
    WHERE id = modified_id
       OR id_dosen IN (dosen1, dosen2)
       OR id_dosen2 IN (dosen1, dosen2);
    
    -- 重新计算关联行的tetangga字段
    UPDATE dosen_relations t
    JOIN (
        SELECT
            t.id AS target_id,
            x.id AS related_id,
            ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY x.id) AS rn
        FROM dosen_relations t
        JOIN dosen_relations x ON t.id != x.id
            AND (
                x.id_dosen = t.id_dosen
                OR x.id_dosen2 = t.id_dosen
                OR x.id_dosen = t.id_dosen2
                OR x.id_dosen2 = t.id_dosen2
            )
        -- 只处理受影响的行
        WHERE t.id = modified_id
           OR t.id_dosen IN (dosen1, dosen2)
           OR t.id_dosen2 IN (dosen1, dosen2)
    ) rel ON t.id = rel.target_id
    SET
        tetangga1 = CASE WHEN rel.rn = 1 THEN rel.related_id ELSE tetangga1 END,
        tetangga2 = CASE WHEN rel.rn = 2 THEN rel.related_id ELSE tetangga2 END;
END //

DELIMITER ;

步骤3.2：创建触发器 #

触发器会在插入或更新数据时调用上述存储过程：

DELIMITER //

CREATE TRIGGER after_dosen_relations_change
AFTER INSERT OR UPDATE ON dosen_relations
FOR EACH ROW
BEGIN
    CALL update_related_tetangga(NEW.id);
END //

DELIMITER ;

关键说明 #

• 匹配规则：我们认为只要两行共享任意一个 id_dosen 或 id_dosen2 的值，就互为关联行
• 排序逻辑：关联行按 id 升序排列，取前两个分别放入 tetangga1 和 tetangga2，如果需要调整排序规则，修改 ORDER BY x.id 即可
• 效率优化：存储过程只会更新受影响的行，而非全表，适合数据量较大的场景

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