如何实现MySQL 8.0中的递归查询_利用CTE公用表表达式

MySQL 8.0引入的CTE公用表表达式分为非递归CTE和递归CTE两类，递归CTE可以实现传统SQL难以完成的层级数据遍历、序列生成等递归类需求，避免了使用存储过程或复杂自连接实现递归逻辑的繁琐。

CTE公用表表达式基础语法

CTE公用表表达式通过WITH关键字定义，基本语法格式如下：

-- 非递归CTE语法
WITH cte_name (column1, column2) AS (
    SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM cte_name;

递归CTE在普通CTE的基础上增加了RECURSIVE关键字，结构分为两个核心部分：初始查询（锚点成员）和递归查询（递归成员），两部分通过UNION ALL或UNION连接。

递归CTE完整语法结构

WITH RECURSIVE cte_name (column_list) AS (
    -- 锚点成员：初始查询，只执行一次
    SELECT initial_column_list
    FROM table_name
    WHERE initial_condition
    UNION ALL
    -- 递归成员：引用CTE自身，重复执行直到无新数据返回
    SELECT recursive_column_list
    FROM table_name t
    INNER JOIN cte_name c ON t.parent_id = c.id
    WHERE recursive_condition
)
SELECT * FROM cte_name;

需要注意递归成员中必须引用CTE本身的名称，且递归查询的字段数量和类型需要和锚点成员的字段完全匹配，否则会抛出语法错误。

常见递归查询场景示例

场景一：查询员工层级关系

假设存在员工表employee，结构如下：

如果要查询ID为1的员工的所有下属层级，可使用以下递归CTE实现：

WITH RECURSIVE subordinate_cte (id, name, manager_id, depth) AS (
    -- 锚点成员：查询初始员工（ID为1的员工）
    SELECT id, name, manager_id, 0 AS depth
    FROM employee
    WHERE id = 1
    UNION ALL
    -- 递归成员：查询当前员工的下属
    SELECT e.id, e.name, e.manager_id, c.depth + 1
    FROM employee e
    INNER JOIN subordinate_cte c ON e.manager_id = c.id
)
SELECT id, name, manager_id, depth FROM subordinate_cte;

查询结果会包含ID为1的员工本身以及所有层级的下属，depth字段表示下属的层级深度，方便区分不同层级的员工。

场景二：生成连续数字序列

递归CTE还可以用来生成连续的序列，比如生成1到10的连续数字，实现代码如下：

WITH RECURSIVE num_cte (num) AS (
    -- 锚点成员：初始值为1
    SELECT 1
    UNION ALL
    -- 递归成员：每次加1，直到值达到10
    SELECT num + 1
    FROM num_cte
    WHERE num < 10
)
SELECT num FROM num_cte;

递归查询注意事项

• 递归CTE必须包含RECURSIVE关键字，否则MySQL会识别为普通CTE导致语法错误。
• 递归成员中不能包含GROUP BY、HAVING、聚合函数、ORDER BY子句，也不能使用LIMIT关键字。
• 需要确保递归查询有终止条件，否则会出现无限递归，MySQL默认设置递归最大次数为1000，可通过调整cte_max_recursion_depth参数修改该限制。
• 锚点成员和递归成员的字段数量、数据类型必须完全一致，字段名可以以锚点成员的定义为准。

使用递归CTE实现MySQL 8.0的递归查询，相比传统的自连接写法逻辑更清晰，代码可读性更高，适合处理各类层级结构、序列生成等递归类需求。

MySQL_8.0递归查询CTE公用表表达式SQL语法修改时间：2026-06-22 06:03:45