MySQL中递归查询实现递归CTE在层级数据查询中的应用-mysql教程-PHP中文网

MySQL中递归查询实现递归CTE在层级数据查询中的应用

尼克

发布： 2025-06-11 14:15:01

原创

930人浏览过

mysql 8.0引入递归cte以支持层级数据查询。递归cte是一种可调用自身的公共表表达式，适用于树形或图结构数据处理，基本结构包括初始查询与递归部分并通过union all连接。1. 可用于查询组织结构中的所有下属员工；2. 构建目录树结构如商品分类；3. 使用时需注意启用mysql 8.0及以上版本、避免无限循环并设置最大深度限制；4. 建议对parent_id字段建立索引以提升性能。递归cte为处理层级结构提供高效方案，但需关注版本兼容性及细节控制。

MySQL中递归查询实现递归CTE在层级数据查询中的应用

在MySQL中处理层级数据，比如组织架构、目录树、评论回复等场景时，递归查询是个很实用的功能。早期的MySQL版本并不支持递归查询，只能通过多次查询或者程序逻辑来实现。但从MySQL 8.0开始，引入了递归CTE（Common Table Expression）语法，使得这类问题可以在SQL层面高效解决。

什么是递归CTE？

递归CTE是一种可以调用自身的公共表表达式，它特别适合用来处理树形结构或图结构的数据。基本结构包括一个初始查询和一个递归部分，两者通过UNION ALL连接。

举个简单的例子，如果你有一个部门表departments，字段包括id和parent_id，你想查出某个部门及其所有子部门的信息，就可以使用递归CTE来完成。

WITH RECURSIVE dept_tree AS (
    SELECT id, name, parent_id
    FROM departments
    WHERE id = 1  -- 初始节点，比如根部门

    UNION ALL

    SELECT d.id, d.name, d.parent_id
    FROM departments d
    INNER JOIN dept_tree t ON d.parent_id = t.id
)
SELECT * FROM dept_tree;

登录后复制

这个语句会从id=1的部门开始，不断查找其下级部门，直到没有更多子节点为止。

递归CTE的典型应用场景

查询组织结构中的所有下属员工

假设你有一个员工表employees，其中包含字段employee_id和manager_id，你想找出某个领导下的所有下属员工（包括间接下属），这时候递归CTE就能派上用场。

WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id = 100  -- 查找直接下属

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

登录后复制

这种写法能一次性获取完整的下属链条，比循环查询效率高很多。

构建目录树结构

文件系统或商品分类常采用父子结构存储。例如，你有一个categories表，里面有category_id和parent_id字段。要展示某一级分类下的完整子树结构，也可以用递归CTE：

WITH RECURSIVE category_tree AS (
    SELECT category_id, name, parent_id
    FROM categories
    WHERE category_id = 5

    UNION ALL

    SELECT c.category_id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_tree t ON c.parent_id = t.category_id
)
SELECT * FROM category_tree;

登录后复制

这样就能拿到该分类及其所有子分类的信息，方便前端渲染成树状结构。

使用递归CTE需要注意的地方

必须启用MySQL 8.0及以上版本：递归CTE是MySQL 8.0才引入的功能，老版本不支持。

避免无限循环：如果数据中存在环（比如A的父级是B，B的父级又是A），会导致递归无法终止。可以通过设置最大深度限制：

SET LOCAL statement_timeout = '60s';

登录后复制

或者在查询中加一个层级字段，控制递归层数：

WITH RECURSIVE dept_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE id = 1

    UNION ALL

    SELECT d.id, d.name, d.parent_id, t.level + 1
    FROM departments d
    INNER JOIN dept_tree t ON d.parent_id = t.id
    WHERE t.level < 10  -- 控制最多递归10层
)
SELECT * FROM dept_tree;

登录后复制