1. 项目概述：为什么我们需要MySQL递归查询？

如果你处理过组织结构、商品分类、评论楼中楼或者任何具有树形层级关系的数据，那你一定遇到过这样的困境：如何高效地查询一个节点的所有子孙节点，或者所有祖先节点？在MySQL 8.0之前，这通常意味着你需要编写复杂的存储过程，或者依赖应用程序层进行多次查询和递归组装，代码冗长且性能堪忧。我接手过一个重构项目，旧系统为了获取一个部门下的所有员工，竟然在代码里循环执行了十几次查询，页面打开慢得让人抓狂。

这正是MySQL递归查询（Recursive Common Table Expression， 简称递归CTE）要解决的痛点。自MySQL 8.0版本起，它引入了对通用表表达式（CTE）的支持，其中就包含了递归CTE。这相当于在SQL语言层面内置了一个“递归循环”的能力，让你用一条清晰、标准的SQL语句，就能完成复杂的层级遍历。这不仅仅是语法糖，更是对开发效率和查询性能的一次巨大提升。无论是做权限系统（查询用户所有角色）、内容管理系统（管理多级栏目），还是电商平台（遍历商品类目树），掌握递归查询都像是拿到了一把解开层级数据枷锁的钥匙。

本文将从一个真实的员工层级表案例出发，手把手带你从零理解递归CTE的语法、执行原理，再到各种实战场景的变体应用。我会分享在调试复杂递归时我常用的“可视化执行步骤”心法，以及如何避免让递归查询变成性能黑洞的注意事项。无论你是正在学习MySQL 8.0新特性的新手，还是被多层查询困扰已久的开发者，这篇“保姆级”指南都将为你提供可直接复用的解决方案。

2. 递归查询核心原理与语法拆解

要玩转递归查询，必须先吃透它的两个核心部分：非递归项（初始查询）和递归项。你可以把它想象成一场接力赛，或者一个不断自我复制的过程。

2.1 递归CTE的基本骨架

一个标准的递归CTE语法结构如下：

WITH RECURSIVE cte_name (column_list) AS ( -- 非递归项（初始成员） SELECT ... FROM ... WHERE ... -- 这是“种子”，递归的起点 UNION ALL -- 递归项 SELECT ... FROM cte_name, other_tables... WHERE ... -- 这里引用了CTE自身！ ) SELECT * FROM cte_name;

关键点在于UNION ALL后面的SELECT语句中，FROM子句里出现了cte_name自身。这就是“递归”二字的来源：查询的定义中引用了它自己。

执行流程（这是理解的重中之重）：

1. 初始化：首先执行非递归项（UNION ALL之前的部分），产生初始结果集。我们称这个集合为 R0。
2. 第一次递归：将 R0 作为cte_name代入递归项中进行查询，产生新的结果集 R1。
3. 第二次递归：将 R1 作为cte_name代入递归项中，产生 R2。
4. 循环与终止：重复上述过程，每次都将上一次递归产生的结果集作为输入，直到递归项查询结果为空集，即本次递归没有产生任何新行时，循环停止。
5. 合并结果：将所有迭代产生的结果集 R0, R1, R2... 通过UNION ALL合并起来，形成最终的CTE结果。

注意：这里使用的是UNION ALL，而不是UNION。因为递归过程需要保留所有迭代产生的行（包括可能重复的行），UNION的去重操作会干扰递归的进行，且通常性能更差。只有在你的业务逻辑明确需要去重时，才考虑使用UNION，但这在递归查询中非常罕见。

2.2 准备演示数据：员工层级表

光说不练假把式，我们创建一个经典的employees表来贯穿全文的示例：

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT NULL, INDEX idx_manager (manager_id), FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL ); INSERT INTO employees (id, name, manager_id) VALUES (1, '张三丰', NULL), -- 掌门人，没有上级 (2, '宋远桥', 1), -- 张三丰的下级 (3, '俞莲舟', 1), (4, '俞岱岩', 1), (5, '张松溪', 1), (6, '张翠山', 1), (7, '殷梨亭', 1), (8, '莫声谷', 1), (9, '宋青书', 2), -- 宋远桥的下级 (10, '小道童A', 9), -- 宋青书的下级 (11, '小道童B', 3); -- 俞莲舟的下级

