MySQLNotes

第 04 章：数据类型与表设计

zjc 于 2026-01-04 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 表结构是长期资产。业务代码可以重构，错误的数据却很难修复。数据类型、约束和索引共同决定了存储成本、查询能力、写入吞吐和数据质量。

4.1 设计目标

好的表设计通常同时满足：

  1. 准确性：字段类型和约束能阻止非法数据；
  2. 可扩展：能承受可预见的业务变化；
  3. 简洁：没有重复和含义混乱的字段；
  4. 可查询：高频条件能建立有效索引；
  5. 可运维：字段注释清楚，变更风险可控；
  6. 低成本：存储、内存和网络开销合理。

表设计不是为了展示完美理论，而是为了让未来三到五年的业务和排障更轻松。

4.2 数值类型

类型 存储范围 适合场景
TINYINT 很小整数 状态、类型、布尔语义
SMALLINT 小整数 地区码、分类编号
INT 常规整数 普通数量、枚举值
BIGINT 大整数 ID、时间戳、金额分
DECIMAL(M,D) 精确小数 金额、费率
FLOAT / DOUBLE 近似浮点 科学计算，不适合金额

整数可选 UNSIGNED，表示无符号，范围从 0 开始：

stock INT UNSIGNED NOT NULL DEFAULT 0

它可以在语义上禁止负库存，但业务仍必须检查扣减数量，避免 stock - quantity 变成负数后报错或中断流程。

4.2.1 金额设计

方案一：DECIMAL：

total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00

方案二：整数分：

total_amount_cent BIGINT NOT NULL DEFAULT 0

两种方案都可以，关键是全链路统一。不要在数据库用元、缓存用分、日志用元、下游再用浮点数。

错误示例：

total_amount FLOAT NOT NULL

浮点数存在精度误差，不适合订单、支付、结算等强一致性场景。

4.3 字符串类型

类型 特点 适合场景
CHAR(N) 定长，会填充空格 固定长度编码，如国家码、币种
VARCHAR(N) 变长，按实际长度存储 名称、描述、号码
TEXT 大文本 长内容，但应避免高频查询
BLOB 二进制 通常不建议直接存 MySQL
ENUM 枚举字符串 修改值需要 DDL，谨慎使用

VARCHAR(N) 的 N 是字符数，不是字节数。utf8mb4 下一个中文或 Emoji 都可能占多个字节。

推荐：

order_no VARCHAR(32) NOT NULL
currency CHAR(3) NOT NULL DEFAULT 'CNY'
remark VARCHAR(512) NULL

不推荐把所有字段都设计成 VARCHAR(255) 或 TEXT。长度约束也是业务约束，能提前暴露错误。

4.3.1 手机号和身份证

手机号可能携带国家码，也可能被未来加密，建议：

mobile VARCHAR(20) NOT NULL

身份证应使用字符串而不是整数，因为它可能包含 X，且有前导零风险。

4.4 时间类型

类型 特点 建议
DATETIME 不带时区，保存字面时间 常规业务时间
TIMESTAMP 存 UTC，显示受会话时区影响 需要时区转换的场景
DATE 日期 生日、账期
TIME 时间 时长或时刻
YEAR 年份 年度维度

带毫秒：

created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)

推荐实践：

  1. 每张业务表都有 created_at；
  2. 需要追踪修改的表加 updated_at；
  3. 应用和数据库统一时区；
  4. 跨时区系统明确存储口径；
  5. 时间条件使用半开区间。

查询 8 月订单：

SELECT COUNT(*)
FROM orders
WHERE created_at >= '2026-08-01 00:00:00.000'
  AND created_at < '2026-09-01 00:00:00.000';

半开区间不会遗漏最后一毫秒，也更容易组合时间分片。

4.5 JSON 类型

MySQL 8.0 对 JSON 支持较好，适合保存结构可变的扩展信息：

ALTER TABLE orders
  ADD COLUMN extra JSON NULL;

写入：

UPDATE orders
SET extra = JSON_OBJECT(
  'channel', 'APP',
  'activity_id', 1001,
  'tags', JSON_ARRAY('new-user', 'free-shipping')
)
WHERE id = 1;

查询：

SELECT order_no, JSON_UNQUOTE(JSON_EXTRACT(extra, '$.channel')) AS channel
FROM orders
WHERE JSON_EXTRACT(extra, '$.activity_id') = 1001;

生成列加索引：

ALTER TABLE orders
  ADD COLUMN channel VARCHAR(32)
    GENERATED ALWAYS AS (JSON_UNQUOTE(extra->>'$.channel')) STORED,
  ADD INDEX idx_extra_channel (channel);

JSON 使用原则：

  1. 不把需要强约束的核心字段塞进 JSON；
  2. 不用 JSON 替代所有表设计；
  3. 高频查询字段提取为生成列；
  4. JSON 字段可能变大，影响内存和备份成本；
  5. 敏感字段加密后再入 JSON。

4.6 主键设计

InnoDB 是聚簇索引组织表，主键选择直接影响页分裂、二级索引大小和复制风险。

推荐：

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)

优点：

  1. 趋势递增，写入友好；
  2. 占用空间比随机 UUID 小；
  3. 二级索引叶子节点保存主键，主键越小二级索引越轻；
  4. 便于分页和范围定位。

不推荐随机字符串主键：

id CHAR(36) NOT NULL,
PRIMARY KEY (id)

如果业务必须暴露随机 ID，可以分开设计：

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_public_id (public_id)

