Mysql8递归查询

最新推荐文章于 2025-10-17 15:21:18 发布

原创最新推荐文章于 2025-10-17 15:21:18 发布 · 958 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库专栏收录该内容

124 篇文章

订阅专栏

MySQL 8.0 引入了对递归公用表表达式（Recursive Common Table Expressions, Recursive CTE） 的支持，这让你能更优雅地查询具有层次结构或树形结构的数据。下面我会为你梳理其核心概念、语法、示例及注意事项。

📊 Recursive CTE 核心语法

Recursive CTE 的基本语法结构如下：

WITH RECURSIVE cte_name (column_list) AS (
    -- 初始查询（Anchor Member）：选择起点
    SELECT ... 
    UNION ALL
    -- 递归查询（Recursive Member）：引用CTE自身进行连接
    SELECT ... FROM cte_name ...
)
-- 最终使用CTE进行查询
SELECT * FROM cte_name;

关键组成部分：

WITH RECURSIVE：声明这是一个递归CTE。
cte_name：你为递归CTE定义的临时名称。
column_list（可选）：可指定CTE的列名。
Anchor Member：初始查询，用于选取递归的起点。
UNION ALL：合并初始查询和递归查询的结果（UNION DISTINCT 也可用，但会去重）。
Recursive Member：通过引用CTE自身（cte_name）并与初始部分联接来进行递归操作。此部分不能包含 GROUP BY、ORDER BY、LIMIT 等聚合或排序操作。
终止条件：递归会持续进行，直到递归部分不再产生新的行。

🗄️ 常见应用场景

Recursive CTE 非常适合处理以下类型的数据：

组织架构：查询员工及其所有下属（或所有上级）。
多级分类：查询商品分类、菜单结构等。
文件系统：查询目录及其所有子文件和子目录。
路径查询：查找图中两点间的路径（需注意循环风险）。
数据生成：生成连续的日期或数字序列。

📝 具体查询示例

1. 查询所有下属（自上而下）

假设有 employees 表（id, name, manager_id），要查询某个经理（如 id 为 1）及其所有下属。

WITH RECURSIVE subordinate_tree AS (
    -- 初始查询：定位到根节点（id=1的员工）
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1
    UNION ALL
    -- 递归查询：联接自身，查找直接下属
    SELECT e.id, e.name, e.manager_id, st.level + 1 AS level
    FROM employees e
    INNER JOIN subordinate_tree st ON e.manager_id = st.id
)
SELECT * FROM subordinate_tree;

id	name	manager_id	level
1	Alice	NULL	1
2	Bob	1	2
3	Charlie	1	2
4	David	2	3
5	Eve	2	3

2. 查询所有上级（自下而上）

要查询某个员工（如 id 为 5）的所有上级经理。

WITH RECURSIVE manager_tree AS (
    -- 初始查询：定位到起点员工（id=5）
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 5
    UNION ALL
    -- 递归查询：联接自身，查找直接上级
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN manager_tree mt ON e.id = mt.manager_id
)
SELECT * FROM manager_tree;

3. 生成序列

Recursive CTE 也可用于生成数字序列，例如生成 1 到 5 的数字：

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

n
1
2
3
4
5

⚠️ 重要注意事项

终止条件至关重要：务必在递归成员中设置明确的终止条件（如 WHERE n < 5 或 ON e.manager_id = st.id），否则查询将无限循环直到达到系统限制。
递归深度限制：MySQL 通过系统变量 cte_max_recursion_depth（默认1000）限制递归调用次数以防止无限循环。若递归层次过深，可能需临时调整此设置：
```
SET SESSION cte_max_recursion_depth = 10000; -- 提高当前会话的递归深度限制
```
性能考量：
- 确保递归查询中用于连接的列（如 manager_id, id）上有合适的索引，这对大表性能至关重要。
- 递归查询可能消耗较多内存和计算资源，尤其是在处理大数据集或深度递归时，需谨慎使用。
循环处理：MySQL 的 Recursive CTE 默认不会自动检测数据中可能存在的循环引用（如 A 的经理是 B，B 的经理又是 A）。这可能导致递归陷入死循环。在设计数据结构时应避免循环，或考虑在应用层添加检查逻辑。
遍历顺序：MySQL 的 Recursive CTE 默认采用广度优先（Breadth-first） 的方式进行遍历。