订单模型：类型、键与约束

← 本站索引 ｜ 练习答案

可运行 SQL

USE mysql_course;

CREATE TABLE customer (
  customer_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(320) NOT NULL,
  display_name VARCHAR(80) NOT NULL,
  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT uq_customer_email UNIQUE (email)
) ENGINE = InnoDB;

CREATE TABLE product (
  product_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  stock INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT uq_product_sku UNIQUE (sku),
  CONSTRAINT ck_product_price CHECK (price >= 0)
) ENGINE = InnoDB;

CREATE TABLE sales_order (
  order_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  total_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  version_no INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT ck_order_total CHECK (total_amount >= 0),
  CONSTRAINT ck_order_status
    CHECK (status IN ('pending','paid','cancelled')),
  CONSTRAINT fk_order_customer
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
) ENGINE = InnoDB;

机制解释

InnoDB 主键叶子页保存整行；二级索引叶子项携带主键，因此过宽或随机主键会扩大索引并降低写入局部性。NOT NULL、UNIQUE、CHECK 和 FOREIGN KEY 在数据库边界统一执行，不能被另一个脚本或服务绕过。

DECIMAL 保存精确十进制定点值。TIMESTAMP 会经过连接时区转换，DATETIME 保存字面日期时间；选择前要定义语义。VARCHAR 长度按字符声明，但索引和行大小受编码后的字节影响。

真实边界

• ALTER TABLE 的算法、锁级别和资源成本依具体变更、表结构和版本而定，必须在同规模克隆环境验证。
• 外键提供引用完整性，但会引入写入顺序、锁和运维依赖；跨服务或分片边界不能假装仍有单实例外键。
• CHECK 约束不能替代应用中的可行动错误提示。

练习

1. 插入非法负价格并记录错误。
2. 设计 order_item 表，保证同一订单商品唯一、数量大于零。
3. 说明为什么 email 是否区分大小写必须先定义业务语义。

验收清单

• 合法订单模型可创建，负金额和零数量由数据库拒绝。
• 每种类型、键和约束都能对应到一条业务不变量。
• DDL 变更方案说明锁、空间、回滚和兼容窗口。

完成后核对答案

深度加餐：类型设计是在保存未来的错误

金额、状态、时间和外部编号都要先确定语义，再决定列类型与约束。

先做一个可观察实验

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) CHARACTER SET ascii NOT NULL,
  total DECIMAL(12,2) NOT NULL,
  status ENUM('PENDING','PAID','CANCELLED') NOT NULL,
  placed_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  note VARCHAR(500) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_orders_order_no (order_no),
  CHECK (total >= 0)
) ENGINE=InnoDB;

预期：金额不会经历浮点舍入；订单号唯一；status 的合法集合和金额非负由数据库共同守门。

原理下潜与工程边界

DECIMAL 保存精确小数，FLOAT/DOUBLE 适合测量而非货币；VARCHAR 的字符集与排序规则影响长度、比较和索引。TIMESTAMP 有时区转换，DATETIME 只保存字面时间。

ENUM 变更需要迁移与应用协同；CHECK 在现代 MySQL 会被执行但旧版本兼容性要核对；隐式字符集转换可能让索引失效。

反例排错路线

用 SHOW CREATE TABLE 检查真实类型、约束和 collation；插入边界值后查看 SHOW WARNINGS，不要只看客户端是否显示成功。

练习加码（仍不提供答案）

加码：为多币种订单补 currency、税额和幂等外部号，写出精度、唯一性和时区决策记录。

验收时请补充：一份可复现的环境/数据前提、正常结果、失败结果以及可回滚或清理动作。

继续查阅：MySQL 8.4 参考手册（外部资料已收录于证据账本） · InnoDB 文档（外部资料已收录于证据账本）