网上买卖系统的数据库设计：使用 MySQL 8 从零到专业的 100 个示例
网上商店在很大程度上是一个数据库问题。顾客要登录，商品有尺码和颜色，库存绝不能为负，支付可能重复到达，两位买家也可能在同一秒点击购买最后一件。本文一步步设计这样一个商店的数据库。你会看到网上商店需要的表，包括主键、外键、索引以及每个索引存在的理由。随后会看到每张表的示例数据，以及重要事务在系统运行时内部发生的事情：下单、锁定库存、支付成功与失败、取消、退款，以及两位买家争抢最后一件商品，同时说明缺少事务时会出什么问题。商品列表、商品页面、购物车、结账、订单历史和管理员订单的模型图，展示了这些界面如何读取这些表。文章最后是 100 个编号示例，使用 MySQL 8 语法，包括 DDL、初始数据、查询、索引、事务、锁、视图、分区、报表和专业实践。诚实说明：准备本文的电脑没有安装 MySQL 8，因此这 100 个示例在这里都没有运行。请你在练习数据库中自行运行，并在依赖结果之前核对。
需要准备的东西
一台安装了 MySQL 8.0 或更高版本的电脑（MySQL Community Server，或 mysql:8.0 的 Docker 镜像），一个客户端，例如 mysql 命令行程序或 MySQL Workbench，以及基本的 SQL 知识：SELECT、INSERT、UPDATE 和 JOIN。学习时请使用练习数据库，不要使用真实的商店数据库。请使用 InnoDB 引擎，它是 MySQL 8 的默认引擎，因为只有 InnoDB 支持事务、行锁和外键。
如何使用示例
SQL 示例面向使用 InnoDB 的 MySQL 8。请在练习数据库（例如 shop_practice）中按编号顺序运行。? 表示应用传入的参数。bash 示例在终端中运行。
知识点 1: 从商店必须回答的问题开始
在写任何 CREATE TABLE 之前，先列出商店必须回答的问题：这位顾客拥有什么？我现在还能卖多少？哪些订单等待打包？每张表都是为了回答其中一个问题而存在，并且不与其他表相互矛盾。本节安全地设置 MySQL 8，并介绍本文后续每张表都遵循的规则。
表及其存储的内容
商店需要顾客、地址、分类、商品、商品规格、库存、购物车、购物车项目、订单、订单明细、支付、退款、发货、发货明细、评价、优惠券，如果是多商户市场，还需要卖家和卖家结算。商品描述卖的是什么，规格是带 SKU 和价格的可售单位。订单明细在下单时复制商品名称和价格，因此以后调价也不会改写历史。
键、约束和索引
每张表都有主键。外键防止孤立行，例如没有顾客的订单。UNIQUE 键保护邮箱或 SKU 等值不重复。CHECK 约束拒绝不可能的值，例如负库存。索引加快页面运行的查询，例如某位顾客的订单历史。本文中的每个索引都在旁边写明了理由。
安全地设置 MySQL 8
使用 utf8mb4 创建数据库，使泰文、日文、中文、阿拉伯文和表情符号都能正确存储。创建一个只能读写数据、不能修改结构的应用用户，并开启严格 SQL 模式，使错误的值被拒绝而不是悄悄改写。密码保存在密钥管理系统中，绝不写入文章或源代码。
示意图
示例 1: 检查服务器版本和默认值
确认 MySQL 8、utf8mb4 和隔离级别。
SELECT VERSION() AS server_version,
@@character_set_server AS charset,
@@collation_server AS collation,
@@transaction_isolation AS isolation_level;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 2: 创建商店数据库
整个商店使用 utf8mb4 和同一种排序规则。
CREATE DATABASE IF NOT EXISTS shop
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 3: 创建最小权限的应用用户
应用可以读写数据，但不能修改表结构。
CREATE USER IF NOT EXISTS 'shop_app'@'localhost' IDENTIFIED BY '<strong-password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
-- No DROP, ALTER or GRANT: the application cannot change the schema.
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 4: 开启严格 SQL 模式
拒绝错误数据，而不是悄悄改写。
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ONLY_FULL_GROUP_BY';
SELECT @@SESSION.sql_mode AS sql_mode;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 5: 确认 InnoDB
事务和外键需要 InnoDB 引擎。
SELECT ENGINE, SUPPORT, TRANSACTIONS, XA
FROM information_schema.ENGINES
WHERE ENGINE = 'InnoDB';
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 6: 重置练习数据库
在不影响商店数据的情况下练习。
-- Practice only: this removes the practice copy and nothing else.
DROP DATABASE IF EXISTS shop_practice;
CREATE DATABASE shop_practice CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 2: 顾客和地址
顾客是拥有唯一邮箱和状态的账户。地址属于顾客，但订单会保存自己的收货地址副本，因为顾客之后可能会修改或删除地址。本节介绍这些表、示例数据，以及每位顾客最多只有一个默认地址的规则。
每个账户一行
customers 表以自增的 customer_id 作为主键，并在 email 上设置 UNIQUE 键。MySQL 8 的 utf8mb4_0900_ai_ci 排序规则在比较邮箱时不区分大小写，因此 [email protected] 和 [email protected] 是同一个账户。只保存密码的哈希值，绝不保存密码本身。status 列可以在不删除历史记录的情况下封禁账户。
地址属于顾客
addresses 表通过外键引用 customers，并设置 ON DELETE CASCADE，因此删除顾客也会删除其保存的地址。orders 表不依赖这一点，因为每个订单都会复制收件人、地址行和邮编。生成列可以保证每位顾客只有一个默认地址，因为 UNIQUE 键允许多个 NULL 值。
隐私：不要把卡信息放进数据库
商店不需要保存完整的卡号。只保存支付服务商的令牌和卡号末四位，卡信息交给服务商处理。尽量减少存放个人信息的表，并记住备份、日志和报表中也会包含这些数据，因此需要同等的保护。
示例 7: 创建客户表
邮箱唯一并带有状态的账户。
CREATE TABLE customers (
customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(254) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NULL,
status ENUM('active','blocked') NOT NULL DEFAULT 'active',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (customer_id),
UNIQUE KEY uq_customers_email (email)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 8: 创建地址表
每位客户可以有多个地址，并通过外键关联。
CREATE TABLE addresses (
address_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
label VARCHAR(40) NOT NULL DEFAULT 'Home',
recipient_name VARCHAR(120) NOT NULL,
line1 VARCHAR(200) NOT NULL,
line2 VARCHAR(200) NULL,
city VARCHAR(100) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
country_code CHAR(2) NOT NULL,
is_default TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (address_id),
KEY idx_addresses_customer (customer_id),
CONSTRAINT fk_addresses_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 9: 写入客户示例数据
用于测试的示例账户。
INSERT INTO customers (email, password_hash, full_name, phone) VALUES
('[email protected]', '<bcrypt-hash-1>', 'Mali Sun', '0812345678'),
('[email protected]', '<bcrypt-hash-2>', 'Niran Chai', '0898765432'),
('[email protected]', '<bcrypt-hash-3>', 'Ploy Wong', NULL);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 10: 写入地址示例数据
示例地址，每位客户只有一个默认地址。
INSERT INTO addresses
(customer_id, label, recipient_name, line1, city, postal_code, country_code, is_default) VALUES
(1, 'Home', 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', 1),
(1, 'Office', 'Mali Sun', '99 Rama IX Road', 'Bangkok', '10310', 'TH', 0),
(2, 'Home', 'Niran Chai', '45 Moo 2', 'Chiang Mai', '50000', 'TH', 1);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 11: 查找默认地址
使用复合索引的查询。
SELECT address_id, recipient_name, line1, city
FROM addresses
WHERE customer_id = 1 AND is_default = 1;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 12: 每位客户只有一个默认地址
生成列让唯一规则得以实现。
-- A generated column is NULL unless is_default = 1, and UNIQUE allows many NULLs.
ALTER TABLE addresses
ADD COLUMN default_owner BIGINT UNSIGNED
GENERATED ALWAYS AS (CASE WHEN is_default = 1 THEN customer_id END) STORED,
ADD UNIQUE KEY uq_one_default_address (default_owner);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 13: 统计每位客户的地址数
LEFT JOIN 会保留没有地址的客户。
SELECT c.customer_id, c.full_name, COUNT(a.address_id) AS address_count
FROM customers AS c
LEFT JOIN addresses AS a ON a.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY address_count DESC, c.customer_id;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 14: 外键与停用账户
孤立的行会被拒绝，停用账户是一次 UPDATE。
-- Fails with error 1452 (foreign key constraint) because customer 999 does not exist.
INSERT INTO addresses (customer_id, recipient_name, line1, city, postal_code, country_code)
VALUES (999, 'Nobody', 'Unknown', 'Nowhere', '00000', 'TH');
UPDATE customers SET status = 'blocked' WHERE customer_id = 3;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 3: 商品、分类和规格
商品目录分三层。分类把商品分组，商品是商店卖的东西，规格是带有 SKU、尺码、颜色和价格的具体可售项目。库存和购物车始终指向规格，而不是商品，因为黑色 M 码 T 恤和白色 L 码 T 恤需要分别计数。
商品和规格
products 表保存名称、slug、描述、基础价格和状态（draft、active 或 archived）。product_variants 表保存 SKU、像 M / Black 这样的规格标签、价格和 is_active 标志。在 (product_id, option_label) 上设置 UNIQUE 键，可防止同一商品出现两个相同的规格。草稿商品在状态变为 active 之前不会出现在列表中。
以树形结构组织分类
categories 表通过 parent_id 引用自身，因此服装下面可以包含 T 恤。外键使用 ON DELETE SET NULL，删除父分类后，子分类会变成顶级分类。若要列出某分类下的所有商品，包括子分类，可以使用递归公用表表达式。
列表和搜索查询
列表显示每个商品最低的有效规格价格。使用上一页最后一个 product_id 的 keyset 分页，在大型目录上仍然很快；而 OFFSET 会随着页数增加越来越慢。使用 ngram 分词器的 FULLTEXT 索引，支持泰文、日文和中文这类词与词之间没有空格的文字搜索。
示意图
示例 15: 带父级的分类
引用同一张表的外键构成树形结构。
CREATE TABLE categories (
category_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
parent_id INT UNSIGNED NULL,
name VARCHAR(100) NOT NULL,
slug VARCHAR(120) NOT NULL,
PRIMARY KEY (category_id),
UNIQUE KEY uq_categories_slug (slug),
KEY idx_categories_parent (parent_id),
CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id)
REFERENCES categories (category_id) ON DELETE SET NULL
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 16: 创建商品表
名称、别名、基础价格和状态。
CREATE TABLE products (
product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id INT UNSIGNED NOT NULL,
name VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL,
description TEXT NULL,
base_price DECIMAL(10,2) NOT NULL,
status ENUM('draft','active','archived') NOT NULL DEFAULT 'draft',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (product_id),
UNIQUE KEY uq_products_slug (slug),
KEY idx_products_category_status (category_id, status),
CONSTRAINT fk_products_category FOREIGN KEY (category_id)
REFERENCES categories (category_id) ON DELETE RESTRICT,
CONSTRAINT chk_products_price CHECK (base_price >= 0)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 17: 创建规格表
可售的单位：SKU、规格和价格。
CREATE TABLE product_variants (
variant_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
product_id BIGINT UNSIGNED NOT NULL,
sku VARCHAR(40) NOT NULL,
option_label VARCHAR(80) NOT NULL,
price DECIMAL(10,2) NOT NULL,
weight_grams INT UNSIGNED NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (variant_id),
UNIQUE KEY uq_variants_sku (sku),
UNIQUE KEY uq_variants_option (product_id, option_label),
CONSTRAINT fk_variants_product FOREIGN KEY (product_id)
REFERENCES products (product_id) ON DELETE RESTRICT,
CONSTRAINT chk_variants_price CHECK (price >= 0)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 18: 写入分类和商品示例数据
示例目录数据。
INSERT INTO categories (category_id, parent_id, name, slug) VALUES
(1, NULL, 'Clothing', 'clothing'),
(2, 1, 'T-shirts', 't-shirts'),
(3, NULL, 'Accessories', 'accessories');
INSERT INTO products (product_id, category_id, name, slug, description, base_price, status) VALUES
(1, 2, 'Cotton Crew T-Shirt', 'cotton-crew-tshirt', 'Soft combed cotton, regular fit.', 390.00, 'active'),
(2, 3, 'Canvas Tote Bag', 'canvas-tote-bag', 'Heavy canvas with an inner pocket.', 590.00, 'active'),
(3, 2, 'Linen Henley Shirt', 'linen-henley', 'Summer linen, long sleeve.', 890.00, 'draft');
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 19: 写入规格示例数据
五个规格，其中一个已停用。
INSERT INTO product_variants
(variant_id, product_id, sku, option_label, price, weight_grams, is_active) VALUES
(1, 1, 'TS-CREW-M-BLK', 'M / Black', 390.00, 180, 1),
(2, 1, 'TS-CREW-L-BLK', 'L / Black', 390.00, 190, 1),
(3, 1, 'TS-CREW-M-WHT', 'M / White', 390.00, 180, 1),
(4, 2, 'TOTE-NAT', 'Natural', 590.00, 420, 1),
(5, 3, 'LIN-HEN-M-BLU', 'M / Blue', 890.00, 200, 0);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 20: 使用 keyset 分页的商品列表
无需 OFFSET 即可快速查询下一页。
SELECT p.product_id, p.name, MIN(v.price) AS from_price
FROM products AS p
JOIN product_variants AS v
ON v.product_id = p.product_id AND v.is_active = 1
WHERE p.status = 'active'
AND p.category_id = 2
AND p.product_id > ? -- last product_id from the previous page
GROUP BY p.product_id, p.name
ORDER BY p.product_id
LIMIT 20;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 21: 商品页面的规格
只取某个商品的启用规格。
SELECT v.variant_id, v.sku, v.option_label, v.price, v.weight_grams
FROM product_variants AS v
WHERE v.product_id = 1
AND v.is_active = 1
ORDER BY v.option_label;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 22: 全文搜索
使用 ngram 分词器的 FULLTEXT 索引。
ALTER TABLE products
ADD FULLTEXT KEY ft_products_name_desc (name, description) WITH PARSER ngram;
SELECT product_id, name
FROM products
WHERE status = 'active'
AND MATCH (name, description) AGAINST ('cotton' IN NATURAL LANGUAGE MODE);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 4: 库存和库存流水
库存是出错就会直接损失金钱的部分。这一设计为每个规格保存三个数字：on_hand 是仓库里实际存在的数量；reserved 是承诺给尚未付款或尚未发货订单的数量；available 是 on_hand 减去 reserved 的可售数量。流水表记录每一次变化，因此任何时候都能与历史核对计数。
带 CHECK 规则的计数器
inventory 表每个规格一行。CHECK 约束要求 on_hand 大于或等于零，reserved 介于零和 on_hand 之间。自 MySQL 8.0.16 起，服务器会强制执行 CHECK 约束，因此会让库存变为负数的 UPDATE 会以错误 3819 失败，而不是悄悄写入错误的值。
流水
inventory_movements 记录每次变化，包括 on_hand 和 reserved 带符号的增减量、原因（如 reserve、release、sale、cancel、return、restock 或 adjustment），以及所属订单。流水只能追加。错误通过新增一行来更正，而不是修改旧行。这为离开商店的每件商品保留了审计记录。
用流水核对计数器
每晚的任务会把每个规格的流水加总，并与计数器比较。不一致意味着存在程序错误或手工修改，应当触发告警。初始数据以期初余额的形式写入流水，因此核对从零开始。
示例 23: 创建库存表
现有数量和已预留数量，带 CHECK 规则。
CREATE TABLE inventory (
variant_id BIGINT UNSIGNED NOT NULL,
on_hand INT NOT NULL DEFAULT 0,
reserved INT NOT NULL DEFAULT 0,
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (variant_id),
CONSTRAINT fk_inventory_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT,
CONSTRAINT chk_inventory_on_hand CHECK (on_hand >= 0),
CONSTRAINT chk_inventory_reserved CHECK (reserved >= 0 AND reserved <= on_hand)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 24: 创建库存流水表
每次库存变动都留下一行。
CREATE TABLE inventory_movements (
movement_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
variant_id BIGINT UNSIGNED NOT NULL,
delta_on_hand INT NOT NULL DEFAULT 0,
delta_reserved INT NOT NULL DEFAULT 0,
reason ENUM('adjustment','restock','reserve','release','sale','cancel','return') NOT NULL,
order_id BIGINT UNSIGNED NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (movement_id),
KEY idx_movements_variant_time (variant_id, created_at),
KEY idx_movements_order (order_id),
CONSTRAINT fk_movements_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 25: 写入库存和期初余额示例数据
库存数量与对应的流水行。
INSERT INTO inventory (variant_id, on_hand, reserved) VALUES
(1, 20, 0),
(2, 8, 0),
(3, 0, 0), -- sold out
(4, 50, 0);
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason) VALUES
(1, 20, 0, 'adjustment'),
(2, 8, 0, 'adjustment'),
(4, 50, 0, 'adjustment');
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 26: 可售数量
现有数量减去已预留数量。
SELECT variant_id, on_hand - reserved AS available
FROM inventory
WHERE on_hand - reserved > 0
ORDER BY variant_id;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 27: 低库存报表
需要补货的规格。
SELECT v.sku, i.on_hand - i.reserved AS available
FROM inventory AS i
JOIN product_variants AS v ON v.variant_id = i.variant_id
WHERE i.on_hand - i.reserved BETWEEN 0 AND 5
ORDER BY available, v.sku;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 28: 补货并写入流水
在一个事务中更新库存并记录。
START TRANSACTION;
UPDATE inventory
SET on_hand = on_hand + 30
WHERE variant_id = 3;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason)
VALUES (3, 30, 0, 'restock');
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 29: 拒绝负库存
CHECK 约束阻止不可能的值。
-- Fails with error 3819 (check constraint violated) on MySQL 8.0.16 and later.
UPDATE inventory SET on_hand = -1 WHERE variant_id = 2;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 30: 对账库存与流水
找出与历史记录不一致的计数。
SELECT i.variant_id, i.on_hand, COALESCE(SUM(m.delta_on_hand), 0) AS ledger_on_hand
FROM inventory AS i
LEFT JOIN inventory_movements AS m ON m.variant_id = i.variant_id
GROUP BY i.variant_id, i.on_hand
HAVING i.on_hand <> COALESCE(SUM(m.delta_on_hand), 0);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 5: 购物车
购物车保存买家打算购买的商品。访客的购物车由会话令牌标识，顾客的购物车由 customer_id 标识。购物车不是订单：不会预留库存，购物车中显示的价格会在结账时重新读取。本节介绍购物车表、安全地添加商品，以及登录后合并访客购物车。
访客购物车和顾客购物车
carts 表包含可选的 customer_id 和可选的 session_token。访客登录后，会设置购物车的 customer_id，并把访客购物车中的商品合并到顾客已打开的购物车中。cart_items 表在 (cart_id, variant_id) 上有 UNIQUE 键，因此同一个规格在每个购物车中只出现一次，重复添加时更新数量，而不是新增一行。
一条语句加入购物车
INSERT ... AS new ON DUPLICATE KEY UPDATE 可以在一条原子语句中增加数量。同一个按钮点两次不会产生两行，数量通过 CHECK 约束和更新时的 LEAST 函数限制在 99 以内。
购物车不是订单
把商品加入购物车不会预留库存，因为每天都有大量购物车被放弃。只有顾客提交订单时才开始预留。因此购物车页面必须再次检查可售情况，并在商品售罄时给出清楚的提示。
示意图
示例 31: 创建购物车表
访客购物车使用会话令牌，客户使用 ID。
CREATE TABLE carts (
cart_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NULL,
session_token CHAR(36) NULL,
status ENUM('open','converted','abandoned') NOT NULL DEFAULT 'open',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (cart_id),
UNIQUE KEY uq_carts_session (session_token),
KEY idx_carts_customer_status (customer_id, status),
KEY idx_carts_status_updated (status, updated_at),
CONSTRAINT fk_carts_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 32: 创建购物车明细表
每个购物车中每个规格一行。
CREATE TABLE cart_items (
cart_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
cart_id BIGINT UNSIGNED NOT NULL,
variant_id BIGINT UNSIGNED NOT NULL,
quantity INT NOT NULL,
added_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (cart_item_id),
UNIQUE KEY uq_cart_variant (cart_id, variant_id),
KEY idx_cart_items_variant (variant_id),
CONSTRAINT fk_cart_items_cart FOREIGN KEY (cart_id)
REFERENCES carts (cart_id) ON DELETE CASCADE,
CONSTRAINT fk_cart_items_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT,
CONSTRAINT chk_cart_quantity CHECK (quantity BETWEEN 1 AND 99)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 33: 写入购物车示例数据
一个客户购物车和一个访客购物车。
INSERT INTO carts (cart_id, customer_id, session_token, status) VALUES
(1, 1, NULL, 'open'),
(2, NULL, 'b0a1c2d3-0000-4000-8000-000000000002', 'open');
INSERT INTO cart_items (cart_id, variant_id, quantity) VALUES
(1, 1, 1),
(1, 4, 2),
(2, 2, 1);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 34: 加入购物车
一条语句完成插入或增加数量。
INSERT INTO cart_items (cart_id, variant_id, quantity)
VALUES (1, 1, 2) AS new
ON DUPLICATE KEY UPDATE
quantity = LEAST(cart_items.quantity + new.quantity, 99);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 35: 登录后合并访客购物车
在一个事务中移动商品。
START TRANSACTION;
INSERT INTO cart_items (cart_id, variant_id, quantity)
SELECT 1, variant_id, quantity
FROM cart_items
WHERE cart_id = 2
ON DUPLICATE KEY UPDATE quantity = LEAST(quantity + VALUES(quantity), 99);
UPDATE carts SET status = 'abandoned' WHERE cart_id = 2;
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 36: 显示实时价格和库存的购物车
显示购物车时读取价格和可售数量。
SELECT ci.variant_id, v.sku, p.name, v.price, ci.quantity,
v.price * ci.quantity AS line_total,
COALESCE(i.on_hand - i.reserved, 0) AS available
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
LEFT JOIN inventory AS i ON i.variant_id = ci.variant_id
WHERE ci.cart_id = 1;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 37: 将长期闲置的购物车标记为放弃
使用状态索引的定期任务。
UPDATE carts
SET status = 'abandoned'
WHERE status = 'open'
AND updated_at < NOW(3) - INTERVAL 7 DAY;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 38: 检查购物车查询计划
EXPLAIN 显示是否使用了索引。
EXPLAIN SELECT cart_id
FROM carts
WHERE status = 'open'
AND updated_at < NOW(3) - INTERVAL 7 DAY;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 6: 订单和订单明细
订单是一次购买的记录。它保存总额、状态和收货地址的副本。每个订单明细复制 SKU、商品名称和单价，因此即使目录以后发生变化，订单的含义也不会改变。本节还介绍总额必须始终相加一致的规则，以及如何快速翻页查看顾客的历史订单。
保存下单时的快照
order_items 保存 sku_snapshot、product_name_snapshot 和 unit_price，它们在创建订单时从 product_variants 复制。line_total 列是生成列，即 unit_price 乘以 quantity，因此永远不会与其输入不一致。variant_id 外键使用 ON DELETE RESTRICT，因此售出过的规格不能删除，应改为停用。
总额必须相加一致
orders 表上的 CHECK 约束要求 grand_total = subtotal - discount_total + shipping_fee。任何修改这些数字之一的代码路径，都必须在同一事务中修改其他数字，否则语句会失败。订单提交前，总额会根据订单明细重新计算，因此表头总是与明细一致。
订单历史翻页
复合索引 (customer_id, created_at) 支持查询某位顾客最新的订单。使用 (created_at, order_id) 的 keyset 分页可以直接取得下一页，而无需扫描之前的行。使用 EXPLAIN 确认使用了索引，并让排序顺序与索引一致。
示意图
示例 39: 创建订单表
购买的主表，包含总额和地址快照。
CREATE TABLE orders (
order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_number VARCHAR(20) NOT NULL,
customer_id BIGINT UNSIGNED NOT NULL,
status ENUM('pending_payment','paid','packed','shipped','delivered','cancelled','refunded')
NOT NULL DEFAULT 'pending_payment',
currency CHAR(3) NOT NULL DEFAULT 'THB',
subtotal DECIMAL(12,2) NOT NULL,
discount_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
shipping_fee DECIMAL(12,2) NOT NULL DEFAULT 0.00,
grand_total DECIMAL(12,2) NOT NULL,
coupon_id INT UNSIGNED NULL,
ship_recipient VARCHAR(120) NOT NULL,
ship_line1 VARCHAR(200) NOT NULL,
ship_city VARCHAR(100) NOT NULL,
ship_postal_code VARCHAR(20) NOT NULL,
ship_country_code CHAR(2) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (order_id),
UNIQUE KEY uq_orders_number (order_number),
KEY idx_orders_customer_created (customer_id, created_at),
KEY idx_orders_status_created (status, created_at),
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE RESTRICT,
CONSTRAINT chk_orders_total CHECK (grand_total = subtotal - discount_total + shipping_fee)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 40: 创建订单明细表
保存购买时的价格，行合计由系统生成。
CREATE TABLE order_items (
order_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
variant_id BIGINT UNSIGNED NOT NULL,
sku_snapshot VARCHAR(40) NOT NULL,
product_name_snapshot VARCHAR(200) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
quantity INT UNSIGNED NOT NULL,
line_total DECIMAL(12,2) GENERATED ALWAYS AS (unit_price * quantity) STORED,
PRIMARY KEY (order_item_id),
UNIQUE KEY uq_order_variant (order_id, variant_id),
KEY idx_order_items_variant (variant_id),
CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE RESTRICT,
CONSTRAINT fk_order_items_variant FOREIGN KEY (variant_id)
REFERENCES product_variants (variant_id) ON DELETE RESTRICT,
CONSTRAINT chk_order_items_qty CHECK (quantity > 0)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 41: 把购物车复制为订单
结算时保存价格。
-- @order_id is the id of the order created just before this statement.
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT @order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = 1;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 42: 写入三张示例订单
处于三种不同状态的订单。
INSERT INTO orders
(order_id, order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code, created_at) VALUES
(1, 'SH-20261001-0001', 1, 'delivered', 780.00, 0.00, 50.00, 830.00, 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', '2026-10-01 10:12:00'),
(2, 'SH-20261020-0002', 2, 'paid', 590.00, 0.00, 50.00, 640.00, 'Niran Chai', '45 Moo 2', 'Chiang Mai', '50000', 'TH', '2026-10-20 18:40:00'),
(3, 'SH-20261029-0003', 1, 'pending_payment', 390.00, 39.00, 50.00, 401.00, 'Mali Sun', '12/3 Sukhumvit Road', 'Bangkok', '10110', 'TH', '2026-10-29 09:05:00');
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 43: 写入订单明细示例数据
这三张示例订单的明细。
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity) VALUES
(1, 1, 'TS-CREW-M-BLK', 'Cotton Crew T-Shirt', 390.00, 2),
(2, 4, 'TOTE-NAT', 'Canvas Tote Bag', 590.00, 1),
(3, 2, 'TS-CREW-L-BLK', 'Cotton Crew T-Shirt', 390.00, 1);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 44: 使用 keyset 分页的订单历史
某位客户的订单，最新的在前。
SELECT order_id, order_number, status, grand_total, created_at
FROM orders
WHERE customer_id = 1
AND (created_at, order_id) < (?, ?) -- last row of the previous page
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 45: 订单详情
一条查询同时获取主表和明细。
SELECT o.order_number, o.status, oi.sku_snapshot, oi.product_name_snapshot,
oi.unit_price, oi.quantity, oi.line_total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.order_id = 1
ORDER BY oi.order_item_id;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 46: 管理员队列：已付款订单
仓库接下来需要打包的订单。
SELECT order_id, order_number, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at
LIMIT 50;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 7: 支付和退款
支付是对订单收费的一次尝试。一个订单可以有多次尝试，例如第一次因卡被拒绝而失败，第二次成功。支付服务商还会发送 webhook，它可能重复到达，或者乱序到达。payments 表和 refunds 表的设计确保重试和重复消息不会让顾客被扣两次款，也不会把同一笔销售记录两次。
幂等键
每次支付尝试都有一个唯一的 idempotency_key。如果应用因为网络超时而重试请求，第二次 INSERT 会找到已有的行，而不会创建新的收费。provider_ref 列在每个服务商内唯一，它把该行与服务商自己的交易 ID 关联起来。
支付行和 webhook
webhook 通知支付已成功或已失败。处理程序通过 provider 和 provider_ref 找到支付记录，并且只有在状态仍为 pending 时才修改它。如果状态已经是最终状态，更新会改变零行，处理程序不再做任何事情。这就是重复的 webhook 无害的原因。
退款规则
退款引用某个支付，并拥有自己的状态。可退金额是支付金额减去所有未失败的退款。退款行在调用服务商之前写入，之后再更新状态。当数据库事务持有行锁时，绝不调用服务商，否则缓慢的网络调用会阻塞其他买家。
示例 47: 创建支付表
每次扣款尝试一行，带幂等键。
CREATE TABLE payments (
payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
provider VARCHAR(30) NOT NULL,
provider_ref VARCHAR(100) NULL,
idempotency_key VARCHAR(64) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (payment_id),
UNIQUE KEY uq_payments_idempotency (idempotency_key),
UNIQUE KEY uq_payments_provider_ref (provider, provider_ref),
KEY idx_payments_order (order_id),
CONSTRAINT fk_payments_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE RESTRICT,
CONSTRAINT chk_payments_amount CHECK (amount > 0)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 48: 创建退款表
每笔退款指向一笔支付，并有自己的状态。
CREATE TABLE refunds (
refund_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
payment_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status ENUM('pending','succeeded','failed') NOT NULL DEFAULT 'pending',
reason VARCHAR(200) NULL,
provider_ref VARCHAR(100) NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (refund_id),
KEY idx_refunds_payment (payment_id),
CONSTRAINT fk_refunds_payment FOREIGN KEY (payment_id)
REFERENCES payments (payment_id) ON DELETE RESTRICT,
CONSTRAINT chk_refunds_amount CHECK (amount > 0)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 49: 安全地记录一次支付尝试
用相同的键重试会返回同一行。
INSERT INTO payments
(order_id, provider, provider_ref, idempotency_key, amount, status)
VALUES
(3, 'sample-gateway', NULL, 'order-3-attempt-1', 401.00, 'pending')
ON DUPLICATE KEY UPDATE updated_at = updated_at;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 50: 写入成功支付的示例数据
订单 1 和订单 2 的支付。
-- No explicit payment_id: example 49 may already have used the first auto-increment value.
INSERT INTO payments
(order_id, provider, provider_ref, idempotency_key, amount, status) VALUES
(1, 'sample-gateway', 'test-ch-0001', 'order-1-attempt-1', 830.00, 'succeeded'),
(2, 'sample-gateway', 'test-ch-0002', 'order-2-attempt-1', 640.00, 'succeeded');
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 51: 可退款金额
支付金额减去未失败的退款金额。
SELECT p.payment_id,
p.amount - COALESCE(SUM(CASE WHEN r.status <> 'failed' THEN r.amount END), 0) AS refundable
FROM payments AS p
LEFT JOIN refunds AS r ON r.payment_id = p.payment_id
WHERE p.payment_id = ?
GROUP BY p.payment_id, p.amount;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 52: 支付与订单不一致
找出支付已成功但订单仍在等待的记录。
SELECT p.payment_id, p.order_id, o.status
FROM payments AS p
JOIN orders AS o ON o.order_id = p.order_id
WHERE p.status = 'succeeded'
AND o.status = 'pending_payment';
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 8: 发货和履约
履约是打包和寄出商品的工作。一个订单可以拆成多个包裹发出，例如其中一件商品缺货时。shipments 表每个包裹一行，shipment_items 说明每个包裹包含哪些订单明细以及数量。状态列只能单向变化，因此已送达的包裹不能退回到准备中。
包裹和部分发货
shipments 引用 orders，shipment_items 引用 shipments 和 order_items。在 (shipment_id, order_item_id) 上的 UNIQUE 键防止同一明细在一个包裹中出现两次。这两张表组合起来，可以表示订单以任何方式拆分成包裹。
状态只能单向前进
把包裹从 preparing 改为 in_transit，再从 in_transit 改为 delivered，使用的 UPDATE 在 WHERE 子句中包含旧状态。乱序的更新会改变零行，应用会报告冲突，而不是覆盖状态。
跟踪数量
某明细尚未发出的数量等于订购数量减去该明细在 shipment_items 中的数量之和。HAVING 子句用来找出未完成的明细，管理员界面用它来显示部分发货的订单。
示意图
示例 53: 创建发货表
订单的包裹，包含快递公司和运单号。
CREATE TABLE shipments (
shipment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
carrier VARCHAR(60) NOT NULL,
tracking_number VARCHAR(60) NULL,
status ENUM('preparing','in_transit','delivered','returned') NOT NULL DEFAULT 'preparing',
shipped_at DATETIME(3) NULL,
delivered_at DATETIME(3) NULL,
PRIMARY KEY (shipment_id),
UNIQUE KEY uq_shipments_tracking (tracking_number),
KEY idx_shipments_order (order_id),
CONSTRAINT fk_shipments_order FOREIGN KEY (order_id)
REFERENCES orders (order_id) ON DELETE RESTRICT
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 54: 创建发货明细表
每个订单行在哪个包裹中，以支持部分发货。
CREATE TABLE shipment_items (
shipment_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
shipment_id BIGINT UNSIGNED NOT NULL,
order_item_id BIGINT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL,
PRIMARY KEY (shipment_item_id),
UNIQUE KEY uq_shipment_line (shipment_id, order_item_id),
KEY idx_shipment_items_line (order_item_id),
CONSTRAINT fk_shipment_items_shipment FOREIGN KEY (shipment_id)
REFERENCES shipments (shipment_id) ON DELETE CASCADE,
CONSTRAINT fk_shipment_items_line FOREIGN KEY (order_item_id)
REFERENCES order_items (order_item_id) ON DELETE RESTRICT,
CONSTRAINT chk_shipment_items_qty CHECK (quantity > 0)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 55: 写入已送达的发货示例数据
订单 1 的发货示例数据。
INSERT INTO shipments
(shipment_id, order_id, carrier, tracking_number, status, shipped_at, delivered_at) VALUES
(1, 1, 'Sample Courier', 'SC0000000001', 'delivered', '2026-10-02 09:00:00', '2026-10-04 15:20:00');
INSERT INTO shipment_items (shipment_id, order_item_id, quantity) VALUES (1, 1, 2);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 56: 打包并创建发货
在一个事务中更新订单状态并创建包裹。
START TRANSACTION;
UPDATE orders SET status = 'packed' WHERE order_id = 2 AND status = 'paid';
-- ROW_COUNT() = 0 means the order was not in the paid state: ROLLBACK.
INSERT INTO shipments (order_id, carrier, status) VALUES (2, 'Sample Courier', 'preparing');
SET @shipment_id = LAST_INSERT_ID();
INSERT INTO shipment_items (shipment_id, order_item_id, quantity)
SELECT @shipment_id, order_item_id, quantity
FROM order_items
WHERE order_id = 2;
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 57: 交给快递并记录送达
发货状态只能向前推进。
UPDATE shipments
SET status = 'in_transit', tracking_number = 'SC0000000002', shipped_at = NOW(3)
WHERE shipment_id = 2 AND status = 'preparing';
UPDATE shipments
SET status = 'delivered', delivered_at = NOW(3)
WHERE shipment_id = 2 AND status = 'in_transit';
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 58: 尚未发货的数量
每行的订购数量减去已发货数量。
SELECT oi.order_item_id, oi.quantity AS ordered,
COALESCE(SUM(si.quantity), 0) AS shipped
FROM order_items AS oi
LEFT JOIN shipment_items AS si ON si.order_item_id = oi.order_item_id
WHERE oi.order_id = 2
GROUP BY oi.order_item_id, oi.quantity
HAVING shipped < ordered;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 9: 评价和优惠券
评价建立信任，优惠券促进销售，但两者都可能被滥用。评价应当只来自真实且已送达的购买，优惠券应当只能在有效期和使用次数限制之内使用。本节的约束在数据库内部保护这两条规则，因此界面代码中的错误无法破坏它们。
经过验证的评价
reviews 表引用的是 order_items，而不仅仅是 products。order_item_id 上的 UNIQUE 键允许每个已购买明细只评价一次。插入语句只在订单状态为 delivered 时选择该明细，因此不能为已取消或未付款的订单写评价。
有限制的优惠券
优惠券有代码、百分比或固定金额折扣、最低消费、开始和结束日期，以及可选的最大使用次数。CHECK 约束要求结束日期晚于开始日期，折扣为正数，并且 used_count 不超过 max_uses。
有条件的兑换
两位买家可能同时使用优惠券的最后一次机会。在 WHERE 子句中检查日期和 used_count 的条件 UPDATE 可以安全地处理这一问题。应用只有在 ROW_COUNT 等于 1 时才应用折扣。
示例 59: 创建评价表
每个订单行一条评价，评分 1 到 5。
CREATE TABLE reviews (
review_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
product_id BIGINT UNSIGNED NOT NULL,
customer_id BIGINT UNSIGNED NOT NULL,
order_item_id BIGINT UNSIGNED NOT NULL,
rating TINYINT UNSIGNED NOT NULL,
body TEXT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (review_id),
UNIQUE KEY uq_reviews_order_item (order_item_id),
KEY idx_reviews_product_created (product_id, created_at),
CONSTRAINT fk_reviews_product FOREIGN KEY (product_id)
REFERENCES products (product_id) ON DELETE RESTRICT,
CONSTRAINT fk_reviews_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ON DELETE CASCADE,
CONSTRAINT fk_reviews_order_item FOREIGN KEY (order_item_id)
REFERENCES order_items (order_item_id) ON DELETE RESTRICT,
CONSTRAINT chk_reviews_rating CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 60: 创建优惠券表
有效期、使用次数上限和数据检查。
CREATE TABLE coupons (
coupon_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
code VARCHAR(40) NOT NULL,
discount_type ENUM('percent','fixed') NOT NULL,
discount_value DECIMAL(10,2) NOT NULL,
min_subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
starts_at DATETIME(3) NOT NULL,
ends_at DATETIME(3) NOT NULL,
max_uses INT UNSIGNED NULL,
used_count INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (coupon_id),
UNIQUE KEY uq_coupons_code (code),
CONSTRAINT chk_coupons_value CHECK (discount_value > 0),
CONSTRAINT chk_coupons_dates CHECK (ends_at > starts_at),
CONSTRAINT chk_coupons_uses CHECK (max_uses IS NULL OR used_count <= max_uses)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 61: 把订单关联到优惠券
在两张表都存在之后添加的外键。
ALTER TABLE orders
ADD CONSTRAINT fk_orders_coupon FOREIGN KEY (coupon_id)
REFERENCES coupons (coupon_id) ON DELETE SET NULL;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 62: 写入优惠券示例数据
一张百分比优惠券和一张固定金额优惠券。
INSERT INTO coupons
(coupon_id, code, discount_type, discount_value, min_subtotal, starts_at, ends_at, max_uses, used_count) VALUES
(1, 'WELCOME10', 'percent', 10.00, 300.00, '2026-01-01 00:00:00', '2027-01-01 00:00:00', NULL, 0),
(2, 'SHIP50', 'fixed', 50.00, 0.00, '2026-10-01 00:00:00', '2026-12-31 23:59:59', 100, 0);
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 63: 安全地使用优惠券
带条件的 UPDATE 防止超额使用。
UPDATE coupons
SET used_count = used_count + 1
WHERE coupon_id = ?
AND starts_at <= NOW(3)
AND ends_at > NOW(3)
AND (max_uses IS NULL OR used_count < max_uses);
-- The application applies the discount only when ROW_COUNT() = 1.
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 64: 已购买商品的评价
只有已送达的订单行才能评价。
INSERT INTO reviews (product_id, customer_id, order_item_id, rating, body)
SELECT v.product_id, o.customer_id, oi.order_item_id, 5, 'Fits well and the cotton feels soft.'
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN product_variants AS v ON v.variant_id = oi.variant_id
WHERE oi.order_item_id = 1
AND o.status = 'delivered';
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 10: 卖家和多商户市场
多商户市场允许许多卖家在同一个商店里销售。数据库必须知道每个商品属于哪个卖家，每个订单明细的收入归哪个卖家，平台保留多少佣金。本设计把卖家复制到每个订单明细上，因此以后商品转给其他卖家时，原卖家的销售历史不会改变。
卖家拥有商品
sellers 表保存名称、邮箱、状态和佣金比例。products.seller_id 是可为空的外键，通过 ALTER TABLE 添加，因此前面主题中的单商户表仍然可以正常使用。pending 状态会让新卖家的商品保持隐藏，直到管理员批准。
在每个明细上保存卖家快照
order_items.seller_id 在订单创建时复制，而不是之后通过 products 查找。这样商品换了所有者，订单历史仍然正确，并且佣金报表只需要一个简单的 JOIN。
结算和佣金
payouts 表每位卖家每个周期保存一行，包含总额、佣金和净额。CHECK 约束要求 net 等于 gross 减去 commission，(seller_id, period_start) 上的 UNIQUE 键防止同一周期的结算被重复创建。
示例 65: 创建卖家表
平台卖家及其佣金比例。
CREATE TABLE sellers (
seller_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(120) NOT NULL,
email VARCHAR(254) NOT NULL,
status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
commission_percent DECIMAL(5,2) NOT NULL DEFAULT 10.00,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (seller_id),
UNIQUE KEY uq_sellers_email (email),
CONSTRAINT chk_sellers_commission CHECK (commission_percent BETWEEN 0 AND 100)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 66: 商品归属于卖家
用 ALTER 添加的、允许为空的外键。
ALTER TABLE products
ADD COLUMN seller_id INT UNSIGNED NULL,
ADD KEY idx_products_seller (seller_id),
ADD CONSTRAINT fk_products_seller FOREIGN KEY (seller_id)
REFERENCES sellers (seller_id) ON DELETE RESTRICT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 67: 把卖家复制到订单行
商品换了卖家，历史记录仍然正确。
ALTER TABLE order_items
ADD COLUMN seller_id INT UNSIGNED NULL,
ADD KEY idx_order_items_seller (seller_id),
ADD CONSTRAINT fk_order_items_seller FOREIGN KEY (seller_id)
REFERENCES sellers (seller_id) ON DELETE RESTRICT;
UPDATE order_items AS oi
JOIN product_variants AS v ON v.variant_id = oi.variant_id
JOIN products AS p ON p.product_id = v.product_id
SET oi.seller_id = p.seller_id
WHERE oi.seller_id IS NULL;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 68: 创建结算表
每位卖家每个周期一条结算记录，带检查约束。
CREATE TABLE payouts (
payout_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
seller_id INT UNSIGNED NOT NULL,
period_start DATE NOT NULL,
period_end DATE NOT NULL,
gross_amount DECIMAL(12,2) NOT NULL,
commission_amount DECIMAL(12,2) NOT NULL,
net_amount DECIMAL(12,2) NOT NULL,
status ENUM('pending','paid') NOT NULL DEFAULT 'pending',
PRIMARY KEY (payout_id),
UNIQUE KEY uq_payouts_period (seller_id, period_start),
CONSTRAINT fk_payouts_seller FOREIGN KEY (seller_id)
REFERENCES sellers (seller_id) ON DELETE RESTRICT,
CONSTRAINT chk_payouts_net CHECK (net_amount = gross_amount - commission_amount)
) ENGINE=InnoDB;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 69: 写入卖家及其商品分配
两位卖家及其商品。
INSERT INTO sellers (seller_id, name, email, status, commission_percent) VALUES
(1, 'Lanna Threads', '[email protected]', 'active', 12.00),
(2, 'Sample Bags Co', '[email protected]', 'active', 10.00);
UPDATE products SET seller_id = 1 WHERE product_id IN (1, 3);
UPDATE products SET seller_id = 2 WHERE product_id = 2;
UPDATE order_items SET seller_id = 1 WHERE variant_id IN (1, 2, 3);
UPDATE order_items SET seller_id = 2 WHERE variant_id = 4;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 70: 一个月的佣金报表
每位卖家的销售总额和佣金。
SELECT s.name,
COUNT(DISTINCT oi.order_id) AS orders,
SUM(oi.line_total) AS gross,
ROUND(SUM(oi.line_total) * s.commission_percent / 100, 2) AS commission
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN sellers AS s ON s.seller_id = oi.seller_id
WHERE o.status IN ('paid','packed','shipped','delivered')
AND o.created_at >= '2026-10-01'
AND o.created_at < '2026-11-01'
GROUP BY s.seller_id, s.name, s.commission_percent
ORDER BY gross DESC;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 11: 事务：下单与预留库存
下单是商店中最重要的事务。它创建订单、复制明细、预留库存并关闭购物车。所有这些步骤必须同时成功或同时失败。本节按运行顺序逐步说明每一步，展示 SQL，并解释如果这些步骤没有包在一个事务中，会出现什么问题。
结账事务内部，逐步说明
1. START TRANSACTION。2. 使用 SELECT ... FOR UPDATE 锁定购物车对应的 inventory 行，并按 variant_id 升序排列。3. 用当前价格计算小计。4. 插入订单表头，并复制默认地址。5. 插入带有价格快照的订单明细。6. 用一条与 order_items 连接的 UPDATE 预留库存。7. 为每个明细写入一行原因为 reserve 的流水。8. 把购物车状态设为 converted。9. COMMIT。示例 76 的存储过程把这九个步骤放在一处展示。
没有事务会怎样
没有事务时，如果在第 4 步之后系统崩溃，就会出现没有预留库存的订单，同一件商品可能被卖两次。如果在第 6 步之后、第 7 步之前崩溃，计数器就会出现流水无法解释的数字。如果订单创建后、购物车关闭前失败，顾客可能为同一个购物车付两次款。这些都是普通的故障，而不是特殊情况。
加锁顺序与短事务
所有地方都按相同顺序加锁。如果一个结账先锁定规格 4 再锁定规格 1，而另一个先锁定规格 1 再锁定规格 4，两者可能发生死锁，MySQL 会以错误 1213 回滚其中一个。事务要保持简短：不要在其中调用支付服务商、发送邮件或等待用户。预留是一个有时限的承诺，支付结果决定它是否变成销售。
示意图
示例 71: 结算: 锁定库存行
按升序执行 SELECT FOR UPDATE。
-- Cart 1 holds three of variant 1 and two of variant 4 (after example 34).
START TRANSACTION;
SELECT variant_id, on_hand, reserved
FROM inventory
WHERE variant_id IN (1, 4)
ORDER BY variant_id
FOR UPDATE;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 72: 结算: 创建订单主表
总额以及默认地址的快照。
-- Subtotal: 3 x 390.00 + 2 x 590.00 = 2350.00. Shipping 50.00. No discount.
INSERT INTO orders
(order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code)
SELECT 'SH-20261030-0004', 1, 'pending_payment', 2350.00, 0.00, 50.00, 2400.00,
a.recipient_name, a.line1, a.city, a.postal_code, a.country_code
FROM addresses AS a
WHERE a.customer_id = 1 AND a.is_default = 1;
SET @order_id = LAST_INSERT_ID();
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 73: 结算: 复制购物车明细
购买时刻的价格。
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT @order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = 1;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 74: 结算: 重新计算总额
总额来自订单明细，而不是购物车。
UPDATE orders AS o
JOIN (
SELECT order_id, SUM(line_total) AS lines_total
FROM order_items
WHERE order_id = @order_id
GROUP BY order_id
) AS t ON t.order_id = o.order_id
SET o.subtotal = t.lines_total,
o.grand_total = t.lines_total - o.discount_total + o.shipping_fee;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 75: 结算: 预留库存并写入流水
CHECK 规则让库存不足的预留失败。
-- If a line asks for more than is on hand, the CHECK constraint fails this statement with error 3819.
-- The whole statement is undone, and the transaction is rolled back by the caller.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved + oi.quantity
WHERE oi.order_id = @order_id;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, quantity, 'reserve', @order_id
FROM order_items
WHERE order_id = @order_id;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 76: 用存储过程下单
完整的结算流程，任何错误都会回滚。
DELIMITER $$
CREATE PROCEDURE place_order(
IN p_customer_id BIGINT UNSIGNED,
IN p_cart_id BIGINT UNSIGNED,
IN p_order_number VARCHAR(20),
OUT p_order_id BIGINT UNSIGNED)
BEGIN
DECLARE v_subtotal DECIMAL(12,2) DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
-- 1 lock the stock rows of the cart in ascending variant_id order
SELECT i.variant_id
FROM inventory AS i
JOIN cart_items AS ci ON ci.variant_id = i.variant_id
WHERE ci.cart_id = p_cart_id
ORDER BY i.variant_id
FOR UPDATE;
-- 2 the cart must be open and belong to this customer
IF NOT EXISTS (SELECT 1 FROM carts
WHERE cart_id = p_cart_id AND customer_id = p_customer_id AND status = 'open') THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cart is not open for this customer';
END IF;
-- 3 subtotal from the current prices
SELECT COALESCE(SUM(v.price * ci.quantity), 0) INTO v_subtotal
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
WHERE ci.cart_id = p_cart_id;
IF v_subtotal = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cart is empty';
END IF;
-- 4 order header with the default address
INSERT INTO orders
(order_number, customer_id, status, subtotal, discount_total, shipping_fee, grand_total,
ship_recipient, ship_line1, ship_city, ship_postal_code, ship_country_code)
SELECT p_order_number, p_customer_id, 'pending_payment', v_subtotal, 0.00, 50.00, v_subtotal + 50.00,
a.recipient_name, a.line1, a.city, a.postal_code, a.country_code
FROM addresses AS a
WHERE a.customer_id = p_customer_id AND a.is_default = 1;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'customer has no default address';
END IF;
SET p_order_id = LAST_INSERT_ID();
-- 5 order lines with the price snapshot
INSERT INTO order_items
(order_id, variant_id, sku_snapshot, product_name_snapshot, unit_price, quantity)
SELECT p_order_id, v.variant_id, v.sku, p.name, v.price, ci.quantity
FROM cart_items AS ci
JOIN product_variants AS v ON v.variant_id = ci.variant_id
JOIN products AS p ON p.product_id = v.product_id
WHERE ci.cart_id = p_cart_id;
-- 6 reserve stock; the CHECK constraint stops oversell
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved + oi.quantity
WHERE oi.order_id = p_order_id;
-- 7 ledger and cart status
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, quantity, 'reserve', p_order_id
FROM order_items
WHERE order_id = p_order_id;
UPDATE carts SET status = 'converted' WHERE cart_id = p_cart_id;
COMMIT;
END$$
DELIMITER ;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 12: 事务：支付成功、失败、取消和退款
订单提交后，支付结果决定预留会发生什么。成功会把预留变成销售，失败会释放预留，取消或退款会把商品放回库存。这些事务都经过设计，使得支付服务商重复发送的消息在第二次处理时不会再产生任何改变。
支付成功
webhook 处理程序启动事务，把支付从 pending 更新为 succeeded。如果没有任何行被修改，说明这是重复的 webhook，处理程序会回滚并返回 200。否则会把订单标记为 paid，从 on_hand 和 reserved 中同时扣减数量，使预留转为已售出，并为每个明细写入一行 sale 流水。示例 77 展示了完整的顺序。
支付失败与取消
支付失败会把支付标记为 failed，取消 pending_payment 状态的订单，并从 reserved 中减去预留数量来释放库存。只有订单更新恰好修改一行时才会释放，因此预留绝不会被释放两次。打包前取消已付款订单，会把数量加回 on_hand 并写入一行 cancel 流水，见示例 79。
退款时不持有锁
退款在调用服务商的前后分为两个短事务。第一个事务记录待处理的退款并提交。调用服务商时不持有任何锁。第二个事务保存服务商的引用号，把退款标记为成功，并且只有在成功的退款金额覆盖支付金额时，才把订单标记为 refunded。示例 81 锁定支付行，使两个退款请求不能同时通过可退金额检查。
示意图
示例 77: 支付成功回调
带条件的步骤：重复的回调不会改变任何内容。
START TRANSACTION;
UPDATE payments
SET status = 'succeeded'
WHERE provider = 'sample-gateway' AND provider_ref = ? AND status = 'pending';
-- ROW_COUNT() = 0: this webhook was already handled. ROLLBACK and answer 200 to the gateway.
UPDATE orders
SET status = 'paid'
WHERE order_id = ? AND status = 'pending_payment';
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.on_hand = i.on_hand - oi.quantity,
i.reserved = i.reserved - oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, -quantity, -quantity, 'sale', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 78: 支付失败释放库存
预留只释放一次。
START TRANSACTION;
UPDATE payments
SET status = 'failed'
WHERE provider = 'sample-gateway' AND provider_ref = ? AND status = 'pending';
-- ROW_COUNT() = 0: already handled, ROLLBACK.
UPDATE orders
SET status = 'cancelled'
WHERE order_id = ? AND status = 'pending_payment';
-- ROW_COUNT() = 0 here too: ROLLBACK, so the reservation is never released twice.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.reserved = i.reserved - oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, 0, -quantity, 'release', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 79: 打包前取消已支付订单
库存回到现有量。
START TRANSACTION;
UPDATE orders
SET status = 'cancelled'
WHERE order_id = ? AND status = 'paid';
-- ROW_COUNT() = 0: the order was packed or shipped already. ROLLBACK and show a return flow instead.
UPDATE inventory AS i
JOIN order_items AS oi ON oi.variant_id = i.variant_id
SET i.on_hand = i.on_hand + oi.quantity
WHERE oi.order_id = ?;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
SELECT variant_id, quantity, 0, 'cancel', order_id
FROM order_items
WHERE order_id = ?;
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 80: 在调用支付服务商前后分两个事务退款
网络调用期间不持有行锁。
-- Transaction 1: record the intent, then commit before calling the provider.
START TRANSACTION;
SELECT payment_id FROM payments WHERE payment_id = ? AND status = 'succeeded' FOR UPDATE;
INSERT INTO refunds (payment_id, amount, status, reason)
VALUES (?, ?, 'pending', 'Customer returned the item');
COMMIT;
-- The provider call happens here, outside any database transaction, so no row lock is held.
-- Transaction 2: store the provider result and update the order.
START TRANSACTION;
UPDATE refunds
SET status = 'succeeded', provider_ref = ?
WHERE refund_id = ? AND status = 'pending';
UPDATE orders AS o
JOIN payments AS p ON p.order_id = o.order_id AND p.status = 'succeeded'
SET o.status = 'refunded'
WHERE o.order_id = ?
AND o.status IN ('paid','packed','shipped','delivered')
AND (SELECT COALESCE(SUM(r.amount), 0) FROM refunds AS r
WHERE r.payment_id = p.payment_id AND r.status = 'succeeded') >= p.amount;
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 81: 用行锁限制退款上限
两个退款请求不能同时通过检查。
START TRANSACTION;
SELECT amount FROM payments WHERE payment_id = ? FOR UPDATE;
SELECT COALESCE(SUM(CASE WHEN status <> 'failed' THEN amount END), 0) AS already_refunded
FROM refunds
WHERE payment_id = ?;
-- The application compares the requested amount with (amount - already_refunded). ROLLBACK when it is too large.
INSERT INTO refunds (payment_id, amount, status, reason)
VALUES (?, ?, 'pending', ?);
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 82: 退回的商品重新入库
只有检验合格、可再次销售时才入库。
START TRANSACTION;
UPDATE inventory
SET on_hand = on_hand + 1
WHERE variant_id = 1;
INSERT INTO inventory_movements (variant_id, delta_on_hand, delta_reserved, reason, order_id)
VALUES (1, 1, 0, 'return', 1);
COMMIT;
-- Restock only when the goods are inspected and resaleable. Otherwise write an adjustment instead.
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 13: 并发：两位买家和最后一件商品
许多买家可能在同一时刻读取同一份库存。如果代码先读取数字，再做判断，然后写入，那么两位买家都可能看到仅剩的一件并都买下它。本节展示这一漏洞、两种修复方法、MySQL 8 提供的锁类型，以及当加锁顺序不同时出现的死锁。
两位买家，一件商品
买家 A 读到 available = 1，买家 B 也读到 available = 1。两人都把 reserved 增加 1。如果没有保护，reserved 会变成 2，而 on_hand 仍是 1，这就是超卖。CHECK 约束会以错误 3819 阻止第二次写入，但这只是安全网，不是完整的修复。真正的修复是在同一条语句中同时完成检查和写入。
锁：FOR UPDATE、NOWAIT 和 SKIP LOCKED
SELECT ... FOR UPDATE 会让其他事务等待，直到当前事务提交。NOWAIT 会立即以错误 3572 失败，而不是等待，适合面向用户的页面。SKIP LOCKED 让工作进程领取下一批没有被其他工作进程持有的任务，适合支付检查队列。只有在需要读取一行并确保它不会改变时，才使用 SELECT ... FOR SHARE。
死锁与乐观锁
当两个事务各自持有对方需要的锁时，就会发生死锁。InnoDB 会检测到死锁，并以错误 1213 回滚其中一个，因此应用必须重试整个事务。根本的解决方法是在任何地方都使用相同的加锁顺序。对于很少发生竞争的商品编辑，使用带 version 列的乐观锁可以完全不持有锁：UPDATE 包含读取时的版本号，如果没有任何行被修改，说明别人已经先保存了。
示例 83: 重现超卖
两位买家、一件商品、没有任何保护。
-- Setup: one unit of variant 2 is left.
UPDATE inventory SET on_hand = 1, reserved = 0 WHERE variant_id = 2;
-- Buyer A and buyer B run these two statements at nearly the same time (no locking, no transaction):
SELECT on_hand - reserved AS available FROM inventory WHERE variant_id = 2; -- both read 1
UPDATE inventory SET reserved = reserved + 1 WHERE variant_id = 2; -- both write
-- Without the CHECK constraint, reserved would become 2 while on_hand is 1: an oversell.
-- With it, the second write fails with error 3819.
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 84: 最后一件：原子化的 UPDATE
一条带条件的语句，只把商品给一位买家。
UPDATE inventory
SET reserved = reserved + 1
WHERE variant_id = 2
AND on_hand - reserved >= 1;
-- ROW_COUNT() = 1: this buyer got the item.
-- ROW_COUNT() = 0: sold out. Show "sold out" and do not create an order.
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 85: NOWAIT 与 SKIP LOCKED
立即失败，或让工作进程跳过被占用的行。
-- NOWAIT: error 3572 at once if another transaction holds the row.
START TRANSACTION;
SELECT on_hand, reserved FROM inventory WHERE variant_id = 2 FOR UPDATE NOWAIT;
ROLLBACK;
-- SKIP LOCKED: a worker takes up to 10 pending payment checks that no other worker holds.
START TRANSACTION;
SELECT payment_id FROM payments
WHERE status = 'pending'
ORDER BY payment_id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- ...check each payment with the provider, update its row, then:
COMMIT;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 86: 查看锁
performance_schema 显示谁持有锁、谁在等待。
SELECT engine_transaction_id, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 'inventory';
SELECT requesting_engine_transaction_id, blocking_engine_transaction_id
FROM performance_schema.data_lock_waits;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 87: 死锁及其修复
所有地方都按相同顺序加锁。
-- Session A -- Session B
START TRANSACTION; START TRANSACTION;
UPDATE inventory SET reserved = reserved + 1 UPDATE inventory SET reserved = reserved + 1
WHERE variant_id = 1; WHERE variant_id = 4;
UPDATE inventory SET reserved = reserved + 1 UPDATE inventory SET reserved = reserved + 1
WHERE variant_id = 4; -- waits for B WHERE variant_id = 1; -- deadlock
-- One session gets error 1213 and is rolled back. The fix: both sessions lock in the same order (1, then 4).
-- Inspect the last deadlock with: SHOW ENGINE INNODB STATUS;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 88: 带版本号列的乐观锁
不持有锁，也能发现被覆盖的更新。
ALTER TABLE products ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 0;
UPDATE products
SET base_price = 420.00, version = version + 1
WHERE product_id = 1 AND version = 0;
-- ROW_COUNT() = 0: someone else changed the product first. Reload it and try again.
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 14: 索引和查询计划
索引是某些列的有序副本，它让 MySQL 无需读取整张表就能找到行。但每个索引都会拖慢写入并占用空间，因此每个索引都需要理由。本节解释如何选择列的顺序，如何阅读 EXPLAIN 和 EXPLAIN ANALYZE，以及如何找到无人使用的索引。
选择复合索引的顺序
复合索引从左到右生效。索引 (customer_id, created_at) 支持按 customer_id 过滤并按 created_at 排序的查询，但不支持只按 created_at 过滤的查询。把等值条件的列放在前面，排序列放在后面。如果最新的行读取最频繁，可以使用降序索引，例如 (created_at DESC, order_id DESC)。
阅读 EXPLAIN
EXPLAIN 显示访问类型、所选索引、估计行数，以及是否需要临时表或 filesort。自 MySQL 8.0.18 起可用的 EXPLAIN ANALYZE 会真正运行查询，并显示每一步的实际时间和行数。请比较估计值与实际值。两者差距很大通常说明统计信息过期，ANALYZE TABLE 可以解决。
查找未使用和重复的索引
sys 架构中的视图 sys.schema_unused_indexes 和 sys.schema_redundant_indexes 会列出候选项。如果已经存在 (status, created_at)，那么 (status) 上的索引就是重复的。计数器会在服务器重启时清零，因此在删除索引前，应当跨越一个完整的业务周期（例如月末作业）进行检查。不可见索引可以让你在不丢失索引的情况下测试删除的影响。
示例 89: 测试不可见索引
删除索引前先检查执行计划。
ALTER TABLE orders ALTER INDEX idx_orders_status_created INVISIBLE;
EXPLAIN SELECT order_id FROM orders WHERE status = 'paid' ORDER BY created_at LIMIT 50;
-- Bring it back if the plans get worse:
ALTER TABLE orders ALTER INDEX idx_orders_status_created VISIBLE;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 90: 最低价格的覆盖索引
只靠索引就能回答查询。
CREATE INDEX idx_variants_product_active_price
ON product_variants (product_id, is_active, price);
EXPLAIN SELECT product_id, MIN(price)
FROM product_variants
WHERE is_active = 1
GROUP BY product_id;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 91: EXPLAIN ANALYZE 与直方图
测量真实工作量，并帮助优化器。
EXPLAIN ANALYZE
SELECT order_id, order_number, created_at
FROM orders
WHERE customer_id = 1
ORDER BY created_at DESC
LIMIT 20;
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 92: 未使用和重复的索引
sys 架构帮你找出可删除的候选索引。
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'shop';
-- Usage counters restart when the server restarts. Check over a full business cycle before you drop an index.
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 15: 视图、报表和分区
页面和报表会反复提出相同的问题。视图为这些问题命名，报表汇总销售，分区让非常大的历史表更易管理。本节介绍库存页面和管理员列表的视图、带合计的月度报表，以及分区的事件日志。
用视图隐藏连接
v_available_stock 这样的视图把 inventory、product_variants 和 products 连接一次，因此每个页面不必重复编写连接。视图不是缓存：MySQL 每次都会执行视图的 SELECT。涉及多表连接的视图不能更新。应当只把视图的 SELECT 权限授予报表角色，而不是基础表。
报表
月度销售报表按月汇总已付款及之后状态订单的 grand_total。GROUP BY ... WITH ROLLUP 会添加合计行。报表直接读取在线表，因此繁重的报表应在只读副本上或夜间运行，并且绝不能让报表持有阻塞结账的锁。
用分区管理历史数据
PARTITION BY RANGE COLUMNS 按时间段把表分开，这里使用 created_at。主键必须包含分区列，因此是 (event_id, created_at)。删除旧分区可以一次移除整个月的数据，避免很长的 DELETE 撑大 undo 日志。分区有助于大型历史表，但不能替代良好的索引。
示例 93: 库存视图
把连接隐藏在视图里，界面代码更简单。
CREATE OR REPLACE VIEW v_available_stock AS
SELECT v.variant_id, v.sku, p.name AS product_name,
i.on_hand, i.reserved, i.on_hand - i.reserved AS available
FROM inventory AS i
JOIN product_variants AS v ON v.variant_id = i.variant_id
JOIN products AS p ON p.product_id = v.product_id;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 94: 订单汇总视图
管理页面每个订单一行。
CREATE OR REPLACE VIEW v_order_summary AS
SELECT o.order_id, o.order_number, o.status, c.full_name AS customer_name,
o.grand_total, o.created_at,
COUNT(oi.order_item_id) AS line_count,
COALESCE(SUM(oi.quantity), 0) AS unit_count
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.order_number, o.status, c.full_name, o.grand_total, o.created_at;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 95: 月度销售与合计行
GROUP BY WITH ROLLUP。
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
COUNT(*) AS orders,
SUM(grand_total) AS revenue
FROM orders
WHERE status IN ('paid','packed','shipped','delivered')
GROUP BY month WITH ROLLUP;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 96: 分区事件日志
范围分区，并能低成本删除旧月份的数据。
CREATE TABLE payment_events (
event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
payment_id BIGINT UNSIGNED NOT NULL,
event_type VARCHAR(40) NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (event_id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (created_at) (
PARTITION p2026h1 VALUES LESS THAN ('2026-07-01'),
PARTITION p2026h2 VALUES LESS THAN ('2027-01-01'),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
-- Later: remove a whole old period at once.
-- ALTER TABLE payment_events DROP PARTITION p2026h1;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
知识点 16: 专业实践：迁移、审计、备份和权限
生产环境中的数据库会随时间变化，可能损坏，并且保存个人数据。专业实践意味着变更有版本并经过审查，每一次状态变化都有记录，备份通过恢复测试验证，每个程序只拥有所需的权限。本节介绍把学习项目与值得信赖的商店区分开来的习惯。
有版本的迁移
每次结构变更都是一个编号文件，按顺序只运行一次。schema_migrations 表记录版本号和文件的校验和，因此被编辑过的迁移可以被发现。不要修改已经在生产环境运行过的迁移，应该编写一个新的迁移来修正它。
审计记录和备份
orders 上的 AFTER UPDATE 触发器会把每次状态变化连同用户和时间写入 order_audit。使用 mysqldump --single-transaction 进行备份，可以在不锁定 InnoDB 表的情况下得到一致的快照。从未恢复过的备份不能算备份：请把它恢复到空的测试数据库，并比较行数。
最小权限
应用用户对商店数据拥有 SELECT、INSERT、UPDATE 和 DELETE 权限，但没有 DROP 或 ALTER。报表使用只能读取两个视图的角色。为迁移保留管理员账户，所有密码都保存在密钥管理系统中，并且每个季度用 SHOW GRANTS 检查权限。
示例 97: 带版本的迁移
每项变更按顺序只执行一次，并带有校验和。
CREATE TABLE schema_migrations (
version VARCHAR(20) NOT NULL,
checksum CHAR(64) NOT NULL,
applied_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (version)
) ENGINE=InnoDB;
-- File V003__add_order_audit.sql: each migration runs once, in order, and the checksum detects edits.
-- The migration tool inserts ('V003', '<sha256 of the file>') after it succeeds.
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 98: 订单状态审计触发器
记录每次状态变化及其操作者。
CREATE TABLE order_audit (
audit_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
old_status VARCHAR(30) NOT NULL,
new_status VARCHAR(30) NOT NULL,
changed_by VARCHAR(100) NOT NULL,
changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (audit_id),
KEY idx_order_audit_order (order_id, changed_at)
) ENGINE=InnoDB;
DELIMITER $$
CREATE TRIGGER trg_orders_status_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF OLD.status <> NEW.status THEN
INSERT INTO order_audit (order_id, old_status, new_status, changed_by)
VALUES (OLD.order_id, OLD.status, NEW.status, CURRENT_USER());
END IF;
END$$
DELIMITER ;
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 99: 备份与恢复
使用 --single-transaction 的 mysqldump，然后做恢复测试。
# Back up: --single-transaction gives a consistent view of InnoDB tables without locking them.
mysqldump --single-transaction --routines --triggers --events --set-gtid-purged=OFF \
-u shop_admin -p shop > shop-2026-10-30.sql
# Restore into an empty test database first, then check row counts before using it.
mysql -u shop_admin -p shop_restore < shop-2026-10-30.sql
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
示例 100: 只读视图的报表角色
报表所需的最小权限。
CREATE ROLE IF NOT EXISTS 'shop_reporting';
GRANT SELECT ON shop.v_order_summary TO 'shop_reporting';
GRANT SELECT ON shop.v_available_stock TO 'shop_reporting';
CREATE USER IF NOT EXISTS 'report_user'@'localhost' IDENTIFIED BY '<strong-password>';
GRANT 'shop_reporting' TO 'report_user'@'localhost';
SET DEFAULT ROLE 'shop_reporting' TO 'report_user'@'localhost';
SHOW GRANTS FOR 'report_user'@'localhost' USING 'shop_reporting';
*此示例未在本机运行，因为准备本文的计算机没有安装 MySQL 8。*
实验：建立商店数据库并运行一次结账
在练习数据库中把本文内容串起来。你将创建结构、加载示例数据、运行一次支付成功的结账，并刻意尝试破坏库存规则。下面每一步都需要你自己动手检查，因为本文准备期间没有运行任何示例。
1. 使用 utf8mb4 创建 shop_practice 数据库，然后创建只具有 SELECT、INSERT、UPDATE 和 DELETE 权限的用户，并运行示例 1 到 6。
2. 按示例 7 到 32 创建 customers、addresses、categories、products、product_variants、inventory、inventory_movements、carts 和 cart_items 表，并使用示例 9、10、18、19、25 和 33 加载初始数据。
3. 按示例 39、40、47 和 48 创建 orders、order_items、payments 和 refunds 表，然后运行示例 44 到 46 中的订单历史查询。
4. 为顾客 1 和购物车 1 运行示例 76 中的结账，然后使用示例 26 和 30 检查 ROW_COUNT 和库存行。
5. 使用相同的 provider_ref 连续运行两次示例 77，并确认第二次没有任何改变。这是幂等性测试。
6. 在两个 MySQL 会话中运行示例 83 和 84，每位买家一个会话，并确认只有一位买家拿到最后一件。
7. 记录每个示例的运行结果，并在把任何代码用于真实商店之前，与本文中预期的行为进行比较。
下一步
读完本文后，请学习调用这些事务的应用层：REST 接口、输入校验，以及死锁后的重试。然后阅读支付服务商的 webhook 文档，因为现实中商店的大多数漏洞都出现在 webhook 处理程序里。最后学习 MySQL 复制和只读副本，把报表从结账数据库中分离出来。
总结
商店数据库围绕几条规则构建：把价格和地址复制到订单中；把库存保存为带 CHECK 约束的计数器，并配合只能追加的流水；让每一次付款和库存变化都成为可以安全重复执行的事务。按同一顺序加锁，保持事务简短，并且绝不在持有锁时调用支付服务商。索引要根据页面实际运行的查询来选择，用 EXPLAIN 检查，并为每一次结构变更记录版本。本文写作期间，100 个示例都没有运行，因此请在使用之前，先在练习数据库中逐一测试。