📚 课程目录第4章数据类型与约束工程

4. 数据类型与约束工程

在 MySQL 企业级开发中，表结构设计质量往往比 SQL 写法更早决定系统的上限。数据类型选错，可能带来存储膨胀、索引失效、隐式转换和精度丢失；约束缺失，则会让脏数据在业务层之外悄悄进入数据库。本节围绕“类型选择”和“约束落地”两条主线，结合一个订单系统的实际表结构，说明如何做出可维护、可验证的设计决策。

一、数值类型：先看范围和精度，再看存储

整数类型的选择原则是“够用且不溢出”。TINYINT 占 1 字节，适合状态码、性别、逻辑删除标记；INT 占 4 字节，适合自增主键、数量、积分；BIGINT 占 8 字节，适合雪花 ID、流水号、时间戳毫秒值。很多团队习惯所有主键都用 BIGINT，这在分布式场景下是合理的，但在单库单表且数据量可控时，INT UNSIGNED 可把上限提升到约 42 亿，同时节省索引空间。

小数类型要区分“精确”和“近似”。金额、单价、税率必须使用 DECIMAL(M,D)，例如 DECIMAL(12,2) 表示总长 12 位、小数 2 位，能精确表示到分。若用 FLOAT 或 DOUBLE 存金额，累加时会出现 0.1+0.2=0.30000000000000004 这类误差，对账时非常危险。对于科学计算、统计比率等允许误差的场景，才考虑浮点类型。

二、字符串类型：CHAR、VARCHAR、TEXT 的边界

CHAR(N) 是定长，适合长度固定的编码，如国家代码 CHAR(2)、MD5 值 CHAR(32)；VARCHAR(N) 是变长，适合用户名、标题、地址等长度波动大的字段。注意 VARCHAR 的长度是字符数而非字节数，在 utf8mb4 下，一个汉字占 3 到 4 字节，索引长度限制需要按字节换算。例如 VARCHAR(255) 在 utf8mb4 下最大可能占 1020 字节，联合索引时要留意 3072 字节的上限。

TEXT 和 BLOB 用于大文本和二进制，它们通常不参与主键和普通索引，且查询时会带来额外的磁盘 IO。若字段经常被 WHERE 条件过滤，应优先用 VARCHAR 并建立索引；若只是展示详情，可拆到扩展表，避免主表行过大影响缓冲池命中率。

三、时间类型：DATETIME 与 TIMESTAMP 的取舍

DATETIME 存储字面值，范围 1000-01-01 到 9999-12-31，不受时区影响；TIMESTAMP 存储 UTC 时间戳，范围到 2038-01-19，会随时区转换。企业系统中，创建时间、更新时间建议用 DATETIME 并配合 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，避免 2038 问题；若需要跨时区展示，可在应用层统一转换。日期只用到天时用 DATE，时间只用到时分秒时用 TIME，不要一律用 DATETIME 浪费空间。

四、JSON 类型：灵活但要有约束

MySQL 5.7 起支持原生 JSON 类型，适合存储扩展属性、埋点参数、不固定的配置。它的优势是写入时校验 JSON 格式，并支持 JSON_EXTRACT、->> 等函数。但 JSON 字段不能直接建普通索引，需要生成列加索引，例如：

ALTER TABLE product
  ADD COLUMN brand VARCHAR(64)
  GENERATED ALWAYS AS (json_unquote(json_extract(attrs,'$.brand'))) STORED,
  ADD INDEX idx_brand (brand);

原则是：高频查询、需要排序分组的字段，应抽成独立列；低频、结构多变的扩展信息才放 JSON。不要把 JSON 当成逃避表结构设计的万能容器。

五、约束实战：主键、外键、唯一、检查

约束是数据库层的“最后一道防线”。主键保证行唯一且非空，推荐使用无业务含义的自增或雪花 ID，避免用手机号、身份证号做主键，因为业务键可能变更且较长。唯一约束用于业务唯一键，如用户名、订单号，它允许一个 NULL 值（除非列声明 NOT NULL）。外键用于维护引用完整性，例如订单明细的 product_id 引用商品表主键，可设置 ON DELETE RESTRICT 防止误删商品。检查约束在 MySQL 8.0.16 后真正生效，可用于限制状态值、金额范围：

CREATE TABLE orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_order_no (order_no),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT ck_orders_amount CHECK (amount >= 0),
  CONSTRAINT ck_orders_status CHECK (status IN (0,1,2,3))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

下面这张图把“类型选择—约束落地—验证反馈”串成一条可检查的设计链路，帮助你在建表前快速核对关键决策点。

六、常见陷阱与检查清单

第一，用 VARCHAR 存日期，导致范围查询无法走索引；第二，用 INT 存手机号，11 位数字超出 INT 上限约 21 亿，必须用 BIGINT 或 VARCHAR(11)；第三，外键列与引用列类型不一致，如 BIGINT 对 INT，会导致外键创建失败或隐式转换；第四，CHECK 约束在 8.0.16 之前被解析但忽略，升级后需重新验证；第五，JSON 字段用 LIKE '%...%' 查询，无法使用索引，应改为生成列或 JSON_CONTAINS 配合多值索引。

建表完成后，建议执行一次“反向验证”：向表中插入一条违反检查约束的记录，确认报错信息符合预期；再用 EXPLAIN 查看高频查询是否命中索引。只有约束真正拦截了脏数据、索引真正被优化器使用，表结构设计才算闭环。