4. 数据类型与约束实战

在真实工程里，建表语句往往决定了系统未来几年的稳定性。字段类型选错，轻则浪费存储、索引膨胀，重则精度丢失、查询无法命中索引；约束缺失，则脏数据会从接口、脚本、人工修数等各个入口渗入，后期清洗成本极高。本节围绕一个“订单与支付”场景，把数据类型选择、主外键、唯一约束、检查约束和默认值串成一套可落地的建表实践。

一、先定业务语义，再定数据类型

选类型的核心不是“哪个短用哪个”，而是先明确字段的业务语义：它表示金额、时间点、枚举状态，还是外部单号？不同语义对应不同的精度、范围和比较规则。

  • 金额：绝不能用 FLOAT/DOUBLE，二进制浮点无法精确表示 0.1。工程中统一用 DECIMAL(18,2)（分币种可到 4 位小数），保证精确加减与对账一致。
  • 时间点：用 DATETIME(3) 或 TIMESTAMP，毫秒精度足够订单场景；只表示“日期”用 DATE，不要用字符串存时间，否则范围查询和时区处理都会出问题。
  • 状态：用 TINYINT 或 SMALLINT 存枚举码，配合检查约束限定取值范围，比 VARCHAR 更省空间且比较更快。
  • 外部单号：长度可预期的用 CHAR(n)，长度不定的用 VARCHAR(n)，并给足余量，避免后期改类型锁表。

下面是一张订单主表的建表示例，字段类型与约束同时落地：

CREATE TABLE orders (
  order_id      BIGINT       NOT NULL AUTO_INCREMENT,
  order_no      VARCHAR(32)  NOT NULL,
  user_id       BIGINT       NOT NULL,
  total_amount  DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  status        TINYINT      NOT NULL DEFAULT 0,
  pay_channel   VARCHAR(16)  DEFAULT 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 uk_order_no (order_no),
  CONSTRAINT ck_status CHECK (status IN (0,1,2,3,4)),
  CONSTRAINT ck_amount CHECK (total_amount >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二、主键、外键与唯一约束的分工

主键解决“行身份唯一”，外键解决“引用完整性”，唯一约束解决“业务键不重复”，三者职责不同，不能互相替代。

主键建议使用自增 BIGINT 或雪花 ID。自增主键写入局部性好、索引页分裂少；分布式场景可用有序 ID 生成器。不要用业务单号做主键，因为业务单号可能变更、长度较长，会拖累所有二级索引。

外键在互联网高并发系统中常被“逻辑外键”替代，即不建数据库级 FOREIGN KEY，而在应用层保证引用有效。原因是外键检查会带来额外锁与级联风险。但在内部管理系统、数据一致性要求高的场景，物理外键仍然值得保留。若保留，需明确级联策略：

ALTER TABLE order_items
  ADD CONSTRAINT fk_item_order
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
  ON DELETE CASCADE ON UPDATE RESTRICT;

唯一约束用于业务键，例如 order_no、user_id + id_card。它既保证数据不重复，也能让查询走唯一索引。注意唯一约束对 NULL 的处理：多数数据库允许多行 NULL，若业务要求“非空且唯一”，必须同时加 NOT NULL。

三、检查约束与默认值的工程价值

检查约束把业务规则前移到数据库层，是防止脏数据最经济的手段。例如状态只能取 0~4、金额不能为负、支付渠道只能是白名单值。它不能替代应用校验，但能兜住脚本、修数、导入等旁路入口。

默认值则减少插入语句的冗余，并保证字段始终有确定值。常见组合是：状态默认 0（待支付）、金额默认 0.00、创建时间默认当前时间、更新时间在更新时自动刷新。需要注意，默认值只在“未显式提供该列”时生效，显式传 NULL 仍会写入 NULL，因此关键字段应同时加 NOT NULL。

检查约束与默认值应写在建表语句中，随版本迁移脚本一起管理，避免只在某台环境手工添加导致环境不一致。

四、约束落地时的常见冲突与处理

约束越多，写入失败的概率越高，因此必须设计好错误处理。例如唯一约束冲突时，应用应捕获重复键异常并转为“单号已存在”的业务提示，而不是直接抛 500。检查约束失败时，应记录具体字段与非法值，便于定位。

另一个常见问题是批量导入。导入前可临时关闭检查约束（部分数据库支持），导入后统一校验；但更稳妥的做法是先导入临时表，清洗后再插入正式表，让约束在正式表上始终生效。

下面这张图把本节的核心对象与约束关系集中呈现，便于对照建表时逐项检查：

订单表约束与类型落地关系图 orders（订单主表） order_id BIGINT · PK · AUTO_INCREMENT order_no VARCHAR(32) · NOT NULL · UNIQUE user_id BIGINT · NOT NULL total_amount DECIMAL(18,2) · DEFAULT 0.00 status TINYINT · DEFAULT 0 · CHECK 0~4 created_at DATETIME(3) · DEFAULT NOW updated_at DATETIME(3) · ON UPDATE NOW order_items（订单明细） item_id BIGINT · PK order_id BIGINT · FK → orders qty INT · CHECK qty > 0 price DECIMAL(18,2) · NOT NULL FK 引用 约束类型速查 PK 主键：行唯一标识，建议自增 BIGINT FK 外键：引用完整性，注意级联策略 UNIQUE 唯一：业务键不重复 CHECK 检查：限定取值范围 DEFAULT 默认：保证字段有确定初值 NOT NULL：关键字段禁止空值

五、一套可执行的建表检查清单

把上面的经验固化成检查项，每次建表或改表时逐条核对，能显著降低返工率：

检查项 判断标准 常见错误
金额类型 DECIMAL(18,2) 及以上 用 FLOAT/DOUBLE 导致对账差 1 分
时间类型 DATETIME(3) 或 TIMESTAMP 用 VARCHAR 存时间，范围查询失效
主键 自增 BIGINT 或有序分布式 ID 用业务单号做主键，索引膨胀
唯一约束 业务键加 UNIQUE，且 NOT NULL 只加 UNIQUE 不加 NOT NULL，多行 NULL 绕过
检查约束 状态、数量、金额范围显式限定 只靠应用校验，脚本导入绕过
默认值 状态、金额、时间设默认 默认值缺失，插入 NULL 后统计出错

最后强调一点：约束不是越多越好，而是要与业务规则一一对应。每加一条约束，都要能回答“它防止了哪种脏数据、失败时应用如何处理”。当约束、类型、默认值三者与业务语义对齐后，表结构本身就成了一份可执行的业务规则文档，后续的查询优化、数据迁移和故障排查都会因此受益。

← 上一章 📖 返回目录 下一章 →