这张表形成了一个简单的树形结构：张三丰是根节点，武当七侠是他的直接下属，宋青书是宋远桥的儿子（下属），还有两个小道童。

3. 实战演练：从基础查询到复杂场景

现在，我们利用递归CTE来解决几个实际开发中高频出现的问题。

3.1 场景一：查询某个节点的所有下属（向下递归）

这是最常见的需求。例如，我们要查询“宋远桥”（id=2）管理的所有下属，包括间接下属。

WITH RECURSIVE subordinate_tree AS ( -- 非递归项：找到起点（宋远桥本人） SELECT id, name, manager_id, 0 AS level FROM employees WHERE id = 2 -- 指定起点 UNION ALL -- 递归项：根据上一轮的结果，找他们的直接下属 SELECT e.id, e.name, e.manager_id, st.level + 1 FROM employees e INNER JOIN subordinate_tree st ON e.manager_id = st.id ) SELECT * FROM subordinate_tree;

执行结果与解析：

逐轮分析：

• R0 (level 0):WHERE id = 2-> 找到宋远桥。
• R1 (level 1): 将 R0 (id=2) 代入递归项，找manager_id = 2的员工 -> 找到宋青书。level = 0+1 =1。
• R2 (level 2): 将 R1 (id=9) 代入递归项，找manager_id = 9的员工 -> 找到小道童A。level = 1+1=2。
• R3 (level 3): 将 R2 (id=10) 代入递归项，找manager_id = 10的员工 -> 找不到，结果集为空，递归终止。

实操心得：level字段的妙用在非递归项中初始化一个level字段（这里从0开始），并在递归项中递增（st.level + 1），这不仅仅是为了展示层级深度。在后续查询中，你可以方便地通过WHERE level <= N来限制递归深度，防止在数据异常（如循环引用）时查询失控。这是递归查询中的一个重要安全措施。

3.2 场景二：查询某个节点的所有上级（向上递归）

现在反过来，我想知道“小道童A”（id=10）的所有上级领导，直到最顶级的掌门。

WITH RECURSIVE manager_tree AS ( -- 非递归项：找到起点（小道童A本人） SELECT id, name, manager_id, 0 AS level FROM employees WHERE id = 10 UNION ALL -- 递归项：根据上一轮的结果，找他们的直接上级 SELECT e.id, e.name, e.manager_id, mt.level + 1 FROM employees e INNER JOIN manager_tree mt ON e.id = mt.manager_id -- 注意连接条件反过来了 ) SELECT id, name, level FROM manager_tree ORDER BY level DESC;

执行结果：

关键点解析：向上递归和向下递归的核心区别在于连接条件。

• 向下递归（找下属）：ON e.manager_id = st.id（员工的领导ID = 上一轮结果的员工ID）
• 向上递归（找上级）：ON e.id = mt.manager_id（员工的ID = 上一轮结果的领导ID）

这里的结果包含了起点自身（level 0）。如果你只想看上级，可以在最终查询中过滤掉level = 0的行。ORDER BY level DESC可以让结果从最高级领导向下排列，更符合阅读习惯。

3.3 场景三：生成完整的树形路径与缩进展示

我们经常需要在后台管理系统里以树形结构展示部门或分类。这需要我们将递归查询的结果格式化成易于理解的样式。

WITH RECURSIVE tree_path AS ( SELECT id, name, manager_id, CAST(id AS CHAR(255)) AS path, -- 初始化路径 0 AS level FROM employees WHERE manager_id IS NULL -- 从根节点开始 UNION ALL SELECT e.id, e.name, e.manager_id, CONCAT(tp.path, '->', e.id), -- 拼接路径 tp.level + 1 FROM employees e INNER JOIN tree_path tp ON e.manager_id = tp.id ) SELECT id, CONCAT(REPEAT(' ', level), '├─ ', name) AS tree_view, -- 用缩进可视化层级 path, level FROM tree_path ORDER BY path; -- 按路径排序，自然形成树形顺序

