PAYATHON 2026

如何使用 history_prices 避免历史发票价格变动

支付老李

结论

生成历史 invoice 时，不要读取 item_options.option_price。item_options 保存的是当前目录价格，invoice 需要的则是成交时价格，两者含义不同。

建议采用下面的设计：

  1. 创建只追加、不覆盖的价格版本表；
  2. 每次价格变动时新增一个版本；
  3. 下单时将对应的 price_version_id 固定到订单明细中；
  4. 生成 invoice 时，始终通过版本 ID 读取成交价格，不根据当前价格或订单时间临时推算。

这样不必把价格直接存入 order_items，但需要在 order_items 中保存指向价格版本的外键。根据订单时间查询历史价格区间也能实现同样的功能，只是在边界时间、并发下单、补数据和人工修改时间等场景中更容易出错。

如果 invoice 是需要长期保存的财务凭证，通常还要保存成交时的商品名称、税率、折扣和币种等信息。这些快照可以放在独立的 invoice_lines 表中，不必全部加入现有的 order_items 表。

价格版本表设计

价格字段最好使用 DECIMAL，也可以明确规定使用最小货币单位的整数。现有的 int(11) 只表示显示宽度，并不代表价格精度。下面的示例继续使用整数，并假设 option_price 的单位是分。

CREATE TABLE item_option_price_history (
    price_history_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    option_id        INT NOT NULL,
    option_price     INT NOT NULL,
    valid_from       DATETIME(6) NOT NULL,
    valid_to         DATETIME(6) NULL,
    created_at       DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

    PRIMARY KEY (price_history_id),
    KEY idx_price_history_lookup (
        option_id,
        valid_from,
        valid_to
    ),

    CONSTRAINT fk_price_history_option
        FOREIGN KEY (option_id)
        REFERENCES item_options(option_id)
) ENGINE = InnoDB;

各字段的含义如下：

  • valid_from：价格开始生效的时间；
  • valid_to：价格停止生效的时间；
  • valid_to IS NULL：当前仍然有效的价格；
  • 每次改价都新增一行，不覆盖旧记录。

如果系统支持多币种，还需要增加 currency_code。否则，单凭一个数字无法判断它代表人民币、美元还是其他币种。

初始化现有价格

启用价格历史后，需要为当前有效的商品选项创建初始版本，否则这些选项没有可供关联的价格记录。

INSERT INTO item_option_price_history (
    option_id,
    option_price,
    valid_from,
    valid_to
)
SELECT
    option_id,
    option_price,
    CURRENT_TIMESTAMP(6),
    NULL
FROM item_options;

对正常配置的 MySQL 来说，20 万行不算特殊规模。不过，在生产环境执行时，仍要留意事务日志、锁等待和执行时间，也可以按 option_id 范围分批导入。

这次初始化只能记录启用历史功能时的当前价格，无法恢复此前发生但没有留下记录的价格变化。如果旧 invoice 没有保存成交价，系统中也没有其他日志或账单数据，就无法可靠还原当时的价格。

使用 trigger 自动记录改价

下面的 trigger 只在价格确实发生变化时执行，并通过 MySQL 的 null-safe equality 运算符 <=> 处理 NULL。

DELIMITER //

CREATE TRIGGER trg_item_options_price_update
BEFORE UPDATE ON item_options
FOR EACH ROW
BEGIN
    DECLARE v_changed_at DATETIME(6);

    IF NOT (OLD.option_price <=> NEW.option_price) THEN
        SET v_changed_at = CURRENT_TIMESTAMP(6);

        UPDATE item_option_price_history
        SET valid_to = v_changed_at
        WHERE option_id = OLD.option_id
          AND valid_to IS NULL;

        INSERT INTO item_option_price_history (
            option_id,
            option_price,
            valid_from,
            valid_to
        )
        VALUES (
            NEW.option_id,
            NEW.option_price,
            v_changed_at,
            NULL
        );
    END IF;
END//

DELIMITER ;

如果系统允许直接新增 item_options，还可以使用初始化 trigger 为新选项创建价格版本：

DELIMITER //

CREATE TRIGGER trg_item_options_price_insert
AFTER INSERT ON item_options
FOR EACH ROW
BEGIN
    INSERT INTO item_option_price_history (
        option_id,
        option_price,
        valid_from,
        valid_to
    )
    VALUES (
        NEW.option_id,
        NEW.option_price,
        CURRENT_TIMESTAMP(6),
        NULL
    );
END//

DELIMITER ;

