MySQL递归查询实战：5分钟搞定树形结构所有子节点（附完整SQL模板）

MySQL递归查询实战：5分钟搞定树形结构所有子节点（附完整SQL模板）

树形结构数据，比如组织架构、菜单权限、分类目录，几乎是每个后端工程师绕不开的“老朋友”。每次遇到需要查询某个节点下所有子孙的任务，你是不是还在琢磨着写个递归函数，在应用层里一遍遍调用数据库？或者用复杂的多表连接把自己绕晕？其实，MySQL自身就藏着处理这类问题的“瑞士军刀”，只是很多人还没找到正确的打开方式。今天，我们不谈复杂的理论，直接从实战出发，用最清晰的思路和拿来即用的SQL模板，让你在5分钟内彻底掌握MySQL递归查询的核心技巧，从此面对层级数据查询，游刃有余。

1. 理解树形数据与递归查询的本质

在深入代码之前，我们得先搞清楚要解决什么问题。所谓树形结构数据，最典型的特征就是每个数据记录都有一个指向其父记录的引用，通常是一个 parent_id 字段。这就形成了一种自关联的关系。想象一下公司的部门表：总公司（id=1）是根节点，其下有技术部（id=2，parent_id=1）、市场部（id=3，parent_id=1）；技术部下面又细分前端组（id=4，parent_id=2）和后端组（id=5，parent_id=2）。

当我们需要查询“技术部及其下属所有团队”时，传统的 WHERE parent_id = 2 只能找到直接子节点（前端组、后端组），而找不到孙子节点及更深的后代。这就是递归查询要解决的痛点：获取一个给定节点的所有后代节点，无论层级有多深。

在MySQL 8.0之前，数据库本身并不直接支持递归查询语法（如其他数据库的 WITH RECURSIVE）。但这难不倒聪明的开发者，大家发明了基于会话变量和内联视图的技巧来模拟递归。其核心思想是：利用MySQL的用户变量，在逐行扫描排序后的数据时，动态地构建一个包含所有后代节点ID的集合。

注意：本文重点讲解的是MySQL 8.0以下版本广泛使用的会话变量模拟递归方法。虽然MySQL 8.0引入了更标准的CTE（公共表表达式）递归，但大量存量项目仍运行在旧版本上，掌握这种通用技巧依然极具实用价值。

2. 构建示例数据环境

任何学习都需要一个沙盒。我们先来创建一个最简化的树形数据表，并插入一些有层次关系的示例数据。这个表结构足够通用，你可以轻松映射到你的业务场景。

