MySQL 递归CTE如何查询多级父子关系数据？

Question

在MySQL中，如何利用递归CTE查询多级父子关系数据是一个常见需求。例如，有一张包含`id`、`name`和`parent_id`字段的表，表示层级结构的数据。当需要获取某个节点的所有子节点时，可以使用递归CTE。但实际操作中可能遇到问题：如何正确设置递归终止条件？如果`parent_id`为NULL表示根节点，递归查询可能会导致无限循环或遗漏部分数据。此外，MySQL 8.0及以上版本才支持递归CTE，低版本无法使用。解决方法是确保初始查询和递归部分逻辑清晰，避免重复或遗漏数据，并验证递归层数是否符合预期。例如，查询根节点ID为1的所有子节点时，需明确递归条件`cte.id = table.parent_id`，同时处理`parent_id`为NULL的情况。

Answer 1

MySQL递归CTE查询多级父子关系数据详解

1. 问题背景与常见需求

在MySQL中，利用递归CTE（Common Table Expressions）查询多级父子关系数据是一种常见的需求。例如，有一张包含`id`、`name`和`parent_id`字段的表，表示层级结构的数据。这种表通常用于描述组织架构、文件目录或分类系统等场景。

当需要获取某个节点的所有子节点时，可以使用递归CTE。然而，在实际操作中可能会遇到以下问题：

• 如何正确设置递归终止条件？
• 如果`parent_id`为NULL表示根节点，递归查询可能会导致无限循环或遗漏部分数据。
• MySQL 8.0及以上版本才支持递归CTE，低版本无法使用。

2. 分析过程

为了更好地理解这些问题，我们需要从以下几个方面进行分析：

1. 初始查询和递归部分逻辑清晰性：确保递归起点和递归条件明确。
2. 避免重复或遗漏数据：递归过程中需要保证每个节点只被访问一次。
3. 验证递归层数是否符合预期：通过调试或日志记录来检查递归深度。

以查询根节点ID为1的所有子节点为例，递归条件应为`cte.id = table.parent_id`，同时需要处理`parent_id`为NULL的情况。

3. 解决方案

以下是解决上述问题的具体方法：

问题	解决方案
如何正确设置递归终止条件？	通过明确递归条件`cte.id = table.parent_id`，并在递归部分中加入终止条件`WHERE table.parent_id IS NOT NULL`。
`parent_id`为NULL表示根节点，如何避免无限循环或遗漏数据？	将根节点作为初始查询的一部分，并在递归部分中排除`parent_id`为NULL的节点。
MySQL 8.0以下版本不支持递归CTE怎么办？	可以使用其他方法（如存储过程或自定义函数）实现类似功能。

4. 示例代码

以下是一个完整的示例代码，展示如何使用递归CTE查询根节点ID为1的所有子节点：

WITH RECURSIVE cte AS (
    SELECT id, name, parent_id
    FROM tree
    WHERE id = 1 -- 初始查询：从根节点开始
    UNION ALL
    SELECT t.id, t.name, t.parent_id
    FROM tree t
    INNER JOIN cte ON cte.id = t.parent_id -- 递归条件
    WHERE t.parent_id IS NOT NULL -- 避免无限循环
)
SELECT * FROM cte;

5. 流程图

以下是递归CTE查询的流程图，帮助理解其执行过程：

graph TD
    A[初始查询] --> B[递归部分]
    B --> C{是否有匹配的子节点}
    C --是--> D[继续递归]
    C --否--> E[结束递归]