一、什么是 WITH RECURSIVE

简单来说，WITH RECURSIVE 就是 MySQL 提供的递归公共表表达式（Recursive CTE），核心就一句话：让 SQL 自己调用自己，实现递归查询。

类比一下，这就像 Ja va 里常见的递归方法：传入一个节点，内部再调用自身处理子节点。但在 WITH RECURSIVE 出现之前，SQL 要想处理树形数据，只能靠多次自关联（JOIN）、应用层循环查询或者存储过程递归，写起来又绕又慢。现在一条 WITH RECURSIVE 语句就能搞定，效率翻倍。

二、MySQL 从哪个版本开始支持？

答案是 MySQL 8.0。从这个版本起，MySQL 正式纳入了 CTE（公共表表达式）和 Recursive CTE（递归公共表表达式）。也就是说，WITH ... 和 WITH RECURSIVE ... 都是 8.0 新增的特性。至于 5.7 及之前的版本——抱歉，不支持。

三、它解决了什么问题

日常开发中，树形结构几乎无处不在：

组织架构

董事长
├── 总经理
│    ├── 技术部
│    └── 财务部
└── 人事部

菜单系统

系统管理
├── 用户管理
├── 角色管理
└── 权限管理

行政区划

中国
├── 北京
├── 上海
└── 广东
      ├── 深圳
      └── 广州

评论回复

评论1
├── 回复1
│    └── 回复2
└── 回复3

在过去，要查询某个节点的所有子节点，只能一层一层手动循环：先查父节点下的子节点，再查子节点下的子节点……重复写 N 次 SQL。而现在，一句 WITH RECURSIVE 就能一次性把整棵树拉出来。这也是它最有价值的场景。

四、WITH RECURSIVE 语法结构

标准语法长这样：

WITH RECURSIVE cte_name AS (
    -- 初始查询（锚点查询）
    SELECT ...
    UNION ALL
    -- 递归查询
    SELECT ...
    FROM table t
    JOIN cte_name c
      ON ...
)
SELECT * FROM cte_name;

五、递归执行过程

我们用一个生成数字序列的例子来理解：

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM nums
    WHERE n < 5
)
SELECT * FROM nums;

执行步骤拆解：

第一步

先执行锚点查询，得到 1。

第二步

把上一步的结果带入递归部分：1 + 1，得到 2。

第三步

继续递归，依次得到 3、4、5，直到 WHERE n < 5 条件不满足为止。

最终结果就是 1,2,3,4,5。每一步的结果都通过 UNION ALL 合并到最终结果集里。

六、WITH RECURSIVE 的组成部分

一个完整的递归 CTE 必须包含三部分：

1. Anchor（锚点）

递归的起点，也就是第一次执行的那条 SELECT 语句。

2. Recursive（递归部分）

不断引用自身的那部分 SELECT，注意必须包含终止条件，否则会无限循环。

3. UNION ALL

连接锚点和递归部分的“桥梁”。这里推荐始终使用 UNION ALL（不去重），因为 UNION 会额外做去重操作，影响性能。

七、第一个实战：生成数字序列

生成 1 到 10 的序列：

WITH RECURSIVE nums AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM nums
    WHERE num < 10
)
SELECT * FROM nums;

结果：

1 2 3 4 5 6 7 8 9 10

八、生成日期序列

生成最近 7 天：

WITH RECURSIVE dates AS (
    SELECT CURDATE() AS dt
    UNION ALL
    SELECT DATE_SUB(dt, INTERVAL 1 DAY)
    FROM dates
    WHERE dt > CURDATE() - INTERVAL 6 DAY
)
SELECT * FROM dates;

结果会得到从今天往前 7 天的日期列表，比如 2026-06-15、2026-06-14……

九、树形结构实战

先建一张部门表：

CREATE TABLE dept (
    id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    parent_id INT
);

数据如下：

1  总公司    NULL
2  技术中心  1
3  财务中心  1
4  开发部    2
5  测试部    2
6  Ja va组    4
7  前端组    4

对应的树形结构：

总公司(1)
├── 技术中心(2)
│    ├── 开发部(4)
│    │    ├── Ja va组(6)
│    │    └── 前端组(7)
│    └── 测试部(5)
└── 财务中心(3)

十、递归向下查询（查询所有子节点）

要查询部门 1 下面的所有节点，可以这样写：

