如何更新Product表且不影响历史订单详情及历史订单总采购值？

解决这个问题的核心思路是让历史订单关联的产品数据成为静态快照，与Product表的动态更新解耦，以下是几种落地方案：

1. 订单详情表存储完整的静态快照数据 #

订单创建时，不要只在订单详情表中存储product_id，而是把Product表中当时的关键信息（比如产品名称、单价、规格）直接复制到订单详情表，同时预先计算并存储该条订单明细的总采购值。

这样一来，后续无论Product表怎么更新，历史订单详情里的产品信息和总采购值都是独立不变的。

示例表结构：

-- 订单详情表
CREATE TABLE order_details (
    order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT, -- 保留关联方便溯源，但核心数据存快照
    product_name VARCHAR(100), -- 产品名称快照
    unit_price DECIMAL(10,2), -- 下单时的单价快照
    quantity INT,
    total_amount DECIMAL(12,2) -- 预先计算的单条明细总采购值
);

创建订单时的插入逻辑：

INSERT INTO order_details (order_id, product_id, product_name, unit_price, quantity, total_amount)
SELECT 
    @current_order_id,
    p.product_id,
    p.product_name,
    p.unit_price,
    @buy_quantity,
    p.unit_price * @buy_quantity
FROM products p 
WHERE p.product_id = @target_product_id;

2. 给Product表做版本化设计 #

每次更新产品信息时，不修改原有记录，而是新增一条带版本标识的记录，同时标记每条记录的生效时间范围。订单详情表关联对应的产品版本ID，而非基础的product_id。

这种方案既能保留产品的历史版本，又能让历史订单绑定到创建时的产品版本，完全不受后续产品更新的影响。

示例表结构：

-- 版本化产品表
CREATE TABLE products (
    product_id INT,
    version_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    unit_price DECIMAL(10,2),
    effective_start DATETIME DEFAULT CURRENT_TIMESTAMP,
    effective_end DATETIME, -- NULL表示当前生效版本
    UNIQUE KEY (product_id, version_id)
);

-- 订单详情表关联版本ID
CREATE TABLE order_details (
    order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_version_id INT, -- 绑定具体版本
    quantity INT,
    total_amount DECIMAL(12,2) -- 预先计算总采购值
);

更新产品时的逻辑：

-- 先标记旧版本失效
UPDATE products 
SET effective_end = CURRENT_TIMESTAMP 
WHERE product_id = @target_product_id AND effective_end IS NULL;

-- 新增新版本记录
INSERT INTO products (product_id, product_name, unit_price)
VALUES (@target_product_id, @new_product_name, @new_unit_price);

3. 强制历史订单使用预存储的总采购值 #

如果无法调整表结构，至少要确保：历史订单的总采购值是在订单确认时就写入数据库的静态值，绝对不能在查询时动态用products.unit_price * order_details.quantity计算。

比如在订单表中添加total_purchase_value字段，订单确认时计算并写入，后续查询历史订单直接读取这个字段，完全不依赖Product表的当前数据。

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