上一篇我们已经设计了博客系统最基础的三张表：
user
avatar
post
同时认识了：
PRIMARY KEY
UNIQUE
INDEX
FOREIGN KEY
一对多
但真正让数据库设计开始有意思的，是下面这些业务：
用户点赞文章
用户收藏文章
用户评论文章
文章拥有多个标签
用户上传文章图片
这些功能会引出几个非常重要的数据库设计模式：
多对多
中间表
联合主键
联合索引
自关联
级联删除
SET NULL
这一篇，我们把它们全部串起来。
一、从“点赞”开始
需求非常简单：
用户可以点赞文章。
先观察业务关系。
一个用户可以点赞：
文章 A
文章 B
文章 C
而一篇文章也可能被：
用户 1
用户 2
用户 3
点赞。
所以：
用户 → 多篇文章
文章 → 多个用户
这就叫：
多对多关系。
二、多对多为什么需要中间表？
假设我们试图直接在 user 表里记录：
likedPostIds
可能变成：
1,5,8,10,20
这种设计会让查询、约束、更新都非常麻烦。
反过来，在 post 表记录：
likedUserIds
也有同样的问题。
关系型数据库中，多对多关系通常通过：
中间表
解决。
于是创建：
user_like_post
三、点赞表实际上是一张“关系表”
例如：
user_like_post
userId postId
1 100
1 200
2 100
它表达的不是三个“点赞对象”。
而是三条关系：
用户 1 点赞了文章 100
用户 1 点赞了文章 200
用户 2 点赞了文章 100
所以这种中间表通常不一定需要额外的：
id
因为：
userId + postId
本身就足以唯一确定一次点赞关系。
四、点赞表怎么建？
可以这样设计：
CREATE TABLE `user_like_post` (
`userId` INT NOT NULL,
`postId` INT NOT NULL,
PRIMARY KEY (`userId`, `postId`),
KEY `idx_postId` (`postId`),
CONSTRAINT `fk_like_user`
FOREIGN KEY (`userId`)
REFERENCES `user` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_like_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
五、为什么主键是两个字段？
这里出现：
PRIMARY KEY (`userId`, `postId`)
这叫：
联合主键。
不是一个字段唯一，而是：
userId + postId
这个组合唯一。
例如：
userId postId
1 100
1 200
2 100
合法。
但是：
1 100
1 100
不合法。
这恰好对应一个业务规则：
同一个用户不能重复点赞同一篇文章。
所以数据库结构本身就帮我们保证了业务正确性。
这就是一个很典型的设计思路：
能让数据库约束的规则，就不要完全依赖业务代码自觉维护。
六、联合主键同时也是联合索引
在 MySQL 中：
PRIMARY KEY (`userId`, `postId`)
不仅是唯一约束，也会建立索引。
这个索引的顺序可以理解为：
(userId, postId)
因此它很适合这样的查询：
SELECT *
FROM user_like_post
WHERE userId = 1;
意思是：
用户 1 点赞过哪些文章？
也适合：
SELECT *
FROM user_like_post
WHERE userId = 1
AND postId = 100;
意思是：
用户 1 是否点赞过文章 100？
七、为什么不需要再给 userId 单独建索引？
因为已经存在：
(userId, postId)
这个联合索引。
它是以：
userId
开头的。
因此：
WHERE userId = ?
已经可以利用它。
如果再创建：
KEY `userId` (`userId`)
很多情况下就是重复索引。
重复索引会：
占用更多磁盘空间
插入时多维护一份索引
更新时多维护一份索引
删除时也要维护
所以索引不是越多越好。
八、为什么 postId 又需要单独建索引？
考虑另一个查询：
SELECT *
FROM user_like_post
WHERE postId = 100;
意思：
文章 100 被哪些用户点赞？
虽然我们有：
(userId, postId)
但它首先按照：
userId
组织。
如果只根据：
postId
查询，并不能很好地利用这个联合索引的前缀。
所以我们建立：
KEY `idx_postId` (`postId`)
这其实就是理解联合索引非常好的例子。
可以先记住：
联合索引
(A, B)通常适合从 A 开始的查询，但不能简单认为它等价于同时拥有A和B两个独立索引。
这和常说的：
最左前缀原则
有关。
九、索引不是“字段重要就建”
通过点赞表，你应该开始建立真正的索引思维。
不是：
userId 很重要
→ 建索引
postId 很重要
→ 建索引
而应该是：
业务经常怎么查询？
已有索引能不能覆盖？
是否真的需要额外索引？
例如：
PRIMARY KEY(userId, postId)
已经支持：
WHERE userId = ?
WHERE userId = ? AND postId = ?
还经常需要：
WHERE postId = ?
所以补：
INDEX(postId)
这才是真正的索引设计。
十、收藏表其实已经会了
现在新增：
用户收藏文章。
关系完全一样：
用户
多对多
文章
所以又是一张中间表：
user_collect_post
例如：
CREATE TABLE `user_collect_post` (
`userId` INT NOT NULL,
`postId` INT NOT NULL,
PRIMARY KEY (`userId`, `postId`),
KEY `idx_postId` (`postId`),
CONSTRAINT `fk_collect_user`
FOREIGN KEY (`userId`)
REFERENCES `user` (`id`)
ON DELETE CASCADE,
CONSTRAINT `fk_collect_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
你会发现：
点赞
收藏
数据库结构极其相似。
以后遇到：
学生选择课程
用户加入群聊
用户关注话题
演员出演电影
用户参加活动
都应该对：
多对多 + 中间表
产生敏感度。
十一、接下来设计评论表
评论比点赞复杂一些。
一条评论至少需要：
id
content
postId
userId
也就是：
谁
在什么文章下
评论了什么
例如：
id 1
content 写得不错
postId 100
userId 7
翻译成人话：
用户 7 在文章 100 下发表了“写得不错”。
十二、评论为什么还需要 parentId？
真实博客通常支持：
评论
└── 回复
└── 再回复
例如：
评论 1：
这篇文章写得不错
评论 2：
谢谢！
评论 3：
确实讲得很清楚
如果评论 2 是回复评论 1，可以保存：
comment
id content parentId
1 这篇文章写得不错 NULL
2 谢谢 1
parentId = 1 的意思就是：
我的父评论是评论 1。
十三、一张表居然可以关联自己
这里：
comment.parentId
指向：
comment.id
所以外键是：
FOREIGN KEY (`parentId`)
REFERENCES `comment` (`id`)
这叫：
自关联。
并不是外键一定要指向另一张表。
一张表也完全可以指向自己。
这种设计经常用于：
评论回复
菜单树
部门层级
分类树
文件夹结构
十四、comment 表完整设计
例如：
CREATE TABLE `comment` (
`id` INT NOT NULL AUTO_INCREMENT,
`content` LONGTEXT NOT NULL,
`postId` INT NOT NULL,
`userId` INT NOT NULL,
`parentId` INT DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_postId` (`postId`),
KEY `idx_userId` (`userId`),
KEY `idx_parentId` (`parentId`),
CONSTRAINT `fk_comment_user`
FOREIGN KEY (`userId`)
REFERENCES `user` (`id`),
CONSTRAINT `fk_comment_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_comment_parent`
FOREIGN KEY (`parentId`)
REFERENCES `comment` (`id`)
ON DELETE SET NULL
ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
十五、ON DELETE CASCADE 是什么意思？
注意文章外键：
ON DELETE CASCADE
假设：
文章 100
下面有：
100 个点赞
30 个收藏
50 条评论
现在文章 100 被删除。
这些关系已经没有意义：
user_like_post.postId = 100
comment.postId = 100
于是：
ON DELETE CASCADE
表示：
父记录删除以后，依赖它的数据跟着删除。
例如：
删除 post 100
自动删除
→ 它的点赞关系
→ 它的收藏关系
→ 它的评论
这就是：
级联删除。
十六、ON DELETE SET NULL 又是什么意思？
但是评论回复关系有一点不同。
例如：
评论 1
└── 评论 2
如果评论 1 被删除：
要不要顺便把评论 2 也删了？
这取决于业务。
如果我们希望：
评论 2 仍然保留，只是原来的父评论已经不存在。
就可以：
ON DELETE SET NULL
于是删除评论 1 以后：
评论 2.parentId
从：
1
变成：
NULL
所以可以简单理解：
CASCADE
跟着删
SET NULL
数据保留，但解除关系
十七、Tag 为什么又需要中间表？
文章还可能有标签：
JavaScript
React
MySQL
数据库
后端
一篇文章可能有很多标签：
文章 A
→ JavaScript
→ React
→ 前端
一个标签也可能属于很多文章：
JavaScript
→ 文章 A
→ 文章 B
→ 文章 C
所以：
post
多对多
tag
又是一组多对多关系。
因此：
tag
post_tag
两张表出现了。
十八、tag 表
CREATE TABLE `tag` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
为什么：
name
需要 UNIQUE？
因为通常不会希望：
JavaScript
JavaScript
JavaScript
在 tag 表中出现三次。
十九、post_tag 表
CREATE TABLE `post_tag` (
`postId` INT NOT NULL,
`tagId` INT NOT NULL,
PRIMARY KEY (`postId`, `tagId`),
KEY `idx_tagId` (`tagId`),
CONSTRAINT `fk_post_tag_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_post_tag_tag`
FOREIGN KEY (`tagId`)
REFERENCES `tag` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
是不是很眼熟？
它和：
user_like_post
几乎是一个模式。
这也是学习数据库设计非常重要的一件事：
不要只背具体表，要发现背后的通用结构。
二十、最后还有文件表
博客文章中通常会上传：
封面
文章图片
附件
真实文件一般不会全部塞进 MySQL。
更常见的结构：
文件本体
→ OSS / S3 / 文件服务器
文件信息
→ MySQL
所以数据库可以保存：
originalname
mimetype
filename
size
width
height
metadata
userId
postId
二十一、设计 file 表
例如：
CREATE TABLE `file` (
`id` INT NOT NULL AUTO_INCREMENT,
`originalname` VARCHAR(255) NOT NULL,
`mimetype` VARCHAR(255) NOT NULL,
`filename` VARCHAR(255) NOT NULL,
`size` INT NOT NULL,
`postId` INT DEFAULT NULL,
`userId` INT NOT NULL,
`width` SMALLINT DEFAULT NULL,
`height` SMALLINT DEFAULT NULL,
`metadata` JSON DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_postId` (`postId`),
KEY `idx_userId` (`userId`),
CONSTRAINT `fk_file_user`
FOREIGN KEY (`userId`)
REFERENCES `user` (`id`),
CONSTRAINT `fk_file_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE SET NULL
ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
其中：
userId
表示：
谁上传了这个文件？
而：
postId
表示：
这个文件当前属于哪篇文章？
如果文章还没发布：
postId = NULL
也是合理的。
二十二、整个数据库关系终于出现了
现在可以把博客数据库理解为：
user
│
├── avatar
│
├── post
│ │
│ ├── comment
│ ├── file
│ ├── user_like_post
│ ├── user_collect_post
│ └── post_tag
│
└── comment
tag
└── post_tag
再按照关系分类：
user → post
一对多
user → comment
一对多
post → comment
一对多
user ↔ post
多对多
通过 user_like_post
user ↔ post
多对多
通过 user_collect_post
post ↔ tag
多对多
通过 post_tag
comment → comment
自关联
通过 parentId
这张关系图比背所有 SQL 更重要。
二十三、项目里为什么会有 database/blog.sql？
完成表设计以后，一个非常常见的项目结构是：
project
│
├── src
├── ...
└── database
└── blog.sql
blog.sql 可以保存：
CREATE TABLE
外键
索引
初始化数据
这样新环境拿到项目以后，就可以通过 SQL 文件快速初始化数据库结构。
还可以准备一些测试数据，例如：
几个用户
几篇文章
几个标签
一些评论
一些点赞
方便开发接口时直接使用。
二十四、这一篇最应该记住的不是 SQL
真正应该形成的是下面这些模式。
模式 1：一对多
一个用户
很多文章
通常：
post.userId
模式 2：多对多
用户
很多文章
文章
很多用户
中间表：
user_like_post
模式 3：联合主键
(userId, postId)
表达：
这组关系不能重复。
模式 4：自关联
comment.parentId
→ comment.id
表达树状结构。
模式 5：外键删除策略
CASCADE
父记录删掉，子记录跟着删
SET NULL
父记录删掉，子记录留下，关系清空
模式 6：查询决定索引
不要看到字段就无脑加索引。
先问：
最常见的 WHERE 是什么？
最常见的 JOIN 是什么？
已有联合索引能不能利用？
总结
数据库设计并不是：
为每个业务随便创建一张表。
真正的过程应该是：
分析实体
↓
分析实体关系
↓
决定一对多还是多对多
↓
设计主键
↓
设计外键
↓
把业务规则变成约束
↓
根据查询需求设计索引
当你理解这一套以后，再看到：
点赞
收藏
关注
标签
评论
回复
就不会觉得它们是六个完全不同的问题。
你会发现，它们实际上只是几个数据库设计模式在不断重复。
下一篇，我们把视角从数据库拉高：
一个真正上线的网站，从用户输入
juejin.cn开始，请求究竟怎么经过 DNS、Nginx、服务器集群、OSS 和 CDN？
