MySQL触发器实现control表插入后自动更新员工工单数量

原触发器失效原因 #

你写的触发器无法运行核心是两个逻辑错误：

• 内层子查询SELECT employee FROM control GROUP BY employee会返回所有员工ID的多行结果，使用=做等值判断时会触发「子查询返回多行」的SQL报错，无法正常执行。
• 每次插入单条control记录就全表扫描重算所有员工的工单计数，性能极差，数据量上来后会造成长时间锁表，完全没有必要。

这个需求不需要写迭代循环，用MySQL触发器完全可以实现，而且逻辑非常简单。触发器触发时可以通过NEW关键字拿到刚插入的那条记录的字段值，只需要更新对应员工的计数即可，不需要全表重算。

正确实现代码 #

1. 插入工单时的计数更新触发器 #

针对control表新增记录的场景，创建如下AFTER INSERT触发器：

CREATE DEFINER=`root`@`localhost` 
TRIGGER `control_after_insert` 
AFTER INSERT ON `control` 
FOR EACH ROW 
UPDATE `employee` 
SET `numOfTickets` = `numOfTickets` + 1 
WHERE `id` = NEW.employee;

2. 其他场景的配套触发器（保证数据一致性） #

如果业务中存在工单结单删除control记录、工单转派修改control表关联员工ID的操作，需要补充两个触发器，避免numOfTickets计数不准：

• 工单删除/结单时的计数扣减触发器

CREATE DEFINER=`root`@`localhost` 
TRIGGER `control_after_delete` 
AFTER DELETE ON `control` 
FOR EACH ROW 
UPDATE `employee` 
SET `numOfTickets` = `numOfTickets` - 1 
WHERE `id` = OLD.employee;

• 工单转派时的计数调整触发器

DELIMITER //
CREATE DEFINER=`root`@`localhost` 
TRIGGER `control_after_update` 
AFTER UPDATE ON `control` 
FOR EACH ROW 
BEGIN
    IF OLD.employee <> NEW.employee THEN
        -- 转出工单的员工计数减1
        UPDATE `employee` SET `numOfTickets` = `numOfTickets` - 1 WHERE `id` = OLD.employee;
        -- 转入工单的员工计数加1
        UPDATE `employee` SET `numOfTickets` = `numOfTickets` + 1 WHERE `id` = NEW.employee;
    END IF;
END //
DELIMITER ;

3. 历史数据初始化 #

触发器上线前，需要先校准employee表中已有的numOfTickets字段值，执行如下SQL即可：

UPDATE employee e
LEFT JOIN (
    SELECT employee, COUNT(*) AS ticket_cnt
    FROM control
    GROUP BY employee
) c ON e.id = c.employee
SET e.numOfTickets = IFNULL(c.ticket_cnt, 0);

注意：不要在触发器中写全表count重算所有员工值的逻辑，当control表数据量达到十万级以上时，单次插入的耗时会从毫秒级飙升到秒级，还会锁住employee全表阻塞其他业务。上述逐行调整的方案每次只操作单条员工记录，没有额外性能开销。

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