在数据库应用中，层级数据结构十分常见，公司的组织架构可以表示为树状结构，其中每个员工都有一个上级经理，在这种情况下，我们经常需要查询一个节点及其所有下属节点，为了实现这一目标，MySQL提供了强大的递归查询功能，通过递归查询可以轻松获取所有层级的子节点，本文将详细介绍如何使用MySQL进行递归查询以获取所有子节点，并提供相关示例和解释。

什么是递归查询？

递归查询是一种在查询过程中自我调用的方法，特别适用于处理具有层级关系的数据，在MySQL中，递归查询通常用于从层次结构的数据表中检索数据，例如组织结构、产品分类等。

使用递归公用表表达式（CTE）

MySQL从8.0版本开始支持递归公用表表达式（Recursive Common Table Expressions, CTE），这使得递归查询变得简单而直观，以下是一个基本的递归查询示例：

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name
    FROM category
    WHERE parent_id = ?  -- 指定要查询的父节点ID
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM category c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

在这个例子中，cte是一个递归公用表表达式，它首先选择指定的父节点，然后通过自连接不断查找子节点，直到没有更多子节点为止。

示例表结构和数据

假设我们有一个名为category的表，表结构如下：

CREATE TABLE category (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(255),
    FOREIGN KEY (parent_id) REFERENCES category(id)
);

并且表中有以下数据：

INSERT INTO category (id, parent_id, name) VALUES
(1, NULL, '根节点'),
(2, 1, '子节点 1'),
(3, 1, '子节点 2'),
(4, 2, '子节点 1-1'),
(5, 2, '子节点 1-2'),
(6, 3, '子节点 2-1'),
(7, 3, '子节点 2-2');

递归查询所有子节点

为了查询ID为1的节点的所有子节点，我们可以使用以下SQL语句：

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name
    FROM category
    WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM category c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

运行结果将是：

+----+------------+---------+
| id | parent_id | name    |
+----+------------+---------+
|  1 |      NULL | 根节点  |
|  2 |         1 | 子节点 1|
|  3 |         1 | 子节点 2|
|  4 |         2 | 子节点 1-1|
|  5 |         2 | 子节点 1-2|
|  6 |         3 | 子节点 2-1|
|  7 |         3 | 子节点 2-2|
+----+------------+---------+

这个结果显示了ID为1的节点及其所有子节点。

优化递归查询

虽然递归查询功能强大，但在处理大量数据时可能会遇到性能问题，以下是一些优化建议：

1、索引：确保在parent_id列上建立索引，以加快连接速度。

2、限制递归深度：如果层级结构较深，可以使用WITH RECURSIVE的MAXDEPTH选项限制递归深度，避免无限递归。

3、减少数据集：尽量在递归前通过WHERE子句过滤掉不需要的数据，减少递归处理的数据量。

4、分批处理：对于非常大的数据集，可以考虑分批次处理，每次只递归一部分数据。

MySQL的递归查询功能为我们处理层级数据提供了强大的工具，通过递归公用表表达式（CTE），我们可以简洁地查询一个节点及其所有子节点，在实际应用中，需要注意性能优化，以确保查询效率，希望本文能帮助你更好地理解和应用MySQL的递归查询功能。