如何创建INSERT触发器自动填充科目总分最大值到max列

• 使用的results表包含student_id、subject_id、total、max四个字段，初始状态下max列为空，已存储各学生不同科目的total得分数据
• 需求：插入total字段数据时，自动按subject_id分组计算对应科目下的total最大值，将值填充到该科目所有记录的max列，实现max列自动更新
• 已编写可正常运行的SELECT查询语句，通过CTE分组查询各科目最大总分后关联原表，代码如下：

WITH CTE AS (SELECT `subject_id`,MAX(`total`) AS MaxTotal
FROM results
GROUP BY `subject_id`
)
SELECT results.*,CTE.MaxTotal
FROM results
JOIN CTE ON results.`subject_id` = CTE.`subject_id`;

• 自行编写BEFORE INSERT触发器时出现大量报错，原触发器代码如下：

CREATE TRIGGER `max_score_before_INSERT` BEFORE INSERT ON `results`
FOR EACH ROW 
SET NEW.max = (WITH CTE AS (SELECT `subject_id`,MAX(`NEW.total`) AS MaxTotal
FROM results
GROUP BY `subject_id`
)
SELECT results.*,CTE.MaxTotal
FROM results
JOIN CTE ON results.`subject_id` = CTE.`subject_id`
);

• 逐行触发的触发器中，SET NEW.max = (...)要求括号内子查询必须返回单个标量值，原写法返回整表多列多结果集，无法直接赋值给单行字段
• CTE中写MAX(NEW.total)逻辑错误：NEW.total仅代表当前待插入单条记录的得分，不是表内全量得分，无法用于计算分组最大值
• 仅靠BEFORE INSERT触发器只能修改当前待插入的单条记录字段值，无法更新同科目下已存在的历史记录的max值，无法满足“对应科目所有记录的max列同步更新”的要求
• MySQL触发器默认不允许在触发器中对触发自身的同一张表进行全表读写操作，未修改配置的情况下直接写同表更新逻辑会触发表锁/递归触发报错

前置配置 #

先执行以下命令放开触发器权限、允许同表递归触发，避免运行报错：

SET GLOBAL log_bin_trust_function_creators = 1;
SET GLOBAL recursive_triggers = ON; -- 仅MySQL8.0及以上版本需要执行

初始化历史数据 #

创建触发器前先把已有历史数据的max字段补全，执行已验证过的CTE逻辑的更新版本即可：

WITH CTE AS (
    SELECT `subject_id`, MAX(`total`) AS MaxTotal
    FROM results
    GROUP BY `subject_id`
)
UPDATE results r
JOIN CTE ON r.`subject_id` = CTE.`subject_id`
SET r.`max` = CTE.MaxTotal;

创建触发器 #

需要两个触发器配合实现逻辑：

1. BEFORE INSERT触发器：给当前待插入的新记录直接计算并填充正确的max值
2. AFTER INSERT触发器：如果新插入的得分是该科目新的最高分，批量更新该科目所有历史记录的max值，避免无效更新

-- 临时修改语句分隔符，避免触发器内分号和客户端默认分隔符冲突
DELIMITER //

-- 插入前给新记录赋值max
CREATE TRIGGER `max_score_before_INSERT` BEFORE INSERT ON `results`
FOR EACH ROW
BEGIN
    SELECT IFNULL(MAX(total), 0) INTO @old_max 
    FROM results 
    WHERE subject_id = NEW.subject_id;
    SET NEW.max = IF(NEW.total > @old_max, NEW.total, @old_max);
END //

-- 插入后同步更新同科目所有历史记录的max
CREATE TRIGGER `max_score_after_INSERT` AFTER INSERT ON `results`
FOR EACH ROW
BEGIN
    -- 仅当新插入记录是新的科目最高分的时候才执行全组更新
    SELECT IFNULL(MAX(total),0) INTO @other_max
    FROM results 
    WHERE subject_id = NEW.subject_id AND student_id != NEW.student_id;
    
    IF NEW.total >= @other_max THEN
        UPDATE results 
        SET max = NEW.total 
        WHERE subject_id = NEW.subject_id;
    END IF;
END //

DELIMITER ;

补充说明：如果业务中存在修改学生得分、删除学生得分记录的场景，需要额外编写AFTER UPDATE、AFTER DELETE触发器，逻辑为重新计算对应subject_id的最大total值，更新该科目下所有记录的max字段即可，否则修改/删除操作后max值不会自动同步。

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