MySQL中通用表表达式（CTE）中的​​递归CTE

通用表表达式（CTE）中的​​递归CTE（WITH RECURSIVE）​​是MySQL中处理树形结构数据（如组织架构、分类目录、评论嵌套等）的强大工具。它允许通过递归查询逐层遍历父子关系的数据。以下是详细解释和示例：

---

​​一、递归CTE的核心概念​​

递归CTE由两部分组成：

1. ​​锚定成员（Anchor Member）​​：定义初始查询（非递归部分），生成递归的起点。
2. ​​递归成员（Recursive Member）​​：基于前一步的结果递归迭代，直到满足终止条件。

语法格式：

sql

复制

WITH RECURSIVE cte_name AS ( -- 锚定成员（初始查询） SELECT ... UNION ALL -- 递归成员（迭代查询） SELECT ... FROM cte_name JOIN ... ) SELECT * FROM cte_name;

---

​​二、应用场景示例：树形结构数据​​

假设我们有一个表示公司组织架构的表 employees，结构如下：

sql

复制

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT, -- 上级经理的ID（父节点） FOREIGN KEY (manager_id) REFERENCES employees(id) ); INSERT INTO employees VALUES (1, 'Alice', NULL), -- CEO，无上级 (2, 'Bob', 1), -- Alice的下属 (3, 'Charlie', 1), -- Alice的下属 (4, 'David', 2), -- Bob的下属 (5, 'Eve', 3); -- Charlie的下属

​​需求1：查询某员工的所有下属（自顶向下递归）​​

例如，查找Alice（id=1）的所有下属（包括间接下属）：

sql

复制

WITH RECURSIVE subordinates AS ( -- 锚定成员：从根节点（Alice）开始 SELECT id, name, manager_id, 1 AS level FROM employees WHERE id = 1 -- 初始条件：CEO UNION ALL -- 递归成员：逐层查找下属 SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e INNER JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates;

​​结果​​：

id | name    | manager_id | level
---|---------|------------|------
1  | Alice   | NULL       | 1    -- 根节点
2  | Bob     | 1          | 2    -- 第一层下属
3  | Charlie | 1          | 2    -- 第一层下属
4  | David   | 2          | 3    -- 第二层下属（Bob的下属）
5  | Eve     | 3          | 3    -- 第二层下属（Charlie的下属）

---

​​需求2：查询某员工的所有上级（自底向上递归）​​

例如，查找David（id=4）的所有上级（包括CEO）：

sql

复制

WITH RECURSIVE superiors AS ( -- 锚定成员：从叶子节点（David）开始 SELECT id, name, manager_id, 1 AS level FROM employees WHERE id = 4 UNION ALL -- 递归成员：逐层查找上级 SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e INNER JOIN superiors s ON e.id = s.manager_id ) SELECT * FROM superiors;

​​结果​​：

id | name    | manager_id | level
---|---------|------------|------
4  | David   | 2          | 1    -- 叶子节点
2  | Bob     | 1          | 2    -- 第一层上级
1  | Alice   | NULL       | 3    -- 根节点（CEO）

---

​​三、递归CTE的关键点​​

1. ​​终止条件​​：递归必须有一个明确的结束条件（如WHERE子句限制层级深度），否则会无限循环。

   sql

   复制

   -- 示例：限制最多递归3层 WITH RECURSIVE subordinates AS ( SELECT id, name, manager_id, 1 AS level FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e INNER JOIN subordinates s ON e.manager_id = s.id WHERE s.level < 3 -- 终止条件：最多3层 )

2. ​​性能优化​​：递归CTE在处理深层树结构时可能较慢，建议：

   • 在manager_id字段上建立索引。
   • 避免无意义的递归（如循环引用数据）。

3. ​​递归方向控制​​：

   • 自顶向下：通过manager_id = s.id连接。
   • 自底向上：通过e.id = s.manager_id连接。

---

​​四、其他应用场景​​

​​示例3：多级分类目录​​

假设表 categories 表示商品分类（父子关系）：

sql

复制

CREATE TABLE categories ( id INT PRIMARY KEY, name VARCHAR(50), parent_id INT, FOREIGN KEY (parent_id) REFERENCES categories(id) ); INSERT INTO categories VALUES (1, 'Electronics', NULL), (2, 'Laptops', 1), (3, 'Smartphones', 1), (4, 'Gaming Laptops', 2);

​​查询所有子分类​​（如查询"Laptops"的所有子类）：

sql

复制

WITH RECURSIVE subcategories AS ( SELECT id, name, parent_id FROM categories WHERE name = 'Laptops' UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c INNER JOIN subcategories s ON c.parent_id = s.id ) SELECT * FROM subcategories;

​​结果​​：

id | name              | parent_id
---|-------------------|----------
2  | Laptops           | 1
4  | Gaming Laptops    | 2

---

​​五、注意事项​​

1. ​​避免无限循环​​：如果数据中存在循环引用（如A的父节点是B，B的父节点又是A），递归CTE会进入死循环。需通过WHERE条件或MAXRECURSION限制层级。

   sql

   复制

   -- 设置最大递归次数为100 SET SESSION cte_max_recursion_depth = 100;

2. ​​递归CTE的隔离性​​：递归CTE在每次执行时独立计算，不与其他查询共享中间结果。

---

通过递归CTE，可以高效处理树形结构数据，解决传统SQL中多层嵌套查询难以维护的问题。实际应用中需结合业务场景设计递归逻辑，并注意性能优化。