重新学习SQL: 完成博客系统的 SQL递归CTE 评论查询设计

2063 字

10 分钟

重新学习SQL: 完成博客系统的 SQL递归CTE 评论查询设计

2026-04-15

笔记

postgresql

/

linux

一直想给自己的博客添加一个评论功能, 真正写起来才发现评论树的层级嵌套查询并不好做, 在业务层做需要发起多次数据库查询, 而在SQL中使用递归CTE可以很好的解决这一点, 本文主要注重于SQL语句的设计, 真得学了叭

参考

Bilibili视频: SQL语言之 CTE(Common Table Expression)公用表达式

实际需求#

实际评论是可以回复盖楼的, 这就意味着在评论内还有评论, 要实现一个评论树的结构

1
用户A: 文章不错! (#1)
2
├─ 贴主: 谢谢(#2)
3
│   ├─ 用户A: 不客气（#3）
4
│   │   └─ 用户B: 膜（#4）
5
│   └─ 用户C: 这文章怎么写的? (#5)
6
└─ 用户C: 你写得也不差(#6)
7
用户D: 膜膜(#7)

期望返回结构#

前端构建需要根据评论的深度层级(depth)来调整缩进, 最好有父级id(parent_id)让我知道是回复了谁, 通过评论路径(path)我就可以摘出整个评论回复链

id	user_id	parent_id	content	depth	path
1	A	NULL	文章不错!	1	1
2	贴主	1	谢谢	2	1 -> 2
3	A	2	不客气	3	1 -> 2 -> 3
4	B	3	膜	4	1 -> 2 -> 3 -> 4
5	C	2	这文章怎么写的?	3	1 -> 2 -> 5
6	C	1	你写得也不差	2	1 -> 6
7	D	NULL	膜膜	1	7

CTE入门#

为什么要使用CTE?#

来看下面这个例子, 查询所有换过部门的员工, 思路是在 job_history 部门更换记录表中获取员工编号, 然后通过编号筛选换过部门的员工。此处的(select distinct empno from job_history)就是一个子表达式, 如果这个子查询再复杂些, 或者需要在别的地方复用, 则需要再写一遍, 大大提高了代码的复杂度

1
select * from employees where empno in (select distinct empno from job_history);

什么是CTE?#

CTE全称Common Table Expression, 是一个命名的临时结果集,它存在于单个语句的范围内,并且可以在该语句中多次引用，而且CTE还可以相互引用。

并且CTE是在所有主流数据库中都通用的表达式(很多数据库都拥有自己的方言, 那这个是不是可以算作数据库中的普通话x)

CTE的基本语法如下：

1
WITH cte_name (column list) AS query
2

3
SELECT * FROM cte_name;

其中cte_name是cte临时表名, (column list)是cte表中的列名, query部分应该用( )包含整个查询语句,

让我们改造一下上文的SQL语句, 查询所有换过部门的员工, 使用CTE实现:

1
WITH emp_with_history AS (
2
    SELECT DISTINCT empno
3
    FROM job_history
4
)
5
SELECT *
6
FROM employees
7
WHERE empno IN (SELECT empno FROM emp_with_history);

你会发现原来的子查询被前置了, 先查询作为临时结果集, 后面的查询再从结果集中二次筛选. 虽然看起来比原式稍复杂, 但是有更好的扩展性, 你可以进行多个子查询, 全部都存成CTE, 然后在后文的查询中自由组合使用, 可读性更好.

CTE递归初识#

CTE像是一个函数, 所得到的结果可以反复复用, 甚至可以自己使用自己, 即为”函数递归”, CTE中的递归需要将WITH变成WITH RECURSIVE, 来看一个例子

1
WITH RECURSIVE cte_table(n) AS (
2
    SELECT 1
3

4
    UNION ALL
5

6
    SELECT n+1 FROM cte_table WHERE n < 10
7
)
8

9
SELECT * FROM cte_table;

输出是一个包含n列的表，有10行，值1到10

整个语句用UNION ALL分成两部分, 上半部分只执行一次, 我们暂且称它为锚点查询, 下半部分会对每个锚点数据反复执行, 直到WHERE中的条件不再符合, 最后将每个子查询和对应原始数据拼接在一起形成最终的结果表.

回到例子中, 上半部分SELECT 1直接与cte_table(n)中的n列对应, 代表直接将一个1放入n列, 下半部分对锚点数据1执行, 将n列的数据+1, 并和锚点数据1拼接在一起, 直到n列不小于10

1
| n   |
2
| --- |
3
| 1   |
4
| 2   |
5
| 3   |
6
| 4   |
7
| 5   |
8
| 6   |
9
| 7   |
10
| 8   |
11
| 9   |
12
| 10  |

更进一步? 递归CTE生成斐波那契数#

试一试生成斐波那契数的前10项

1
WITH RECURSIVE fib(n, fib_n, next_fib) AS (
2
    -- 初始值：第1项为0，第2项为1
3
    SELECT 1, 0, 1
4
    UNION ALL
5
    -- 递归：下一项 = 当前项 + 前一项
6
    SELECT n + 1, next_fib, fib_n + next_fib
7
    FROM fib
8
    WHERE n < 10   -- 生成前10项
9
)
10
SELECT n   AS 序号,
11
       fib_n AS 斐波那契数
12
FROM fib;

1
| 序号 | 斐波那契数 |
2
| ---- | ---------- |
3
| 1    | 0          |
4
| 2    | 1          |
5
| 3    | 1          |
6
| 4    | 2          |
7
| 5    | 3          |
8
| 6    | 5          |
9
| 7    | 8          |
10
| 8    | 13         |
11
| 9    | 21         |
12
| 10   | 34         |

好了, 你已经掌握了CTE的精髓—递归

简化版: 测试核心功能#

id	user_id	parent_id	content	time
1	123	NULL	文章不错!	2026-04-15T08:00
2	121	1	谢谢	2026-04-15T08:12

在数据库中, 评论数据是按照上述格式存储的

1
WITH RECURSIVE comment_tree(com_id, user_id, content, depth, path) AS (
2
    SELECT c.id, c.user_id, c.content, 1, c.id from comments AS c
3
    WHERE c.parent_id IS NULL
4

5
    UNION ALL
6
    SELECT c.id, c.user_id, c.content, ct.depth+1, concat(ct.path, ' -> ', c.id)
7
    FROM comments AS c
8
    JOIN comment_tree AS ct ON ct.com_id = c.parent_id
9
)
10
SELECT * FROM comment_tree
11
ORDER BY path;

该sql返回的内容和上文期望结果相同

在sql中, 使用from comments AS c给comments表起了一个别名c, 接下来需要访问cte表和评论表, 就用表名.列名来区分, 每次计算子节点时都ct.depth+1, 通过concat(ct.path, ' -> ', c.id)拼接出类似于1 -> 2 -> 3的回复链, 后续通过ORDER BY path;自动实现按照评论回复顺序排序

下半部分的JOIN comment_tree AS ct ON ct.com_id = c.parent_id其实就是连接条件, 等价于FROM comments c, comment_tree ct WHERE ct.com_id = c.parent_id, 但是JOIN更加可读

1
-- 使用 JOIN
2
SELECT ...
3
FROM comments c
4
JOIN comment_tree ct ON ct.com_id = c.parent_id
5

6
-- 使用 WHERE
7
SELECT ...
8
FROM comments c, comment_tree ct
9
WHERE ct.com_id = c.parent_id

实际生产环境设计#

在实际博客系统中, 不只是获取用户id, 还需要根据user_id, 在users表中查询对应的用户信息并加入评论返回中, 还需要支持置顶评论, 根评论按最新排序, 子评论按时间先后排序, 评论分页功能

并且简化版的sql中还存在一个漏洞, 直接使用path字符串进行排序, 当评论id达到两位数时, 按照符逐个比较排序会发生10排在2前面, 因为先比较的是"10"中的第一个字符"1"与"2"

数据库表示例#

comments 表#

id	parent_id	root_id	article	user_id	content	time	pinned
1	NULL	1	/abc	101	Root A (pinned)	2024-01-01 10:00:00	1
2	1	1	/abc	102	Child A1	2024-01-01 10:05:00	0
3	1	1	/abc	103	Child A2	2024-01-01 10:10:00	0
4	NULL	4	/abc	104	Root B	2024-01-02 09:00:00	0
5	4	4	/abc	101	Child B1	2024-01-02 09:15:00	0
6	2	1	/abc	105	Grandchild A1-1	2024-01-01 10:20:00	0

users 表#

id	username	avatar	home_page	role	email
101	alice	a.jpg	alice.com	user	a@x.com
102	bob	b.jpg	bob.com	user	b@x.com
103	charlie	c.jpg	charlie.com	user	c@x.com
104	dave	d.jpg	dave.com	user	d@x.com
105	eve	e.jpg	eve.com	user	e@x.com

查询SQL#

1
WITH RECURSIVE comment_tree AS (
2
  SELECT id, id AS com_id, article, user_id, content, browser, os, parent_id, root_id, time, pinned,
3
         1 AS depth,
4
         lpad(id::text, 10, '0') AS path,
5
         time AS root_time
6
  FROM comments
7
  WHERE {root_filter}
8

9
  UNION ALL
10

11
  SELECT c.id, c.id AS com_id, c.article, c.user_id, c.content, c.browser, c.os,
12
         c.parent_id, c.root_id, c.time, c.pinned,
13
         ct.depth + 1 AS depth,
14
         concat(ct.path, ' -> ', lpad(c.id::text, 10, '0')) AS path,
15
         ct.root_time
16
  FROM comments AS c
17
  JOIN comment_tree AS ct ON ct.com_id = c.parent_id
18
  WHERE c.article = :article
19
)
20
SELECT ct.id, ct.article, ct.user_id, ct.content, ct.browser, ct.os,
21
       ct.parent_id, ct.root_id, ct.time, ct.depth, ct.path, ct.pinned,
22
       u.username, u.avater, u.home_page, u.role, u.email
23
FROM comment_tree ct
24
LEFT JOIN users u ON ct.user_id = u.id
25
ORDER BY ct.pinned DESC, ct.root_time DESC, ct.path ASC
26
OFFSET :skip LIMIT :limit

该SQL相较于简化版有着以下明显改进

类别	简化版	最终版
根节点过滤	`WHERE c.parent_id IS NULL` （固定取所有根评论）	`WHERE {root_filter}`（占位符，可动态指定根节点，比如 `root_id = 某值` 或 `parent_id IS NULL AND article = :article）`
文章过滤	无	WHERE 子句中添加了 `c.article = :article` 确保子评论与根评论属于同一篇文章
路径构建	`concat(ct.path, ' -> ', c.id)` 路径如 1 -> 2 -> 3	用 `lpad(..., 10, '0')` 将 ID 补零成固定宽度（如 0000000001）保证按字符串排序时能按数字顺序排列（避免 10 排在 2 前面）
排序与分页	无	置顶评论（pinned）优先然后按根评论时间倒序同根下的评论按 path 字典序（即树的深度优先顺序）支持分页（OFFSET / LIMIT）

最终查询结果#

（{root_filter} = parent_id IS NULL AND article = :article，article=/abc，skip=0，limit=100，排序 pinned DESC, root_time DESC, path ASC）

id	article	user_id	content	parent_id	root_id	time	depth	path	pinned	username	avatar	home_page	role	email
1	/abc	101	Root A (pinned)	NULL	1	2024-01-01 10:00:00	1	0000000001	1	alice	a.jpg	alice.com	user	a@x.com
2	/abc	102	Child A1	1	1	2024-01-01 10:05:00	2	0000000001 -> 0000000002	0	bob	b.jpg	bob.com	user	b@x.com
6	/abc	105	Grandchild A1-1	2	1	2024-01-01 10:20:00	3	0000000001 -> 0000000002 -> 0000000006	0	eve	e.jpg	eve.com	user	e@x.com
3	/abc	103	Child A2	1	1	2024-01-01 10:10:00	2	0000000001 -> 0000000003	0	charlie	c.jpg	charlie.com	user	c@x.com
4	/abc	104	Root B	NULL	4	2024-01-02 09:00:00	1	0000000004	0	dave	d.jpg	dave.com	user	d@x.com
5	/abc	101	Child B1	4	4	2024-01-02 09:15:00	2	0000000004 -> 0000000005	0	alice	a.jpg	alice.com	user	a@x.com