第 59 章：数据库设计规范
学习目标
- 掌握三大范式与反范式的取舍
- 学会阿里规约中的字段命名和类型选择
- 设计出可扩展、可维护的表结构
一、为什么需要设计规范？
现实教训：
- 表名
user_info、UserInfo、t_user、sys_users混用 → 半年后没人记得哪个表是干啥的- 字段用
varchar(255)一把梭 → 大字段拖慢索引、占满磁盘- 状态用
int但没有任何注释 → "0 到底是禁用还是未审核？"- 没有
create_time/update_time→ 出问题无法追溯好的设计 = 半年的代码，差的设计 = 半年的债务。
二、命名规范（阿里规约）
表名
sql
-- ✅ 推荐
sys_user -- 系统模块_业务
sys_role
sys_menu
order_info
order_item
-- ❌ 反例
User -- 大写
t_user -- t 前缀（MySQL 没必要）
sys_users_info -- 复数 + 多余词规约：
- 表名小写，下划线分隔
- 模块名前缀（
sys_、order_、pay_） - 单数形式（
user不是users） - 不要带数据库名前缀（
tf_user）
字段名
sql
-- ✅ 推荐
user_id, user_name, phone, email, status
create_time, update_time, create_by, update_by
is_deleted, version
-- ❌ 反例
UserID -- 驼峰（数据库世界用下划线）
userName -- 驼峰
user_id_col -- 多余后缀
flag -- 含义不明必备字段（每张表都应该有）
sql
CREATE TABLE sys_user (
id BIGINT NOT NULL AUTO_INCREMENT,
-- ... 业务字段 ...
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
COMMENT '创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
COMMENT '更新时间',
create_by BIGINT DEFAULT NULL
COMMENT '创建人 ID',
update_by BIGINT DEFAULT NULL
COMMENT '更新人 ID',
is_deleted TINYINT NOT NULL DEFAULT 0
COMMENT '是否删除（0=否，1=是）',
version INT NOT NULL DEFAULT 0
COMMENT '乐观锁版本号',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci COMMENT='用户表';这 6 个字段是"标配"。缺一个，半年后被同事问候。
三、字段类型选择
整数类型
sql
-- ✅ 精确选择
TINYINT -- -128 ~ 127，状态、类型、布尔
SMALLINT -- -32768 ~ 32767
INT -- 约 21 亿，主键、外键
BIGINT -- 约 922 亿，雪花 ID
-- ❌ 反例
INT status -- 浪费 3 字节
VARCHAR id -- 性能差、排序错乱字符串类型
sql
-- ✅ 按需选择长度
CHAR(2) -- 固定长度（如身份证区码）
CHAR(32) -- 固定 32（如 MD5）
VARCHAR(20) -- 用户名
VARCHAR(11) -- 手机号
VARCHAR(64) -- 邮箱
VARCHAR(255) -- 一律默认 255 是懒！
-- ❌ 反例
VARCHAR(2000) -- 巨型字段，性能灾难
TEXT username -- 用 TEXT 当 VARCHAR，索引都建不了VARCHAR(N) 中的 N 是字符数，不是字节数。
VARCHAR(255)在 utf8mb4 下最多占 1020 字节。
时间类型
sql
-- ✅ DATETIME vs TIMESTAMP
DATETIME -- 范围 1000-9999，不受时区影响 ← 推荐
TIMESTAMP -- 范围 1970-2038，自动转 UTC，受时区影响
-- ❌ 反例
VARCHAR time -- "2026-01-01 10:00:00"，排序性能差，无法用时间函数
BIGINT time -- 1640995200000，可读性为零
INT year -- 只存年份？用 YEAR 类型金额类型（最易出错）
sql
-- ❌ 用浮点数存金额
DOUBLE price -- 0.1 + 0.2 = 0.30000000000000004
FLOAT amount -- 精度丢失，分账对不平
-- ✅ 用 DECIMAL 或 BIGINT（分）
DECIMAL(10, 2) -- 10 位数字，小数点后 2 位，最大 99999999.99
BIGINT amount -- 单位是"分"（1元=100分），整型计算精确java
// Java 端映射
@Column(type = MySqlTypeCode.DECIMAL)
private BigDecimal price;
// 或者用分单位
private Long amountInCents; // 10001 表示 100.01 元布尔类型
sql
-- MySQL 没有真正的 BOOLEAN，TINYINT(1) 是约定
TINYINT is_deleted -- 0=假，1=真（推荐）
TINYINT(1) is_active -- 同样的效果
-- ❌ 反例
VARCHAR is_deleted -- 存 "true"/"false"？能 WHERE 吗？
INT is_deleted -- 浪费 3 字节四、三大范式与反范式
第一范式（1NF）：字段不可分
sql
-- ❌ 违反 1NF
address VARCHAR(500) -- 存 "浙江省杭州市西湖区xx路xx号"
-- ✅ 符合 1NF
province VARCHAR(20),
city VARCHAR(20),
district VARCHAR(20),
detail VARCHAR(200)第二范式（2NF）：非主键字段完全依赖主键
sql
-- ❌ 违反 2NF（订单明细里存了商品名）
CREATE TABLE order_item (
order_id BIGINT,
product_id BIGINT,
product_name VARCHAR(100), -- 依赖 product_id，不依赖整张表的主键
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- ✅ 符合 2NF
CREATE TABLE order_item (
order_id BIGINT,
product_id BIGINT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- product_name 在 product 表里 JOIN 出来第三范式（3NF）：非主键字段不能传递依赖
sql
-- ❌ 违反 3NF（用户表里存了部门名）
CREATE TABLE user (
id BIGINT PRIMARY KEY,
dept_id BIGINT,
dept_name VARCHAR(100) -- 通过 dept_id 间接依赖 id
);
-- ✅ 符合 3NF
CREATE TABLE user (
id BIGINT PRIMARY KEY,
dept_id BIGINT
);反范式：为了性能而妥协
sql
-- 反范式：在订单表冗余商品名称（避免 JOIN）
CREATE TABLE order_item (
id BIGINT PRIMARY KEY,
order_id BIGINT,
product_id BIGINT,
product_name VARCHAR(100), -- ① 冗余字段，每次下单快照
price DECIMAL(10,2), -- ② 冗余字段，记录下单时的价格
quantity INT
);
-- 商品改名/调价后，历史订单仍显示下单时的名字和价格 ← 关键！反范式的使用场景：
- 历史快照：订单、发票等不能跟随主数据变化的场景
- 读多写少：评论数、关注数，避免 JOIN 计数
- 大表 JOIN 性能差：把常用查询字段冗余进大表
五、索引设计规范
sql
-- ✅ 主键索引：每张表必须有
PRIMARY KEY (id)
-- ✅ 普通索引：WHERE、JOIN、ORDER BY 涉及的字段
KEY idx_user_status (user_id, status)
KEY idx_create_time (create_time)
-- ✅ 唯一索引：业务上唯一的字段
UNIQUE KEY uk_phone (phone)
UNIQUE KEY uk_tenant_username (tenant_id, username)
-- ❌ 反例
KEY idx_all (col1, col2, col3, col4, col5, col6) -- 联合索引别太长（最多 5-6 列）
KEY idx_text (description) -- 长文本字段别建索引最佳实践：
- 单表索引不超过 5-6 个
- 区分度低的字段别建索引（如
gender、枚举值） - 联合索引遵循最左前缀原则（等值在前，范围在后）
六、外键与约束
外键：用还是不用？
sql
-- ✅ 用外键（数据一致性要求高）
CREATE TABLE order_item (
order_id BIGINT NOT NULL,
FOREIGN KEY (order_id) REFERENCES `order`(id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);互联网项目通常不用外键：
- 每次 INSERT/UPDATE 都要检查外键，性能差
- 分库分表后外键失效
- 测试麻烦（要先准备父数据）
应用层保证一致性：删除前先查有没有被引用。
NOT NULL 与 DEFAULT
sql
-- ✅ 字段尽量 NOT NULL，给默认值
CREATE TABLE user (
username VARCHAR(50) NOT NULL,
status TINYINT NOT NULL DEFAULT 1,
age INT DEFAULT NULL,
balance DECIMAL(10,2) NOT NULL DEFAULT 0
);
-- ❌ 字段全是 NULL
CREATE TABLE user (
username VARCHAR(50), -- 不知道是空字符串还是没填
age INT -- NULL vs 0 含义模糊
);原因：
- NULL 值难以聚合（
COUNT(NULL) = 0） - NULL 影响索引效率
- NULL 让查询语义模糊
七、字符集与排序规则
sql
-- ✅ 推荐
CREATE TABLE user (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;| 项 | 推荐 | 说明 |
|---|---|---|
| 字符集 | utf8mb4 | 真 UTF-8，支持 emoji 和生僻字 |
| 排序规则 | utf8mb4_unicode_ci | 准确的多语言排序 |
| 存储引擎 | InnoDB | 事务、行锁、崩溃恢复 |
| 排序规则 | utf8mb4_bin | 精确比较（适合密码、ID） |
⚠️ MySQL 的
utf8不是真 UTF-8！utf8只支持 3 字节，emoji 存不进去。永远用utf8mb4。
八、表与字段注释
sql
CREATE TABLE sys_user (
id BIGINT NOT NULL AUTO_INCREMENT
COMMENT '用户 ID（雪花算法）',
username VARCHAR(50) NOT NULL
COMMENT '登录名，唯一',
nickname VARCHAR(50) DEFAULT ''
COMMENT '昵称',
phone VARCHAR(20) DEFAULT NULL
COMMENT '手机号',
email VARCHAR(100) DEFAULT NULL
COMMENT '邮箱',
status TINYINT NOT NULL DEFAULT 1
COMMENT '状态：0=禁用 1=正常 2=锁定',
gender TINYINT DEFAULT 0
COMMENT '性别：0=未知 1=男 2=女',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
COMMENT '创建时间',
PRIMARY KEY (id),
UNIQUE KEY uk_username (username),
KEY idx_phone (phone),
KEY idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
COMMENT='系统用户表';没有注释的字段就是定时炸弹：半年后没人知道
status = 2是什么意思。
九、垂直拆分与水平拆分
垂直拆分（按列）
sql
-- 把大字段、不常用字段拆到独立表
-- 主表
CREATE TABLE user (
id, username, password, status, create_time
);
-- 扩展表（1对1）
CREATE TABLE user_ext (
user_id, avatar, bio, address, last_login_ip
);适用场景：
- 大字段（TEXT、BLOB）拖慢主表查询
- 冷热数据分离（基本信息 vs 详细资料）
水平拆分（按行）
sql
-- 按时间分表
t_order_202601, t_order_202602, t_order_202603
-- 按用户 ID 哈希分表
t_user_0, t_user_1, ..., t_user_15适用场景：
- 单表超过 2000 万行
- 写操作成为瓶颈
80% 的项目永远不需要分表！先加索引、读写分离。详见第 57 章。
十、设计工具推荐
| 工具 | 用途 | 特点 |
|---|---|---|
| Navicat | 客户端 GUI | 商业、界面友好 |
| DBeaver | 客户端 GUI | 免费、跨平台 |
| MySQL Workbench | 客户端 GUI | 官方 |
| dbdiagram.io | 在线 ER 图 | 简洁、DSL 描述 |
| draw.io | 通用绘图 | 免费 |
| PowerDesigner | 建模工具 | 专业级、贵 |
| Chiner | 中文建模工具 | 免费、元数据管理 |
sql
-- dbdiagram.io 示例
Table users {
id bigint [pk]
username varchar(50) [not null, unique]
email varchar(100)
status tinyint [default: 1]
created_at datetime [default: `CURRENT_TIMESTAMP`]
}
Table posts {
id bigint [pk]
user_id bigint [ref: > users.id]
title varchar(200)
content text
}
Table comments {
id bigint [pk]
post_id bigint [ref: > posts.id]
user_id bigint [ref: > users.id]
content text
}十一、本章小结
| 要点 | 关键 |
|---|---|
| 表名 | 小写下划线，模块前缀，单数 |
| 必备字段 | id + 4 个审计字段 + 软删 + 版本号 |
| 整数 | 按范围选 TINYINT/SMALLINT/INT/BIGINT |
| 字符串 | 按实际长度，不要默认 255 |
| 时间 | DATETIME（不受时区影响） |
| 金额 | DECIMAL 或 BIGINT 分，不用浮点 |
| 布尔 | TINYINT(0/1) |
| 范式 | 1NF 不可分、2NF 完全依赖、3NF 无传递 |
| 反范式 | 历史快照、读多写少、大表 JOIN 性能差 |
| 外键 | 互联网项目通常不用，应用层保证 |
| 字符集 | 永远用 utf8mb4，不是 utf8 |
| 注释 | 表和字段都要写 COMMENT |
| 拆分 | 80% 项目不需要分表 |
动手练习
练习 1：基础题
为「博客系统」设计 4 张表：用户、文章、评论、分类。要求：
- 应用本文所有规约（命名、类型、注释、必备字段）
- 设计合理的索引
- 画 ER 图
练习 2：进阶题
改造一个已有的"烂表"（字段类型不合理、缺少注释、索引过多），应用本章规范重新设计。