如何让MySQL中基于其他表的聚合表自动更新？

问题背景 #

我在MySQL中有一张table1，初始数据如下：

通过Python脚本（使用SQLAlchemy + Pandas）定期向table1追加CSV数据。我需要一张聚合表table2，通过以下SQL生成：

CREATE TABLE table2 AS
SELECT Category, AVG(total_sold) avg_sold, AVG(revenue) avg_revenue, AVG(profit) avg_profit
FROM table1
GROUP BY 1

生成的table2数据如下：

现在需要实现：当table1插入新记录时，table2自动基于table1全量数据重新计算聚合值。

解决方案 #

方案1：使用触发器（全量更新） #

触发器完全适配你的需求，当table1有新数据插入时，自动重新生成table2。注意：这种方式每次插入都会全量计算，适合数据量不大的场景。

步骤1：初始化table2（若未创建）

如果还没创建table2，先执行初始创建语句：

CREATE TABLE table2 AS
SELECT Category, AVG(total_sold) avg_sold, AVG(revenue) avg_revenue, AVG(profit) avg_profit
FROM table1
GROUP BY 1;

步骤2：创建AFTER INSERT触发器

创建触发器，当table1插入数据后，清空table2并重新插入全量聚合结果：

DELIMITER //
CREATE TRIGGER update_table2_after_insert
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
    -- 清空table2现有数据
    TRUNCATE TABLE table2;
    -- 重新插入最新全量聚合数据
    INSERT INTO table2 (Category, avg_sold, avg_revenue, avg_profit)
    SELECT Category, AVG(total_sold), AVG(revenue), AVG(profit)
    FROM table1
    GROUP BY Category;
END //
DELIMITER ;

方案2：使用视图（更高效替代方案） #

如果不需要物理存储聚合数据，视图是更优选择——它始终实时反映table1的最新聚合结果，无需手动或触发更新，还能节省存储空间：

CREATE VIEW view_table2 AS
SELECT Category, AVG(total_sold) avg_sold, AVG(revenue) avg_revenue, AVG(profit) avg_profit
FROM table1
GROUP BY Category;

之后直接查询view_table2就能得到最新的聚合值，无需维护物理表。

注意事项 #

• 触发器方案中，每次插入都会全量计算，若table1数据量很大，会影响插入性能，此时优先选择视图。
• 如果table1还有更新、删除操作，需要额外创建AFTER UPDATE和AFTER DELETE触发器，保证table2数据同步。
• 使用Python脚本插入数据时，触发器会自动触发，无需修改现有脚本逻辑。

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