表设计与主键外键 — CodeRoadMap表设计与主键外键
这一课你将能做到
- 从一句业务需求推导出表清单和主外键关系，判断一对多、多对多该建什么表
- 为字段选对数据类型（金额用 DECIMAL、主键用自增 BIGINT、时间用 DATETIME），并亲手触发一次外键约束报错
- 说清三范式的人话版、外键的优缺点、什么场景会放弃外键改用应用层保证
前置：第一课建库脚本已跑。本课在 sql_practice 里做小实验，结束前给一段复原提示。
为什么要学
前面 6 课一直在「用」表，这一课回答「为什么表长这样」。表结构是最难返工的东西：接口写错改代码就行，表设计错了要迁移数据、改十处查询。而且面试必考——三范式、为什么自增主键、外键要不要用，全是后端面试的固定曲目。学完这一课，你再回头看第一课那 5 张表，每一列都应该能说出「为什么在这」。
先建立图像
设计表就是回答三个问题：
① 有哪几类东西？ → 每类一张表（学生、课程、课时…）
② 一对多？多对多？ → 一对多：外键放「多」的那方
多对多：拆中间表
学生 ↔ 课时 是多对多 → study_record 就是中间表
③ 每张表靠什么认行？ → 主键；靠什么关联？ → 外键
student 1 ──< study_record >── 1 lesson >── 1 course
（中间表：学生×课时的关系，还捎带 completed/score）
第一课的 5 张表就是按这个思路长出来的。下面动手验证它为什么站得住。
跟着做
步骤 1：从需求推表（纸面练习）
需求一句话：「学生注册后能浏览课程，付费购课，逐节学习并记录完成情况和成绩。」
推导过程：
- 找名词实体：学生、课程、课时 → student / course / lesson
- 找关系：学生与课时是「学习关系」（多对多，还带完成状态、分数）→ 中间表 study_record；学生与课程是「购买关系」（多对多，带金额状态）→ 中间表 pay_order
- 找约束：邮箱唯一 → UNIQUE；课程没发布不该被看到 → status 列；钱要精确 → DECIMAL
反过来想一个反例：如果把学生姓名直接抄进 study_record（「省得 JOIN」），学生改名就要 UPDATE 几万行记录，还会出现同一个人两个名字的历史脏数据。冗余一次，维护一生——这就是范式存在的理由。
步骤 2：数据类型怎么选（对照表）
| 数据 | 选型 | 为什么不选别的 |
|---|
| 主键、外键 id | BIGINT AUTO_INCREMENT | INT 上限 21 亿，业务表直接 BIGINT 一步到位 |
| 金额 | DECIMAL(10,2) | FLOAT/DOUBLE 是二进制浮点，0.1+0.2≠0.3，钱绝不能用 |
| 短字符串 | VARCHAR(32/64/128) | 长度按真实上限定，别一律 255 |
| 长文本 | TEXT / MEDIUMTEXT | 本站 content_md 用 MEDIUMTEXT，能存 16MB |
| 时间 | DATETIME | 8 字节、无时区转换；TIMESTAMP 只有 4 字节且受 2038 限制 |
| 布尔/状态 | TINYINT(1) 或短 VARCHAR+注释 | 用注释写清枚举值，如 -- draft/published |
动手验证金额精度问题：
SELECT 0.1 + 0.2; -- DECIMAL 语境：0.3（精确）
SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE); -- 0.30000000000000004（浮点误差）
步骤 3：主键怎么选
-- 反例：用邮箱当主键（业务键）
-- 邮箱能改；换邮箱 = 主键值变了，所有引用它的子表全要改
-- 字符串主键索引又长又随机，B+ 树插入频繁页分裂
-- 正解：无业务含义的自增 BIGINT 主键 + 业务列加 UNIQUE
CREATE TABLE student (
id BIGINT PRIMARY KEY AUTO_INCREMENT, -- 内部认行
email VARCHAR(128) NOT NULL UNIQUE, -- 对外约束
slug VARCHAR(64) NOT NULL UNIQUE -- 对外路由（本站 course 用 slug 做 URL）
);
本站 cf_track 就是这个模式：库里用自增 id 关联，URL 里用 slug，各司其职。分布式系统会用雪花 ID 之类全局有序 id，思路相同：主键无业务含义、尽量递增、尽量短。
步骤 4：外键约束——亲手触发一次报错
INSERT INTO study_record (student_id, lesson_id, completed)
VALUES (999, 6, 0);
-- ERROR 1452: Cannot add or update a child row:
-- a foreign key constraint fails（student 表里没有 id=999）
这就是外键的价值：不存在的引用根本写不进去，脏数据在建表层就被拦住。再试违反唯一键：
INSERT INTO study_record (student_id, lesson_id, completed)
VALUES (1, 6, 1);
-- ERROR 1062: Duplicate entry '1-6'（uk_student_lesson 唯一键，重复报名被拦）
步骤 5：CASCADE 连锁与 SET NULL
第一课建表时 lesson.course_id 写的是 ON DELETE CASCADE——删课程自动删课时。反过来，本站 cf_submission.lesson_id 用的是 ON DELETE SET NULL：课时删了，提交记录保留，只是断开关联。两种策略怎么选：
-- 语义强绑定的子数据：跟着父表一起没（课时属于课程）
CONSTRAINT fk_lesson_course FOREIGN KEY (course_id)
REFERENCES course(id) ON DELETE CASCADE
-- 子数据有自己的独立价值：断开引用但保留（提交记录属于用户资产）
FOREIGN KEY (lesson_id) REFERENCES cf_lesson(id) ON DELETE SET NULL
（第 6 课步骤 5 已演示过 CASCADE 连锁，这里补上另一种策略的用途。）
步骤 6：ALTER 改表结构（DDL）
ALTER TABLE course ADD COLUMN tag VARCHAR(32) DEFAULT NULL; -- 加列
ALTER TABLE course MODIFY COLUMN tag VARCHAR(64) DEFAULT NULL; -- 改类型
ALTER TABLE course DROP COLUMN tag; -- 删列（谨慎！）
SHOW CREATE TABLE course\G -- 核对结构
MySQL 8 的加列多数情况是 INSTANT 算法，秒级完成不锁业务；改列类型、删列仍是重量级操作，生产要在低峰执行并评估锁表影响。
步骤 7：复原练习库
DROP DATABASE IF EXISTS sql_practice;
-- 然后回第一课重跑完整建库脚本
讲清楚要点
- 三范式的人话版：1NF 列不可再拆（别把「深圳市南山区」塞一个列）；2NF 非主键列要依赖整个主键（中间表的属性不能只依赖一半键）；3NF 非主键列之间别互相依赖（课时表存 course_id 就够了，别再冗余课程标题和分类名）。范式管的就一件事：一份事实只存一处。
- 范式不是教条：列表页高频 JOIN 的字段适度冗余（反范式）换性能是常见操作，比如订单表冗余下单时的商品标题快照。前提是你清楚冗余的更新代价。
- 主键三原则：无业务含义、递增（对 B+ 树友好，第 8 课展开）、短小（每个二级索引都暗藏一份主键）。业务标识（email/slug/手机号）用 UNIQUE 约束管，不当主键。
- 外键是双刃剑：优点——一致性硬保证、自文档、级联自动化；缺点——写入多一层校验、锁传播、分库分表/批量导入场景碍事。互联网大厂常在应用层保证一致性、表上不建外键；中小系统和内部系统放心用外键。本站 schema 用了外键 + CASCADE，属于后者的务实选择。
- NOT NULL + DEFAULT 优先：NULL 在比较、聚合、索引里全是坑（第 3、5 课都见过）。拿不准的列先 NOT NULL 再给默认值。
- 约束是最后一道防线：UNIQUE 挡重复、FK 挡野引用、NOT NULL 挡空值。应用层校验照做，数据库兜底不缺席——两层都有的系统才睡得着觉。
你可能会懵的地方
-
每张表都必须有自增 id 主键吗？
InnoDB 没显式主键会生成隐藏的行 id，但你看不见也用不了。显式 BIGINT AUTO_INCREMENT 主键是最省心的默认选择；例外是多对多中间表可以用「两个外键列做联合主键」，或按业务选联合主键。
-
什么时候不该用数据库外键？
大流量写入（外键校验+锁传播是开销）、分库分表（跨库没法外键）、需要极致的批量导入速度。这些场景把一致性检查放到应用层/对账任务。一般业务系统先用外键没错。
-
金额到底用 DECIMAL 几位？
DECIMAL(10,2) 够绝大多数业务（最大 99,999,999.99）；涉及汇率、利息的用 DECIMAL(19,6) 之类更高精度，展示层再四舍五入。Java 侧对应 BigDecimal，永远不要用 double 接金额。
-
VARCHAR(255) 是不是随便写？
255 不是魔法数。长度是上限不是预分配（InnoDB 按实际长度存），但过长的 VARCHAR 会影响排序缓冲、临时表内存结构。按真实业务上限写：手机号 20、邮箱 128、标题 128。
-
一对多和多对多到底怎么落表？
一对多：外键放「多」的一方（lesson.course_id）。多对多：拆中间表，两个外键 + 联合唯一键（study_record 的 uk_student_lesson 就是标准答案，还捎带了关系自身的属性 completed/score）。
错法对照
| 错法 | 问题 | 正解 |
|---|
| DOUBLE/FLOAT 存金额 | 精度丢失，对账对不上 | DECIMAL(10,2) |
| 手机号/邮箱当主键 | 可变、隐私、索引随机 | 自增 BIGINT 主键 + UNIQUE |
| 所有列一律 VARCHAR(255) | 排序/临时表浪费 | 按语义定长度和类型 |
| 一张大宽表塞所有字段 | 冗余、更新灾难 | 拆表 + JOIN |
| 子表外键列不建索引 | JOIN 与级联全表扫 | InnoDB 外键自动建；手动关联列记得建 |
| 列全允许 NULL | 比较/聚合处处是坑 | 默认 NOT NULL + DEFAULT |
卡点急救
- 错误 1452（外键失败）：先插父表再插子表；或值真的错了，先查父表有没有这行。
- 错误 1062（唯一键冲突）：撞了 UNIQUE。先
SELECT 看重复数据是脏数据还是业务正常重复。
- 错误 1054（Unknown column）：改过表名/列名后忘改代码；
SHOW CREATE TABLE 对准现状。
- ALTER 大表很慢：MySQL 8 加列多是 INSTANT；改类型/删列是重建表级操作，低峰执行，或用 gh-ost/pt-osc 在线改表工具。
- 关联查询报字符集/排序规则不一致：库、表、连接统一
utf8mb4 + 同一 collation（本站统一 utf8mb4_unicode_ci）。
面试常问
-
三范式是什么？什么时候反范式？
1NF 列原子性、2NF 消除部分依赖、3NF 消除传递依赖，本质是消除冗余保证一致。读多写少的报表、需要历史快照的订单等场景适度冗余换性能，但要明确冗余列的更新策略。
-
为什么推荐自增主键？
递增插入对 B+ 树聚簇索引最友好——总是追加到最右页，不产生随机 IO 和页分裂；短整型主键让所有二级索引更小。UUID 作主键的问题就在随机性与长度。
-
数据库外键的优缺点？要不要用？
优点：一致性硬约束、级联自动化、自文档。缺点：写放大、锁传播、迁移与分库分表不便。结论分场景：一般系统放心用，超大并发/分库分表在应用层保证并对账。
-
DATETIME 和 TIMESTAMP 怎么选？
DATETIME 8 字节、范围 1000~9999 年、存的是字面时间；TIMESTAMP 4 字节、2038 到顶、存 UTC 按时区转换。本站业务时间统一 DATETIME，跨时区产品才需要 TIMESTAMP 的自动转换。
自测三问
- 学生和课时是多对多关系，为什么中间表叫 study_record 而不是「学生课时关系表」就行？它比纯关系表多承载了什么？
- 为什么 email 既不做主键、又必须 UNIQUE？
ON DELETE CASCADE 和 ON DELETE SET NULL 分别适合什么关系？各举本站一例。
小结一句话
表设计 = 实体成表、一对多外键放多方、多对多拆中间表；主键无业务含义且自增，外键与唯一约束兜底一致性，范式管冗余、反范式换性能但要清醒。
延伸