分布式系统可以用号段服务、雪花 ID 或数据库序列生成趋势递增 ID。无论选择哪种，都要处理时钟回拨、机房位、中心依赖和 ID 泄露问题。

4.7 约束设计

常见约束：

约束 作用
PRIMARY KEY 唯一标识行
NOT NULL 禁止空值
UNIQUE KEY 业务唯一性
FOREIGN KEY 引用完整性
CHECK 条件检查
DEFAULT 默认值

示例：

CREATE TABLE accounts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    balance DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    status TINYINT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_user (user_id),
    CONSTRAINT chk_balance CHECK (balance >= 0),
    CONSTRAINT chk_status CHECK (status IN (1, 2))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL 8.0.16 之前 CHECK 会被解析但忽略，8.0.16 起生效。

4.7.1 是否使用外键

互联网高并发系统通常不使用物理外键，原因：

  1. 每次写入都要检查父表；
  2. 可能引入额外锁和级联风险；
  3. 分库分表后难以使用；
  4. 大规模数据维护成本高。

替代方案：

  1. 应用层校验；
  2. 定时对账；
  3. 唯一约束保留；
  4. 删除使用状态标记；
  5. 引用关系通过任务修复。

小规模内部系统可以使用外键，它能减少脏数据。

4.8 范式与反范式

第三范式可以简单理解为：字段尽量只依赖主键，不要保存可以由其他表推导出的冗余信息。

范式化示例：

orders 只保存 user_id
users 保存 username

查询时 JOIN：

SELECT o.order_no, u.username
FROM orders o
JOIN users u ON u.id = o.user_id;

反范式化示例：

order_items 保存 product_name 快照

这不是冗余错误。商品后来改名、改价，不应改变历史订单成交内容。

何时可以冗余：

  1. 查询压力明显高于写入压力；
  2. 冗余字段变化频率低；
  3. 不一致带来的业务风险可控；
  4. 能显著减少 JOIN；
  5. 有明确的数据修复机制。

何时不该冗余：

  1. 账户余额等强一致字段；
  2. 变化频繁且来源复杂的字段；
  3. 没有对账机制的统计值；
  4. 只是为了少写一个 JOIN。

4.9 状态字段设计

常见错误是 status 使用随意字符串，程序里同时出现 paid、PAID、1、已支付。

推荐：

status TINYINT UNSIGNED NOT NULL DEFAULT 10 COMMENT '10待支付，20已支付，30已取消'

或使用小整数加字典表：

CREATE TABLE order_status_dict (
    status_code TINYINT UNSIGNED NOT NULL,
    status_name VARCHAR(32) NOT NULL,
    PRIMARY KEY (status_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

状态机应收敛：

CREATED -> PAID -> SHIPPED -> FINISHED
CREATED -> CANCELED
PAID -> REFUNDING -> REFUNDED

非法流转必须拒绝，而不是靠后台脚本修数据。

4.10 通用字段

常见通用字段：

字段 类型建议 说明
id BIGINT UNSIGNED 主键
created_at DATETIME(3) 创建时间
updated_at DATETIME(3) 更新时间
created_by BIGINT UNSIGNED 创建人
updated_by BIGINT UNSIGNED 更新人
deleted TINYINT UNSIGNED 软删标记
version INT UNSIGNED 乐观锁版本
tenant_id BIGINT UNSIGNED 多租户隔离

软删除示例：

deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0未删，1已删'

软删除不是万能方案。它会让唯一键和查询条件复杂化，例如：

UNIQUE KEY uk_username (username, deleted)

如果同一个用户名可以多次删除，这个唯一键仍不够。可以考虑：

  1. 删除时把用户名迁入历史表；
  2. 唯一键包含 delete_version；
  3. 使用不可复用的唯一随机值；
  4. 核心表禁止物理删除，只做状态归档。

4.11 索引初步设计

表设计时先识别查询路径：

按用户查最近订单：user_id + created_at
按商户查状态订单：merchant_id + status + created_at
按订单号查详情：order_no 唯一

对应索引：

PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_created (user_id, created_at),
KEY idx_merchant_status_created (merchant_id, status, created_at)

索引不是越多越好。每个二级索引都会：

  1. 占用磁盘空间；
  2. 拖慢写入；
  3. 增加 Buffer Pool 竞争；
  4. 增加优化器选择成本；
  5. 加大大事务和 DDL 风险。

4.12 表设计评审清单

上线前逐项检查：

检查项 问题
命名 库、表、字段、索引是否统一且有注释
主键 是否显式、趋势递增、类型合理
类型 金额、时间、状态、字符串是否正确
约束 非空、默认值、唯一键是否完整
查询 高频条件是否有索引
容量 一年后行数、字段大小、总容量
生命周期 是否有归档和清理策略
权限 是否需要新账号和敏感字段权限
变更 是否支持安全 DDL 和回滚
对账 派生数据如何校验

本章小结

数据类型和约束是第一道数据质量防线。金额用 DECIMAL 或整数分，时间统一口径，主键保持趋势递增，状态字段收敛为小整数。JSON 适合扩展信息，不适合替代核心关系字段。范式保证清晰，反范式用于有明确收益和修复机制的场景。设计表时应从查询路径和容量出发，而不是只看当前页面需要什么字段。

思考题

  1. 为什么 FLOAT 不适合保存订单金额？
  2. 随机 UUID 作为 InnoDB 主键有什么代价？如何折中？
  3. DATETIME 和 TIMESTAMP 有什么区别？
  4. 高并发系统为什么常不用物理外键？如何补偿一致性风险？
  5. 给订单表增加一个渠道字段，你会如何评估类型、约束、索引和兼容性？