如何在MySQL中动态更新统计表实现分组匹配数据自动计数

触发器具实现方案（同步第二张统计表） #

你之前触发器无法生效大概率是没有转义MySQL保留字Group，或是漏了全场景的触发规则，按以下步骤配置即可正常运行：

1. 先给第二张统计表的Group字段设为唯一主键，保证重复分组更新逻辑正常执行

ALTER TABLE second_table ADD PRIMARY KEY (`Group`);

2. 配置新增动物触发规则：在第一张表插入数据后，对应分组计数+1

DELIMITER //
CREATE TRIGGER after_animal_insert
AFTER INSERT ON first_table
FOR EACH ROW
BEGIN
  INSERT INTO second_table (`Group`, `Number in Group`)
  VALUES (NEW.`Group`, 1)
  ON DUPLICATE KEY UPDATE `Number in Group` = `Number in Group` + 1;
END //
DELIMITER ;

3. 配置删除动物触发规则：在第一张表删除数据后，对应分组计数-1

DELIMITER //
CREATE TRIGGER after_animal_delete
AFTER DELETE ON first_table
FOR EACH ROW
BEGIN
  UPDATE second_table 
  SET `Number in Group` = `Number in Group` - 1
  WHERE `Group` = OLD.`Group`;
  -- 可选逻辑：分组计数归0时自动删除对应分组行
  DELETE FROM second_table
  WHERE `Group` = OLD.`Group` AND `Number in Group` = 0;
END //
DELIMITER ;

4. 配置动物改分组触发规则：在第一张表修改动物所属分组时，同时更新新旧两个分组的计数

DELIMITER //
CREATE TRIGGER after_animal_update
AFTER UPDATE ON first_table
FOR EACH ROW
BEGIN
  -- 旧分组计数减1
  UPDATE second_table 
  SET `Number in Group` = `Number in Group` - 1
  WHERE `Group` = OLD.`Group`;
  -- 新分组计数加1，不存在则新增
  INSERT INTO second_table (`Group`, `Number in Group`)
  VALUES (NEW.`Group`, 1)
  ON DUPLICATE KEY UPDATE `Number in Group` = `Number in Group` + 1;
  -- 可选逻辑：旧分组计数归0时自动删除
  DELETE FROM second_table
  WHERE `Group` = OLD.`Group` AND `Number in Group` = 0;
END //
DELIMITER ;

5. 首次配置完成后执行初始化语句，对齐两张表的历史数据

REPLACE INTO second_table (`Group`, `Number in Group`)
SELECT `Group`, COUNT(*)
FROM first_table
GROUP BY `Group`;

轻量替代方案（无需维护第二张表） #

如果数据量不大，不需要冗余存储计数，可以直接用分组查询实时获取统计结果，返回结构和第二张表完全一致，不需要额外维护触发器：

SELECT `Group`, COUNT(*) AS `Number in Group`
FROM first_table
GROUP BY `Group`;

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