如何避免更新MySQL Consultant表share1影响报表历史数据？

问题根源 #

你当前的查询是实时关联Consultant表的share1字段，所有历史交易记录（来自iap4表）都会动态读取对应顾问最新的share1值，而非交易发生时的数值，所以更新Consultant的share1后，所有关联历史记录的显示值都会同步变化。

可行解决方案 #

方案1：在交易表中冗余历史share1值（最直接）

• 给iap4表新增字段，比如transaction_share1，用于存储交易发生时的share1值
• 插入iap4记录时，同步将当时Consultant表中对应顾问的share1存入该字段
• 修改报表查询语句，直接从iap4读取历史值：

SELECT 
    transaction_share1 AS share1,
    docname,
    qty,
    price1,
    discount,
    tot4,
    tech1 AS tech
FROM iap4;

如果已有历史数据，先批量补全：

UPDATE iap4
JOIN Consultant ON iap4.docname = Consultant.consultant_name
SET iap4.transaction_share1 = Consultant.share1;

方案2：用触发器自动同步历史值

• 给iap4表新增transaction_share1字段
• 创建BEFORE INSERT触发器，插入记录时自动从Consultant获取当时的share1并赋值：

DELIMITER //
CREATE TRIGGER trg_iap4_save_share1
BEFORE INSERT ON iap4
FOR EACH ROW
BEGIN
    SELECT share1 INTO NEW.transaction_share1
    FROM Consultant
    WHERE consultant_name = NEW.docname;
END //
DELIMITER ;

后续插入记录无需手动处理transaction_share1，查询时直接使用该字段即可，更新Consultant的share1不会影响历史交易记录。

方案3：创建历史快照表

• 新建iap4_history表，包含iap4所有字段+share1+交易时间戳
• 每次插入iap4记录时，同步将交易信息+当时的share1插入快照表
• 报表查询从iap4_history读取数据，原iap4可仅保留活跃数据，实现历史数据归档隔离

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