创建 trigger 前，应先完成现有数据的初始化。否则，第一次改价时可能找不到需要关闭的当前版本。

还要限制应用账号直接修改历史表，防止旧价格被覆盖或删除。

推荐：订单直接关联价格版本

先给订单明细增加价格版本外键：

ALTER TABLE order_items
    ADD COLUMN price_history_id BIGINT UNSIGNED NULL,
    ADD KEY idx_order_items_price_history (price_history_id),
    ADD CONSTRAINT fk_order_items_price_history
        FOREIGN KEY (price_history_id)
        REFERENCES item_option_price_history(price_history_id);

创建订单时，从当前有效的价格版本中取得 price_history_id：

INSERT INTO order_items (
    order_id,
    item_id,
    option_id,
    price_history_id
)
SELECT
    ?,
    io.item_id,
    io.option_id,
    ph.price_history_id
FROM item_options AS io
JOIN item_option_price_history AS ph
  ON ph.option_id = io.option_id
 AND ph.valid_to IS NULL
WHERE io.option_id = ?;

查询 invoice 时，直接关联订单保存的版本：

SELECT
    oi.order_items_id,
    oi.order_id,
    oi.item_id,
    oi.option_id,
    io.option_name,
    ph.option_price
FROM order_items AS oi
JOIN item_option_price_history AS ph
  ON ph.price_history_id = oi.price_history_id
JOIN item_options AS io
  ON io.option_id = oi.option_id
WHERE oi.order_id = ?;

后续即使价格发生变化，旧订单中的 price_history_id 仍指向原来的价格版本，因此历史 invoice 不会随之改变。

创建订单、读取当前价格版本和写入订单明细应放在同一个数据库事务中。业务规则也要明确：改价和下单同时发生时，以哪个操作先提交为准，以及哪个价格应当生效。

如果只能根据订单时间查询

假设 orders 表中有可靠且不可修改的 ordered_at，可以按照价格的有效时间区间查询：

SELECT
    oi.order_items_id,
    ph.option_price
FROM order_items AS oi
JOIN orders AS o
  ON o.order_id = oi.order_id
JOIN item_option_price_history AS ph
  ON ph.option_id = oi.option_id
 AND ph.valid_from <= o.ordered_at
 AND (
        ph.valid_to > o.ordered_at
        OR ph.valid_to IS NULL
     )
WHERE oi.order_id = ?;

这里采用左闭右开区间：

valid_from <= ordered_at < valid_to

使用这种方案时，必须满足以下条件：

  • 同一个 option_id 的价格有效期不能重叠；
  • 订单时间和改价时间采用相同的时区与时间精度；
  • ordered_at 创建后不能随意修改；
  • 初始化价格历史之前的订单无法据此准确还原价格；
  • 下单时间必须代表价格确定的时刻，而不只是订单记录写入数据库的时刻。

因此，时间区间查询适合审计和追溯，但稳定性不如在订单中保存 price_history_id。

trigger 还是应用代码

两种方式都可以，承担的职责有所不同。

Trigger 能覆盖后台、脚本和人工 SQL 等改价入口，因此不容易漏记。它的问题是业务逻辑隐藏在数据库中，部署、测试和排错会相对麻烦。

应用代码更容易测试，也方便记录改价原因、操作人和业务批次，但前提是所有改价入口都经过同一个服务。如果仍允许人员直接执行 UPDATE item_options，价格历史就可能被绕过。

实际项目可以采用下面的组合：

  • 由应用服务执行改价业务；
  • 改价和新增价格版本在同一个事务中完成；
  • 通过数据库权限禁止应用直接覆盖历史记录；
  • 使用 trigger 作为最后一道完整性保护，或者彻底禁止绕过价格服务进行更新。

无论选择哪种方式，都不应把价格历史表当成普通的 audit 日志。它属于订单定价模型，而订单中固定的 price_history_id 才能保证 invoice 可以重复生成且结果一致。

商品名称也会变化

如果生成 invoice 时仍连接当前的 item_options.option_name，商品改名后，旧 invoice 中的名称也会改变。可以采用两种方案：

  • 像价格一样为名称建立版本，并让订单关联对应版本；
  • 创建不可变的 invoice_lines，保存开票时的名称、单价、数量、税率、折扣和币种。

对于正式财务凭证，第二种方案通常更可靠。订单仍可引用商品和价格版本，但 invoice 一旦签发，其明细快照便不再受商品目录变化影响。

备注：内容仅供参考。