执行结果（部分）：

技巧详解：

1. 路径（path）字段：使用CAST(id AS CHAR(255))初始化，并在递归中使用CONCAT拼接。这生成了一个像1->2->9->10的字符串，清晰地表示了从根到当前节点的完整链路。这个字段对于按树形顺序排序（ORDER BY path）和快速判断节点关系（例如用WHERE path LIKE '1->2%'查找某分支下的所有节点）极其有用。
2. 树形视图（tree_view）：利用REPEAT(' ', level)生成与层级深度成正比的缩进（这里用四个空格），再配合├─这样的图形字符，可以在纯文本的查询结果中直观地看到树形结构。这在调试或生成简单报表时非常方便。
3. 从根节点开始：通过WHERE manager_id IS NULL启动递归，可以一次性拉出整棵树。这对于数据初始化、导出或全量分析场景非常高效。

4. 进阶技巧与性能优化实战

掌握了基础用法，我们来看看如何应对更复杂的情况和规避性能陷阱。

4.1 处理循环引用与设置递归深度限制

在脏数据或特殊业务逻辑下，可能会出现A的上级是B，B的上级又是A的循环引用情况。这会导致递归查询陷入无限循环。MySQL默认提供了两种防护机制，但我们也需要主动设防。

1. 使用cte_max_recursion_depth系统变量这是MySQL最直接的防护墙。它限制了递归CTE的最大迭代次数，默认值是1000。你可以针对当前会话修改它：

SET SESSION cte_max_recursion_depth = 500; -- 调低限制 SET SESSION cte_max_recursion_depth = 10000; -- 调高限制以处理深层树

在递归查询前设置这个值，是控制风险的基本操作。

2. 在递归逻辑中主动检测循环对于严格的数据，我们可以通过在CTE中增加一个路径集合字段，来主动判断是否遇到了重复节点。

WITH RECURSIVE recursive_cte AS ( SELECT id, name, manager_id, CAST(id AS CHAR(255)) AS path, JSON_ARRAY(id) AS visited_ids, -- 使用JSON数组存储已访问的ID 0 AS level FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id, CONCAT(rc.path, '->', e.id), JSON_ARRAY_APPEND(rc.visited_ids, '$', e.id), -- 将新ID加入数组 rc.level + 1 FROM employees e INNER JOIN recursive_cte rc ON e.manager_id = rc.id WHERE NOT JSON_CONTAINS(rc.visited_ids, CAST(e.id AS JSON), '$') -- 关键：确保新ID不在已访问列表中 ) SELECT * FROM recursive_cte;

这里利用JSON_ARRAY和JSON_CONTAINS函数来维护一个已访问ID的列表。递归项中的WHERE子句确保了不会再去遍历已经访问过的节点，从而有效避免了循环。这种方法比单纯依赖深度限制更精确，但会带来额外的JSON计算开销，适用于对数据完整性要求极高、且树深度不是特别深的场景。

4.2 递归查询的性能陷阱与索引优化

递归查询可能成为性能杀手，尤其是在处理大型树（如超大型组织架构、深度分类）时。其性能瓶颈主要出现在递归项的连接操作上。

核心性能原则：递归项的连接条件必须走索引！

在我们的例子中，递归项是FROM employees e INNER JOIN cte ON e.manager_id = cte.id。这里e.manager_id是驱动字段。因此，在employees.manager_id列上建立索引是必须的。如果没有这个索引，每次递归迭代都会进行全表扫描，当数据量较大时，查询时间会呈指数级增长。

你可以通过EXPLAIN命令来查看递归查询的执行计划：

EXPLAIN WITH RECURSIVE ... (你的递归查询语句);

