如何通过MySQL触发器控制evaluation_weight列总和不超过100

直接通过BEFORE触发器+自定义异常抛出的方式就能实现需求，MySQL 5.5及以上版本都支持该语法，不需要额外依赖其他组件。

注意前提 #

绝大多数权重管控都是按业务维度（比如同一考核模板、同一评分项分类）分组校验总和，全表所有数据加总算权重的场景几乎没有实际业务意义，请根据实际表结构调整统计条件。
默认提供全表统计的实现代码，如果需要分组校验，只需要把代码里注释掉的分组查询语句放开，替换成你实际的关联字段即可。

触发器实现代码 #

1. 插入操作校验触发器 #

在数据写入前计算插入后的总权重，达到阈值直接抛错拦截：

DELIMITER //
CREATE TRIGGER trg_eval_criteria_insert_weight_check
BEFORE INSERT ON evaluation_criteria
FOR EACH ROW
BEGIN
    DECLARE total_weight DECIMAL(5,2);
    -- 分组校验场景用下面这句，替换成实际关联字段，比如template_id
    -- SELECT SUM(evaluation_weight) INTO total_weight FROM evaluation_criteria WHERE template_id = NEW.template_id;
    
    -- 全表校验场景用这句
    SELECT IFNULL(SUM(evaluation_weight),0) INTO total_weight FROM evaluation_criteria;
    
    SET total_weight = total_weight + NEW.evaluation_weight;
    IF total_weight >= 100 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '操作拦截：权重总和不能大于等于100';
    END IF;
END //
DELIMITER ;

2. 更新操作校验触发器 #

更新时要排除当前正在修改的行本身的旧值，避免统计错误：

DELIMITER //
CREATE TRIGGER trg_eval_criteria_update_weight_check
BEFORE UPDATE ON evaluation_criteria
FOR EACH ROW
BEGIN
    DECLARE total_weight DECIMAL(5,2);
    -- 分组校验场景用下面这句，和插入触发器的分组条件保持一致
    -- SELECT SUM(evaluation_weight) INTO total_weight FROM evaluation_criteria WHERE template_id = NEW.template_id AND id != NEW.id;
    
    -- 全表校验场景用这句
    SELECT IFNULL(SUM(evaluation_weight),0) INTO total_weight FROM evaluation_criteria WHERE id != NEW.id;
    
    SET total_weight = total_weight + NEW.evaluation_weight;
    IF total_weight >= 100 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '操作拦截：权重总和不能大于等于100';
    END IF;
END //
DELIMITER ;

补充说明 #

• 不要用AFTER触发器做校验，AFTER触发时数据已经写入表中，回滚会产生额外的undo日志开销，BEFORE触发器在写入前校验，性能更好
• 权重字段建议用DECIMAL类型存储，不要用FLOAT/DOUBLE，避免浮点数精度误差导致总和计算偏差
• 如果业务允许权重总和刚好等于100，只需要把判断条件的>=改成>即可
• 重新创建触发器前可以先执行DROP TRIGGER IF EXISTS 触发器名;删除旧触发器，避免创建报错

内容的提问来源于stack exchange，提问作者Dương