CTE公用表表达式与递归查询基础
CTE全称Common Table Expression，即公用表表达式，是MySQL 8.0及以上版本支持的特性，它可以在一个查询中定义一个临时的结果集，供后续查询使用。当CTE配合WITH RECURSIVE语法时，就可以实现递归查询，专门处理具有层级关系的数据。
递归CTE的结构通常分为两部分：锚点成员和递归成员。锚点成员是递归的初始查询，返回最顶层的基础数据；递归成员则引用CTE自身，基于锚点成员的结果不断向下扩展，直到没有新的数据产生为止。
WITH RECURSIVE基本语法
递归CTE的基本语法结构如下：
WITH RECURSIVE cte_name (column1, column2, ...) AS (
-- 锚点成员：初始查询，返回初始数据集
SELECT initial_column1, initial_column2, ...
FROM table_name
WHERE 初始条件
UNION ALL
-- 递归成员：引用CTE自身，基于已有结果扩展
SELECT t.column1, t.column2, ...
FROM table_name t
INNER JOIN cte_name c ON t.关联字段 = c.关联字段
)
SELECT * FROM cte_name;
需要注意，递归成员和锚点成员的结果集列数、数据类型必须保持一致，且递归部分不能包含聚合函数、GROUP BY、LIMIT等会终止递归的操作。
场景示例：员工层级查询
假设有一张员工表employee，结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| id | INT | 员工ID |
| name | VARCHAR(50) | 员工姓名 |
| manager_id | INT | 直属上级ID，顶层员工为NULL |
现在需要查询所有员工及其对应的层级，从CEO开始向下展开，实现代码如下：
WITH RECURSIVE employee_hierarchy AS (
-- 锚点成员：查询顶层员工（CEO，manager_id为NULL）
SELECT id, name, manager_id, 1 AS level
FROM employee
WHERE manager_id IS NULL
UNION ALL
-- 递归成员：查询下属员工，层级加1
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, id;
上述代码中，锚点成员先找到最顶层的CEO，递归成员则通过manager_id和CTE自身的id关联，不断找到下一级员工，同时给每个员工标记对应的层级，最终得到完整的员工层级结构。
场景示例：商品分类树查询
再来看商品分类的场景，分类表category结构如下：
- id：分类ID
- category_name：分类名称
- parent_id：父分类ID，顶级分类为NULL
如果需要查询某个顶级分类下的所有子分类，形成完整的分类树，代码如下：
WITH RECURSIVE category_tree AS (
-- 锚点成员：查询指定的顶级分类，这里以id=1为例
SELECT id, category_name, parent_id, category_name AS full_path
FROM category
WHERE id = 1
UNION ALL
-- 递归成员：查询子分类，拼接完整分类路径
SELECT c.id, c.category_name, c.parent_id, CONCAT(ct.full_path, '->', c.category_name)
FROM category c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, category_name, full_path FROM category_tree;
这个示例中，递归过程中还拼接了完整的分类路径，方便直观看到每个子分类的所属层级关系。
使用注意事项
使用WITH RECURSIVE进行递归查询时，需要注意以下几点：
- MySQL版本要求：必须是8.0及以上版本，低版本不支持该语法。
- 避免无限递归：如果数据中存在循环引用，比如A的上级是B，B的上级是A，会导致递归无限执行，需要在代码中增加终止条件，或者确保业务数据没有循环引用。
- 性能问题：递归查询如果层级过深、数据量过大，可能会影响查询性能，实际使用中可以根据需要增加过滤条件，减少递归的数据范围。
- 递归成员的限制：递归部分不能使用ORDER BY、LIMIT、聚合函数等，这些操作只能在最终的SELECT语句中使用。
总结
使用MySQL的CTE公用表表达式配合WITH RECURSIVE语法，能够将复杂的递归查询逻辑拆分成清晰的锚点和递归部分，大幅降低代码复杂度，提升可读性。无论是员工组织架构、商品分类树还是其他层级结构数据的查询，都可以通过这种方式快速实现。开发者在实际使用时，只需要根据业务场景调整锚点的初始条件和递归的关联逻辑，就可以高效完成递归查询需求。
MySQLCTE公用表表达式WITH_RECURSIVE递归查询修改时间：2026-07-18 18:39:11