在输出中，重点关注递归部分（UNION ALL之后的部分）的SELECT，看它是否使用了idx_manager这样的索引。如果看到type: ALL（全表扫描），你就必须考虑添加索引了。

其他优化建议：

• 减少CTE输出列：在CTE定义中只选择必要的列，而不是SELECT *。多余的数据会在每次递归迭代中被携带和传递，增加开销。
• 尽早过滤：如果可能，在非递归项或递归项的WHERE子句中就加入过滤条件，减少参与递归的数据量。例如，如果你只关心活跃员工，可以加上AND e.is_active = 1。
• 权衡递归深度与广度：对于“广度”很大（每个节点下属很多）但“深度”很浅的树，递归查询效率尚可。但对于“深度”很深的链表式结构（例如评论的盖楼），递归查询可能需要很多次迭代，此时可以考虑在应用层分批次处理，或者使用像闭包表（Closure Table）这样的专门设计来存储层级关系的模型。

5. 常见问题排查与调试心得

即使理解了原理，在实际编写复杂的递归查询时，依然容易出错。下面是我总结的几个常见问题和调试方法。

5.1 问题一：查询返回空结果或结果不全

这是新手最常遇到的问题。90%的原因出在非递归项的初始条件上。

排查步骤：

1. 独立运行非递归项：把CTE中UNION ALL之前的部分单独拿出来执行。确保它能返回你期望的“种子”行。如果这里就返回空，那整个递归查询结果必然是空的。
2. 检查连接条件：确认递归项中的ON条件是否正确。是e.manager_id = cte.id（向下找）还是e.id = cte.manager_id（向上找）？连接方向反了，会导致递归无法进行。
3. 检查数据一致性：确认你的“起点”ID在表中真实存在，并且其manager_id关系符合预期。有时数据脏污（如起点ID的manager_id指向一个不存在的ID）会导致递归提前终止。

5.2 问题二：错误“Recursive query aborted after 1 second”

这通常是触发了cte_max_recursion_depth限制。除了前面提到的设置该变量外，更应检查数据是否存在循环引用。

诊断循环引用的快速查询：你可以写一个简单的查询来寻找直接循环（A管B，B又管A）：

SELECT a.id, a.name, b.id as mgr_id, b.name as mgr_name FROM employees a INNER JOIN employees b ON a.manager_id = b.id WHERE b.manager_id = a.id;

如果这个查询返回了行，那就找到了直接的死循环。对于间接的长循环，可以通过编写一个寻找“反向路径”的递归查询来检测，思路类似前面提到的“主动检测循环”的方法。

5.3 调试心法：将递归“可视化”执行

对于复杂的递归逻辑，我习惯在CTE中增加一个iteration或step字段，并在最终输出时将其排序，来模拟递归的每一步。

WITH RECURSIVE debug_cte AS ( SELECT id, name, manager_id, 0 AS level, 0 AS iteration, CAST(id AS CHAR) AS debug_path FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id, dc.level + 1, dc.iteration + 1, CONCAT(dc.debug_path, '->', e.id) FROM employees e INNER JOIN debug_cte dc ON e.manager_id = dc.id WHERE dc.iteration < 5 -- 防止失控，只递归5步看看 ) SELECT iteration, level, id, name, debug_path FROM debug_cte ORDER BY iteration, id;

通过观察iteration列，你可以清晰地看到每一轮递归产生了哪些新行。debug_path则展示了每一行是如何被找到的。这个方法是定位递归逻辑错误（比如为什么某一层没有产生预期数据）的利器。

5.4 递归CTE与存储过程/函数递归的对比

在MySQL 8.0之前，我们只能用存储过程或函数来实现递归。现在有了递归CTE，该如何选择？

个人建议：对于标准的、以查询为目的的层级遍历，优先使用递归CTE。它的简洁和可维护性优势巨大。只有当你的递归逻辑异常复杂，需要在递归过程中进行数据修改、调用外部服务或实现非标准的遍历算法（如广度优先搜索的特定优化）时，才考虑使用存储过程。