表关系设计
为什么拆表
一张表只存一类东西。
users → 存用户信息
posts → 存文章
categories → 存分类
comments → 存评论
tags → 存标签
拆完之后，问题来了：这些表怎么关联？
答案是外键——在一张表里存另一张表的 id，再用约束保证关系正确。
常见关系只有三种：一对一、一对多、多对多。下面逐个讲清楚每种关系怎么建表、用什么约束、为什么这么用。
一、一对一（1:1）
A 有一条，B 也只有一条。
例子
用户的基本信息（账号密码）在 users 表，详细资料（生日、签名、头像）在 user_profiles 表。
users 表 user_profiles 表
┌──────────┐ ┌──────────────────┐
│ id │◄─────│ user_id (外键) │
│ username │ │ birthday │
│ password │ │ bio │
└──────────┘ │ avatar_url │
└──────────────────┘
建表 SQL
CREATE TABLE user_profiles (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
birthday DATE,
bio VARCHAR(500),
avatar_url VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (user_id), -- ① 保证一对一
FOREIGN KEY (user_id) REFERENCES users(id) -- ② 引用 users 表
);
用到了什么约束，为什么
① UNIQUE (user_id) —— 这是区分一对一和一对多的关键
没有 UNIQUE，同一个用户可以插入多条资料：
加了 UNIQUE，user_id = 10 只能出现一次，第二次插入直接报错。这才是一对一。
② FOREIGN KEY (user_id) REFERENCES users(id) —— 保证引用的用户存在
外键的作用：user_profiles.user_id 的值必须在 users.id 里真实存在。
-- 假设 users 表没有 id=999 的用户
INSERT INTO user_profiles (user_id, bio) VALUES (999, '测试');
-- ❌ 报错：foreign key constraint fails
不会出现"资料属于一个不存在的用户"这种情况。
什么时候拆，什么时候不拆
字段少、经常一起查 → 直接放 users 表，简单。
字段多、不常用、涉及隐私 → 拆出去做一对一。
二、一对多（1:N）
A 有很多个 B，但每个 B 只属于一个 A。
最常见的关系，几乎每个项目都有。
例子
一个用户写了很多篇文章
一个分类下有很多篇文章
一篇文章下有很多条评论
users 表 posts 表
┌──────────┐ ┌──────────────────┐
│ id │◄─────────│ user_id (外键) │
│ username │ │ category_id │
└──────────┘ │ title │
└──────────────────┘
categories 表 │
┌──────────┐ │
│ id │◄──────────────┘
│ name │
└──────────┘
建表 SQL
CREATE TABLE posts (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL, -- ① 必须有作者
category_id BIGINT UNSIGNED NULL, -- ② 可以没分类
title VARCHAR(255) NOT NULL,
content TEXT,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(id), -- ③ 引用 users
FOREIGN KEY (category_id) REFERENCES categories(id) -- ④ 引用 categories
);
用到了什么约束，为什么
① NOT NULL —— 关系必须存在
文章必须有作者，所以 user_id 是 NOT NULL。插入时不填这个字段直接报错。
② 允许 NULL —— 关系可选
文章可以暂时没有分类，所以 category_id 允许 NULL。
关系必须存在 → NOT NULL
关系可选 → NULL
③④ FOREIGN KEY —— 保证引用的数据存在
外键放在"多"的一方（posts 表），而不是在 users 表里存 '1,2,3' 这种字符串。
为什么？想想现实：一个老师带 100 个学生，你不会在老师档案里写 100 个学生名字，而是在每个学生的档案里写"我的老师是谁"。
博客里的一对多汇总
三、多对多（N:N）
A 有很多个 B，B 也有很多个 A。
例子
一篇文章可以有多个标签（"MySQL"、"后端"、"入门"）
一个标签可以属于多篇文章（"MySQL" 标签下有 50 篇文章）
为什么不能直接存
在 posts 表加个 tag_ids 字段存 '1,2,3'？
❌ 查询 "MySQL 标签有哪些文章" → 只能用 LIKE '%1%'，慢得要死
❌ 外键约束加不上
❌ 可能存成 '1,1,2' 这种重复值
❌ 删除某个标签后，还要去每个文章里改字符串
正确做法：中间表
把多对多拆成两个一对多：
posts 表 post_tags 中间表 tags 表
┌──────────┐ ┌──────────────┐ ┌──────────┐
│ id │◄─────│ post_id │ │ id │
│ title │ │ tag_id │───────►│ name │
└──────────┘ └──────────────┘ └──────────┘
中间表只存关系：
建表 SQL
CREATE TABLE post_tags (
post_id BIGINT UNSIGNED NOT NULL,
tag_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (post_id, tag_id), -- ① 联合主键防重复
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, -- ② 引用 posts
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE -- ③ 引用 tags
);
用到了什么约束，为什么
① PRIMARY KEY (post_id, tag_id) —— 联合主键
两个字段组合做主键，意味着同一个 post_id + tag_id 的组合只能出现一次：
(1, 1) → ✅ 文章1 绑定 MySQL
(1, 2) → ✅ 文章1 绑定 后端
(1, 1) → ❌ 重复了，文章1 已经绑定过 MySQL
单独的 post_id 可以重复（一篇文章可以有多个标签），单独的 tag_id 也可以重复（一个标签可以属于多篇文章），但组合不能重复。
②③ FOREIGN KEY ... ON DELETE CASCADE —— 级联删除
ON DELETE CASCADE 的意思是：删文章时，自动删掉这篇文章的所有标签绑定。
DELETE FROM posts WHERE id = 1;
-- post_tags 里 post_id=1 的记录自动被删除
不用手动去清理中间表，数据库帮你处理。
如果不想用联合主键，也可以加自增 id，但要额外加联合唯一约束：
CREATE TABLE post_tags (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
post_id BIGINT UNSIGNED NOT NULL,
tag_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (id),
UNIQUE (post_id, tag_id), -- 组合唯一，效果一样
FOREIGN KEY (post_id) REFERENCES posts(id),
FOREIGN KEY (tag_id) REFERENCES tags(id)
);
四、外键删除策略
父表数据删了，子表怎么办？
-- 用户有文章时不允许删除（默认行为）
FOREIGN KEY (user_id) REFERENCES users(id)
-- 删文章时，自动删掉文章的所有标签绑定
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
-- 删分类时，文章保留，category_id 变成 NULL
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
注意：用 SET NULL 的话，外键字段必须允许 NULL。
五、外键字段类型必须一致
外键创建失败最常见的原因：父表和子表字段类型不一致。
-- ✅ 两边都是 BIGINT UNSIGNED
users.id BIGINT UNSIGNED
posts.user_id BIGINT UNSIGNED
-- ❌ 一个 UNSIGNED 一个不是，外键建不起来
users.id BIGINT UNSIGNED
posts.user_id BIGINT
差一个 UNSIGNED 也不行。
六、物理外键 vs 逻辑外键
实际项目大多用逻辑外键，靠后端代码保证关系正确。
学习阶段建议先用物理外键，能帮你理解表关系。
七、总结
一对一 → 外键放扩展表 + UNIQUE（没有 UNIQUE 就变成一对多）
一对多 → 外键放"多"的一方 + NOT NULL/NULL（决定关系是否必须）
多对多 → 中间表 + 联合主键或联合唯一（防止重复绑定）
外键类型必须和父表主键一致
外键阻止插入不存在的引用，也阻止删除被引用的父表数据
ON DELETE CASCADE 让数据库自动清理关联数据