-- 创建一个名为 `department` 的部门表
CREATE TABLE `department` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '部门ID，主键',
  `name` VARCHAR(50) NOT NULL COMMENT '部门名称',
  `parent_id` INT DEFAULT NULL COMMENT '父部门ID，NULL表示根部门',
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`) -- 为父ID建立索引，提升查询性能
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门层级表';

接下来，我们插入一些有代表性的数据，构建一个清晰的树形结构：

-- 插入示例数据
INSERT INTO `department` (`id`, `name`, `parent_id`) VALUES
(1, '集团公司', NULL),
(2, '技术研发中心', 1),
(3, '市场营销中心', 1),
(4, '前端开发部', 2),
(5, '后端开发部', 2),
(6, '算法研究部', 2),
(7, '品牌推广部', 3),
(8, '渠道运营部', 3),
(9, 'Web前端组', 4),
(10, '移动端组', 4),
(11, 'Java服务组', 5),
(12, 'Go服务组', 5),
(13, '华东区渠道', 8),
(14, '华南区渠道', 8);

为了更直观地理解数据间的层级关系，我们用下面的表格来展示：

这样，一棵清晰的“部门树”就构建好了。我们的目标是：输入任何一个部门的ID，能一次性查出它下面所有的子部门。

3. 核心SQL模板逐行解析

下面这个SQL语句，就是实现递归查询的“魔法公式”。它看起来有点复杂，但拆解开来每一步都逻辑清晰。我们以查询“技术研发中心（id=2）”所有子部门为例。

SELECT
    id,
    name
FROM (
    SELECT
        t1.id,
        t1.name,
        t1.parent_id,
        IF(
            FIND_IN_SET(t1.parent_id, @pids) > 0,
            @pids := CONCAT(@pids, ',', t1.id),
            -1
        ) AS is_child
    FROM
        (SELECT id, name, parent_id FROM department ORDER BY parent_id, id) t1,
        (SELECT @pids := 2) t2 -- 初始化要查询的根节点ID
) t3
WHERE is_child != -1;

让我们像调试代码一样，逐层剖析这个查询是如何工作的：

第一步：初始化与数据准备 (FROM 子句)

(SELECT id, name, parent_id FROM department ORDER BY parent_id, id) t1,
(SELECT @pids := 2) t2

• t1 子查询：将部门表按照 parent_id 和 id 排序。这个排序至关重要，它确保了在扫描数据时，父节点总是出现在其子节点之前（或至少以可预测的顺序出现）。ORDER BY parent_id, id 是一种常见的保证方式。
• t2 子查询：初始化一个MySQL用户变量 @pids，并将其设置为我们要查询的起始节点ID，这里是 2。这个变量将作为一个“已发现节点ID集合”的字符串容器。

第二步：逐行判断与集合扩展 (IF 与 FIND_IN_SET) 这是整个查询的灵魂。对于 t1 中的每一行数据，都会执行这个 IF 函数：

1. FIND_IN_SET(t1.parent_id, @pids) > 0：检查当前行的 parent_id 是否存在于变量 @pids 这个用逗号分隔的ID字符串中。
2. 如果为真（即当前节点的父节点已经在我们的目标集合中），那么：
   • @pids := CONCAT(@pids, ',', t1.id)：将当前节点的 id 追加到 @pids 字符串的末尾。这意味着我们“发现”了一个新的子节点，并将其纳入集合。
   • 同时，IF 函数返回这个新的 @pids 值（实际上在is_child列中我们更关心标记，这里返回集合值更多是为了演示逻辑，实践中常直接标记）。
3. 如果为假（当前节点的父节点不在集合中），则返回 -1 作为标记。
4. 最终，为每一行生成一个 is_child 列，其值要么是扩展后的 @pids（或其代表的有效标记），要么是 -1。

第三步：过滤出结果 (WHERE 子句) 最外层的查询简单地筛选出 is_child != -1 的所有行。这些行就是那些在遍历过程中，其父节点已被纳入 @pids 集合的节点，即我们要找的所有子节点。

为了让你更直观地看到这个“发现”过程，我们模拟一下前几行的执行状态：

可以看到，只有当扫描到 parent_id 为2的记录（id=4,5,6）时，它们才被识别并加入集合。随后，当扫描到 parent_id 为4或5的记录（id=9,10,11,12）时，由于它们的父节点已经在不断增长的 @pids 集合里了，所以也会被识别出来。这个过程就像涟漪一样扩散开，直到找不到新的子节点为止。

4. 高级技巧与实战变种

掌握了基础模板，我们来看看如何应对更复杂的实际需求。

4.1 查询包含自身节点

有时候，我们需要的不仅仅是子节点，而是包含起始节点在内的整个子树。只需要修改初始化变量和过滤条件即可。

-- 查询id=2的节点及其所有后代
SELECT
    id,
    name
FROM (
    SELECT
        t1.id,
        t1.name,
        IF(
            FIND_IN_SET(t1.parent_id, @pids) > 0 OR t1.id = 2, -- 增加对自身ID的判断
            @pids := CONCAT(@pids, ',', t1.id),
            -1
        ) AS is_child
    FROM
        (SELECT id, name, parent_id FROM department ORDER BY parent_id, id) t1,
        (SELECT @pids := '') t2 -- 初始化为空，或一个不可能的值
) t3
WHERE is_child != -1;

关键改动在 IF 条件中增加了 OR t1.id = 2，使得起始节点自身在第一轮判断中就能被加入集合。

4.2 获取完整的层级路径

光有节点列表还不够，我们常常需要知道每个节点在树中的完整路径，例如“集团公司 / 技术研发中心 / 后端开发部 / Java服务组”。这需要在递归过程中记录路径信息。我们可以使用另一个变量 @path 来串联名称。

SELECT
    id,
    name,
    node_path
FROM (
    SELECT
        t1.id,
        t1.name,
        @path AS old_path,
        IF(
            FIND_IN_SET(t1.parent_id, @pids) > 0,
            CONCAT(@path := CONCAT(@path, ' / ', t1.name), -- 更新路径变量
                   @pids := CONCAT(@pids, ',', t1.id)),
            -1
        ) AS is_child,
        @path AS node_path -- 最终路径
    FROM
        (SELECT id, name, parent_id FROM department ORDER BY parent_id, id) t1,
        (SELECT @pids := '2', @path := (SELECT name FROM department WHERE id = 2)) t2 -- 初始化路径为根节点名
) t3
WHERE is_child != -1;

这个查询会额外返回一列 node_path，显示从起始节点到当前节点的名称路径。注意，变量赋值和使用的顺序需要仔细设计。

4.3 性能考量与限制

虽然这个技巧很强大，但在使用时必须心中有数：

• 变量赋值顺序：MySQL中 SELECT 里用户变量的赋值顺序并不完全确定，尤其是在复杂查询中。我们使用的 ORDER BY parent_id, id 和特定的查询结构，是为了尽可能保证处理顺序符合树形遍历的逻辑。在极少数边缘情况下，可能需要调整排序方式。
• 结果集排序：查询结果默认是按照 t1 子查询的排序（parent_id, id）输出的，这通常是一种广度优先的展示。如果你需要严格的层级深度顺序，可能需要在最外层再进行排序，或者通过维护一个深度变量在递归过程中实现。
• 大数据量性能：FIND_IN_SET 函数对字符串的操作，在数据量很大（例如数万节点）时可能成为性能瓶颈。它本质上是线性查找。对于超大型树，这种纯SQL方案可能力不从心，需要考虑在数据库设计时引入“路径枚举”（如 1/2/5/）或“闭包表”等专门模型，或者在应用层进行递归查询。
• MySQL 8.0+ 的更好选择：如果你的环境是MySQL 8.0或更高版本，强烈建议使用标准的 WITH RECURSIVE 语法。它更清晰、更标准，且通常由查询优化器更好地处理。例如，实现相同功能的查询可以这样写：

  WITH RECURSIVE sub_tree AS (
      -- 锚点成员：起始节点
      SELECT id, name, parent_id, 1 as level
      FROM department
      WHERE id = 2
      UNION ALL
      -- 递归成员：连接子节点
      SELECT d.id, d.name, d.parent_id, st.level + 1
      FROM department d
      INNER JOIN sub_tree st ON d.parent_id = st.id
  )
  SELECT id, name, level FROM sub_tree ORDER BY level, id;
  

  这个查询不仅更容易理解和维护，还能轻松获取每个节点的深度（level）。

5. 封装成可复用的存储过程或函数

在项目中，我们肯定不希望每次查询都写这么长一串SQL。将其封装成存储过程，是提升开发效率的最佳实践。下面是一个通用的存储过程示例，它接受一个起始节点ID作为参数，并返回该节点的所有后代节点信息。

DELIMITER //

CREATE PROCEDURE `GetAllSubNodes`(IN root_id INT)
BEGIN
    -- 声明临时表存储结果，避免变量作用域问题
    DROP TEMPORARY TABLE IF EXISTS tmp_result;
    CREATE TEMPORARY TABLE tmp_result (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        parent_id INT,
        depth INT DEFAULT 0
    );

    -- 初始化变量
    SET @pids = CAST(root_id AS CHAR);
    SET @depth = 0;

    -- 插入根节点（如果需要）
    -- INSERT INTO tmp_result SELECT id, name, parent_id, @depth FROM department WHERE id = root_id;

    WHILE @pids IS NOT NULL AND @pids != '' DO
        -- 找出当前@pids中所有节点的直接子节点
        INSERT INTO tmp_result
        SELECT d.id, d.name, d.parent_id, @depth + 1
        FROM department d
        WHERE FIND_IN_SET(d.parent_id, @pids) > 0
          AND NOT EXISTS (SELECT 1 FROM tmp_result tr WHERE tr.id = d.id); -- 防止重复

        -- 准备下一轮循环的pid集合：本次新插入的所有节点ID
        SET @pids = NULL;
        SELECT GROUP_CONCAT(id) INTO @pids FROM tmp_result WHERE depth = @depth + 1;

        SET @depth = @depth + 1;
    END WHILE;

    -- 返回结果
    SELECT id, name, parent_id, depth FROM tmp_result ORDER BY depth, id;

    -- 清理临时表（可选，连接结束后会自动删除）
    DROP TEMPORARY TABLE IF EXISTS tmp_result;
END //

DELIMITER ;

使用这个存储过程就非常简单了：

-- 调用存储过程，查询id为2的节点所有子节点
CALL GetAllSubNodes(2);

这个存储过程版本使用了循环和临时表，逻辑上更接近我们手动递归的思路，避免了单条复杂SQL中变量赋值的顺序风险，尤其适合在需要获取节点深度（depth）的场景下使用。它通过 WHILE 循环，一层一层地向下探索树形结构，直到找不到新的子节点为止。

6. 避坑指南与最佳实践

在实际项目中使用递归查询，有几个坑点需要特别注意：

1. 循环引用检测：如果数据质量有问题，出现了A的父节点是B，B的父节点又是A的循环引用，那么基于会话变量的递归查询可能会陷入死循环（在存储过程循环版本中表现为无限循环）。在向树形表插入或更新数据时，应有业务逻辑或数据库触发器来防止这种情况。可以在递归查询中设置一个最大深度限制作为安全阀。

   -- 在存储过程的WHILE循环中增加深度限制
   IF @depth > 20 THEN -- 假设最大深度为20层
       LEAVE;
   END IF;
   

2. 索引是性能之友：确保 parent_id 字段上有索引。递归查询的本质是多次的父子关系查找，parent_id 上的索引能极大提升 FIND_IN_SET 子查询或 JOIN 操作的性能。

3. 会话变量的作用域：用户变量 @pids 的作用域是当前数据库会话。这意味着如果你在同一个会话中快速连续执行两次不同的递归查询，第二次查询可能会受到第一次查询残留变量值的影响。最佳实践是在查询开始时显式初始化所有用到的变量，就像我们在模板中做的那样 (SELECT @pids := 2) t2，或者在使用完变量后将其置为 NULL。

4. 考虑使用应用层递归：对于深度非常深、或者需要极其复杂过滤条件的树形查询，有时在应用层（如Java、Python代码中）进行递归查询可能更灵活、更容易调试。你可以先查询出所有数据，然后在内存中构建树形结构并进行遍历。这尤其适用于数据量不大但查询逻辑复杂的场景。

5. 升级到MySQL 8.0：如果项目有选择权，将数据库升级到MySQL 8.0并使用 WITH RECURSIVE 是长远来看最省心、最标准化的方案。它的语法清晰，是SQL标准的一部分，可移植性更好，也更容易被其他开发者理解。

纸上得来终觉浅，绝知此事要躬行。理解递归查询最好的方式，就是打开你的MySQL客户端，创建上面的示例表，把每一个SQL模板都亲手执行一遍，观察中间变量的变化和最终结果。当你需要处理一个多级分类的展开，或者计算一个部门的总人数（需要累加所有子部门）时，你会感谢自己掌握了这项技能。数据库的世界里，很多看似复杂的问题，往往就藏着一个精巧的解决方案，等着你去发现和运用。