MySQL 订单表怎么设计：主表、明细、商品快照与事务写入

商品今天改了名称和价格，昨天的订单不能跟着变。用户买了两件键盘，也不能在订单表里保存一个“商品ID逗号分隔列表”，然后再靠字符串拆解还原金额。

订单表设计先区分两类事实：商品目录描述现在卖什么、卖多少钱；订单明细记录当时买了什么、成交单价与数量是什么。两者保留关系，但不能在查询历史订单时拿当前目录价格重新结算。

下面从空库建立客户、商品、订单和订单明细四张表，创建一笔订单，再修改目录验证历史快照。SQL 适用于 MySQL 8.0.16 及以上，实际在 MySQL 8.4.11 执行。它是完整的表设计练习，不是包含库存、支付、优惠和退款的完整商城接口。

四张表各自保存什么

示例固定编号只是为了让结果容易核对。实际编号由应用或数据库按自己的生成规则提供；order_no 是业务订单号，另设唯一约束，不与内部主键的职责混用。

CREATE DATABASE order_model_practice CHARACTER SET utf8mb4;
USE order_model_practice;

CREATE TABLE customer_account (
    id BIGINT NOT NULL,
    display_name VARCHAR(60) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE product_catalog (
    id BIGINT NOT NULL,
    sku_code VARCHAR(40) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    current_price DECIMAL(10,2) NOT NULL,
    active TINYINT NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uk_product_sku (sku_code),
    CONSTRAINT chk_product_price CHECK (current_price >= 0),
    CONSTRAINT chk_product_active CHECK (active IN (0,1))
) ENGINE=InnoDB;

CREATE TABLE sales_order (
    id BIGINT NOT NULL,
    order_no VARCHAR(40) NOT NULL,
    customer_id BIGINT NOT NULL,
    status VARCHAR(12) NOT NULL,
    total_amount DECIMAL(18,2) NOT NULL,
    created_at DATETIME(6) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_order_customer_time (customer_id, created_at, id),
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
        REFERENCES customer_account(id) ON DELETE RESTRICT,
    CONSTRAINT chk_order_status CHECK (status IN ('created','paid','cancelled')),
    CONSTRAINT chk_order_total CHECK (total_amount >= 0)
) ENGINE=InnoDB;

CREATE TABLE order_item (
    id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name_snapshot VARCHAR(100) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL,
    line_amount DECIMAL(16,2)
        GENERATED ALWAYS AS (unit_price * quantity) STORED,
    PRIMARY KEY (id),
    UNIQUE KEY uk_item_order_product (order_id, product_id),
    KEY idx_item_product (product_id),
    CONSTRAINT fk_item_order FOREIGN KEY (order_id)
        REFERENCES sales_order(id) ON DELETE RESTRICT,
    CONSTRAINT fk_item_product FOREIGN KEY (product_id)
        REFERENCES product_catalog(id) ON DELETE RESTRICT,
    CONSTRAINT chk_item_price CHECK (unit_price >= 0),
    CONSTRAINT chk_item_quantity CHECK (quantity BETWEEN 1 AND 1000)
) ENGINE=InnoDB;

INSERT INTO customer_account VALUES (1, '演示买家');
INSERT INTO product_catalog VALUES
(501, 'KEYBOARD-A', '机械键盘', 199.90, 1),
(502, 'MOUSE-B', '办公鼠标', 49.90, 1);

客户与订单是一对多，订单与明细也是一对多。一张订单可以包含多种商品，因此商品不能直接塞成订单表里的固定 product1、product2 列。明细的数量字段表达同款买几件，不需要同一款商品复制两条完全相同的行。

这份模型约定同一订单同一商品只有一行，所以 (order_id, product_id) 唯一。如果业务允许同一 SKU 因不同促销或定制参数拆成多行，就必须调整明细身份与唯一规则，不能直接拿当前约束套进去。商品表只做停用，不删除仍被订单引用的目录行；明细的快照则保留当时名称与单价。

line_amount 是行内计算，数据库根据单价和数量生成，写入时不要再传这个字段。它能防止“单价乘数量与行小计不一致”，但不会跨表替 sales_order 自动汇总。订单总额需要在创建或允许修改明细的业务事务中维护，CHECK 约束也不能比较另一张表的 SUM。

在同一个事务里写入主表和两条明细

在同一个连接先创建主表，再从目录复制这次练习的有效商品信息：

START TRANSACTION;
INSERT INTO sales_order
    (id, order_no, customer_id, status, total_amount, created_at)
VALUES (100, 'ORD-DEMO-100', 1, 'created', 0, '2026-10-24 10:00:00');

INSERT INTO order_item
    (id, order_id, product_id, product_name_snapshot, unit_price, quantity)
SELECT 10001, 100, id, product_name, current_price, 2
FROM product_catalog WHERE id = 501 AND active = 1
UNION ALL
SELECT 10002, 100, id, product_name, current_price, 1
FROM product_catalog WHERE id = 502 AND active = 1;
SELECT ROW_COUNT() AS inserted_items;

inserted_items 应为 2。这一步不是摆设：INSERT...SELECT 没查到商品时可以插入零行而不报错。这里的两个固定 SKU 都应有效；若不是两行，执行 ROLLBACK 停止本次练习，不要继续提交一张少明细的订单。应用接收可变商品清单时，也必须核对请求商品集合、有效商品集合与最终写入结果，而不是只看 SQL 有没有抛异常。

确认是两行后继续：

UPDATE sales_order
SET total_amount = (
    SELECT SUM(line_amount) FROM order_item WHERE order_id = 100
)
WHERE id = 100;
COMMIT;

SELECT o.order_no, o.total_amount,
       i.product_id, i.product_name_snapshot, i.unit_price, i.quantity, i.line_amount
FROM sales_order AS o
JOIN order_item AS i ON i.order_id = o.id
WHERE o.id = 100
ORDER BY i.id;

键盘行是 199.90 × 2 = 399.80，鼠标行是 49.90 × 1 = 49.90，主表总额为 449.70。明细中保存的价格来自服务端目录读取结果，不是直接相信请求中传来的“成交价”。真正有优惠、运费或税费时，应先定义每项金额的计算与舍入口径，再扩展对应字段；本例没有暗中把它们算进商品小计。

主表 total_amount 是为了保存该笔订单的约定总额，不是可以随时从当前商品价推导的缓存。若允许创建后改单价、数量或优惠，相关入口也要同时维护总额，并处理并发修改。把整个写入流程交给一个业务事务，比让 Controller 分别调用几个各自提交的 DAO 更容易保持这个约束。

改目录以后，历史订单应保持原样

现在故意调整键盘目录，再同时读出当前值与历史快照：

UPDATE product_catalog
SET product_name = '新款机械键盘', current_price = 299.90
WHERE id = 501;

SELECT i.product_id,
       i.product_name_snapshot, i.unit_price AS ordered_price,
       p.product_name AS current_name, p.current_price
FROM order_item AS i
JOIN product_catalog AS p ON p.id = i.product_id
WHERE i.order_id = 100
ORDER BY i.id;

SELECT total_amount FROM sales_order WHERE id = 100;

第一行的历史名称仍是机械键盘，历史单价仍为 199.90；目录已经是新款机械键盘和 299.90。订单总额还是 449.70。展示订单详情时取快照字段，展示“再次购买”的商品卡片时才可能取当前目录信息，这两种用途不能用一个含糊的 price 字段混在一起。

用户的当前昵称和收货地址也可能变化。是否给订单保存姓名、地址快照，要按业务需要另外建模；不能把“有外键”理解成“关联对象以后所有字段都应随着订单变动”。本例没有增加收货地址表，避免用一份未实现的结构假装完整支持物流。

让数据库拒绝两种无效明细

分别执行下面两条，每条都应失败，不影响已经提交的订单：

INSERT INTO order_item
    (id, order_id, product_id, product_name_snapshot, unit_price, quantity)
VALUES (10003, 100, 501, '重复商品', 1.00, 1);

第一条得到唯一约束错误 1062，因为同一订单已经有商品 501。即使换一个新的明细主键，也不能绕过业务唯一规则。

数量检查使用一张新的练习订单，避免同一商品重复的唯一约束干扰这个反例：

START TRANSACTION;
INSERT INTO sales_order
    (id, order_no, customer_id, status, total_amount, created_at)
VALUES (101, 'ORD-DEMO-101', 1, 'created', 0, '2026-10-24 11:00:00');
INSERT INTO order_item
    (id, order_id, product_id, product_name_snapshot, unit_price, quantity)
VALUES (10101, 101, 502, '零数量样本', 49.90, 0);

这次得到 CHECK 错误 3819。随后必须在同一连接执行：

ROLLBACK;
SELECT id, total_amount FROM sales_order ORDER BY id;
SELECT id, line_amount FROM order_item ORDER BY id;

最终仍然只有订单 100，金额 449.70；明细只有 10001、10002，金额分别 399.80、49.90。单条明细失败不等于前面的主表 INSERT 也自动从整个事务中撤销，显式回滚才结束这次失败创建。