WITH RECURSIVE dept_tree AS (
    SELECT
        id,
        dept_name,
        parent_id,
        1 level
    FROM dept
    WHERE id = 1
    UNION ALL
    SELECT
        d.id,
        d.dept_name,
        d.parent_id,
        dt.level + 1
    FROM dept d
    JOIN dept_tree dt
      ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

结果：

1  总公司
2  技术中心
3  财务中心
4  开发部
5  测试部
6  Ja va组
7  前端组

十一、增加层级显示

只需要在查询中加上 level 字段，就能清晰看到每个节点的深度：

WITH RECURSIVE dept_tree AS (
    SELECT id, dept_name, parent_id, 1 level
    FROM dept WHERE id=1
    UNION ALL
    SELECT d.id, d.dept_name, d.parent_id, dt.level+1
    FROM dept d
    JOIN dept_tree dt ON d.parent_id=dt.id
)
SELECT id, dept_name, level FROM dept_tree;

结果：

id  dept_name   level
1   总公司      1
2   技术中心    2
3   财务中心    2
4   开发部      3
5   测试部      3
6   Ja va组      4
7   前端组      4

十二、生成完整路径

很多权限系统会要求显示完整的组织路径，比如“总公司/技术中心/开发部/Ja va组”。实现这个很简单：

WITH RECURSIVE dept_tree AS (
    SELECT
        id,
        dept_name,
        parent_id,
        dept_name AS path
    FROM dept
    WHERE id=1
    UNION ALL
    SELECT
        d.id,
        d.dept_name,
        d.parent_id,
        CONCAT(dt.path, '/', d.dept_name)
    FROM dept d
    JOIN dept_tree dt ON d.parent_id=dt.id
)
SELECT * FROM dept_tree;

结果会生成所有节点的完整路径字符串。

十三、WITH RECURSIVE 能向上查吗？

完全可以。很多人容易误解，以为递归只能从父到子向下查。实际上，递归的方向完全由 JOIN 条件控制——只要把 JOIN 的关联方向反过来，就能实现向上查询祖先节点。

十四、向上递归查询祖先节点

比如要查 Ja va组（id=6）的所有上级：

WITH RECURSIVE parent_tree AS (
    SELECT id, dept_name, parent_id
    FROM dept
    WHERE id = 6
    UNION ALL
    SELECT d.id, d.dept_name, d.parent_id
    FROM dept d
    JOIN parent_tree pt
      ON d.id = pt.parent_id
)
SELECT * FROM parent_tree;

结果：

6  Ja va组
4  开发部
2  技术中心
1  总公司

十五、向上生成完整路径

类似地，我们可以向上拼接出完整的路径字符串：

WITH RECURSIVE parent_tree AS (
    SELECT id, dept_name, parent_id, dept_name AS path
    FROM dept WHERE id=6
    UNION ALL
    SELECT d.id, d.dept_name, d.parent_id,
           CONCAT(d.dept_name, '/', pt.path)
    FROM dept d
    JOIN parent_tree pt ON d.id=pt.parent_id
)
SELECT * FROM parent_tree ORDER BY id;

最终会得到类似 总公司/技术中心/开发部/Ja va组 的路径字符串。

十六、避免死循环

如果数据意外出现了环形引用（比如 1→2→3→1），递归就会无限执行下去。解决办法是记录已经访问过的节点路径，判断是否重复：

WITH RECURSIVE dept_tree AS (
    SELECT id, parent_id,
           CAST(id AS CHAR(1000)) path
    FROM dept WHERE id=1
    UNION ALL
    SELECT d.id, d.parent_id,
           CONCAT(dt.path, ',', d.id)
    FROM dept d
    JOIN dept_tree dt ON d.parent_id=dt.id
    WHERE FIND_IN_SET(d.id, dt.path)=0
)
SELECT * FROM dept_tree;

通过 FIND_IN_SET 检查当前节点是否已经在路径中间出现过，如果出现过就终止该分支的递归。

十七、递归层数限制

MySQL 默认的递归最大深度是 1000 层，可以通过以下变量查看：

SHOW VARIABLES LIKE '%recursion%';

变量名是 cte_max_recursion_depth。如果需要调整，可以这样设置：

SET SESSION cte_max_recursion_depth = 5000;
-- 或者全局生效
SET GLOBAL cte_max_recursion_depth = 5000;

十八、WITH RECURSIVE 使用规则总结

写递归 CTE 时必须遵循以下要点：

• 必须包含 WITH RECURSIVE name AS (anchor UNION ALL recursive) 结构
• 递归部分必须引用 CTE 自身（即 FROM name）
• 必须有终止条件，否则死循环
• 推荐使用 UNION ALL 而不是 UNION，后者额外去重影响性能

十九、企业开发中的典型应用

• 组织架构树：查询所有下级
• RBAC 权限菜单：加载菜单树
• 评论回复：查询完整评论链
• 行政区域：查询省市区层级
• 商品分类：查询所有子分类

二十、面试高频问题

Q1：WITH RECURSIVE 从哪个版本开始支持？

MySQL 8.0。

Q2：WITH 和 WITH RECURSIVE 区别？

普通 WITH 定义的是非递归 CTE，不能自引用；而 WITH RECURSIVE 允许在 CTE 内部引用自身，实现递归。

Q3：能否查询父节点？

可以。改变 JOIN 方向即可：向下用 d.parent_id = tree.id，向上用 d.id = tree.parent_id。

Q4：为什么推荐 WITH RECURSIVE？

相比传统的循环查询方式，递归 CTE 的 SQL 更简洁、只需访问一次数据库、性能更好，且天然支持树形结构。

总结

WITH RECURSIVE 是 MySQL 8.0 引入的递归查询利器，通过“锚点查询 + UNION ALL + 递归查询”的模式，可以优雅地处理组织架构、菜单树、评论树、行政区划、商品分类等所有层级数据。无论是向下查询子孙节点，还是向上追溯祖先节点，一句 SQL 就能搞定。对于现代 MySQL 的树形数据处理，它已经成为当之无愧的首选方案。

参考：

mysql递归查询语法WITH RECURSIVE

MySQL RECURSIVE Clauses

MySQL | Recursive CTE (Common Table Expressions)