如何实现records表更新时自动同步calculation表的统计数据

你之前编写的UPDATE语句为单次执行的静态逻辑，本身没有自动触发能力，要实现records表增删改操作时自动同步统计结果到calculation表，最常用的方案是在数据库层面创建触发器，也可以根据业务场景选择视图方案。

方案1：触发器同步（适合需要物理表存储统计值、追求查询性能的场景） #

首先需要提前在calculation表中为user_id和symbol字段创建联合唯一主键/联合唯一索引，保证后续重复维度的更新逻辑正常生效。

1. 新增记录触发器 #

插入records表新记录时，自动累加对应维度的统计值，维度不存在则自动创建：

DELIMITER //
CREATE TRIGGER after_records_insert
AFTER INSERT ON records
FOR EACH ROW
BEGIN
    INSERT INTO calculation (user_id, symbol, sumOfAmount)
    VALUES (NEW.user_id, NEW.symbol, NEW.amount)
    ON DUPLICATE KEY UPDATE sumOfAmount = sumOfAmount + NEW.amount;
END //
DELIMITER ;

2. 更新记录触发器 #

修改records表记录时，根据修改内容同步调整统计值：

DELIMITER //
CREATE TRIGGER after_records_update
AFTER UPDATE ON records
FOR EACH ROW
BEGIN
    -- 维度字段（user_id/symbol）修改：旧维度扣减旧值，新维度累加新值
    IF OLD.user_id != NEW.user_id OR OLD.symbol != NEW.symbol THEN
        UPDATE calculation SET sumOfAmount = sumOfAmount - OLD.amount
        WHERE user_id = OLD.user_id AND symbol = OLD.symbol;
        INSERT INTO calculation (user_id, symbol, sumOfAmount)
        VALUES (NEW.user_id, NEW.symbol, NEW.amount)
        ON DUPLICATE KEY UPDATE sumOfAmount = sumOfAmount + NEW.amount;
    -- 仅amount字段修改：直接更新差值
    ELSE
        UPDATE calculation SET sumOfAmount = sumOfAmount + (NEW.amount - OLD.amount)
        WHERE user_id = NEW.user_id AND symbol = NEW.symbol;
    END IF;
END //
DELIMITER ;

3. 删除记录触发器 #

删除records表记录时，自动扣减对应维度的统计值，可选删除统计值为0的冗余行：

DELIMITER //
CREATE TRIGGER after_records_delete
AFTER DELETE ON records
FOR EACH ROW
BEGIN
    UPDATE calculation SET sumOfAmount = sumOfAmount - OLD.amount
    WHERE user_id = OLD.user_id AND symbol = OLD.symbol;
    -- 可选逻辑：统计值归0时删除对应维度行
    DELETE FROM calculation WHERE user_id = OLD.user_id AND symbol = OLD.symbol AND sumOfAmount = 0;
END //
DELIMITER ;

部署时先执行你原本的全量UPDATE语句初始化存量数据，再创建上述触发器即可保证后续增量变更自动同步。

方案2：视图替代物理表（适合数据量不大、不想维护触发器的场景） #

不需要维护calculation物理表和同步逻辑，直接创建统计视图，查询时自动实时计算结果，数据永远和records表一致：

CREATE VIEW calculation_view AS
SELECT user_id, symbol, SUM(amount) AS sumOfAmount FROM records GROUP BY user_id, symbol;

该方案的缺点是数据量较大时查询性能低于预计算的物理表，可根据业务规模选择。

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