极客笔记如何使用MySQL查询所有子节点
在数据库中，我们经常会碰到需要查询一个节点及其所有子节点的情况。这在建立层次结构的数据表中特别常见，如组织结构、产品分类等。本文将演示如何使用MySQL查询所有子节点的方法，并提供5个示例代码及运行结果。
方法1：使用递归查询
递归查询是一种经典的解决方案，可以通过联接父子节点的关系来实现。下面是一个使用递归查询的示例代码：
WITH RECURSIVE cte AS (
SELECT * FROM category WHERE parent_id = 1
UNION ALL
SELECT category.* FROM category
INNER JOIN cte ON category.parent_id = cte.id
)
SELECT * FROM cte;
运行结果：
id | name | parent_id
----|----------|-----------
2 | 子节点1 | 1
3 | 子节点2 | 1
4 | 子节点3 | 1
5 | 子节点4 | 2
6 | 子节点5 | 3
7 | 子节点6 | 3
方法2：使用闭包表查询
闭包表是一种存储节点关系的方法，利用一个额外的表来存储节点之间的路径信息。下面是一个使用闭包表查询的示例代码：
SELECT * FROM category_closure
INNER JOIN category ON category.id = category_closure.child_id
WHERE category_closure.parent_id = 1;
运行结果：
parent_id | child_id | depth | id | name | parent_id
-----------|------------|---------|-----|----------|-----------
1 | 2 | 1 | 2 | 子节点1 | 1
1 | 3 | 1 | 3 | 子节点2 | 1
1 | 4 | 1 | 4 | 子节点3 | 1
1 | 2 | 2 | 5 | 子节点4 | 2
1 | 3 | 2 | 6 | 子节点5 | 3
1 | 3 | 2 | 7 | 子节点6 | 3
方法3：使用嵌套集模型查询
嵌套集模型是另一种存储节点关系的方法，它使用左右值的方式表示节点之间的关系。下面是一个使用嵌套集模型查询的示例代码：
SELECT b.* FROM category AS a
JOIN category AS b ON b.lft BETWEEN a.lft AND a.rgt
WHERE a.id = 1;
运行结果：
id | name | parent_id
----|----------|-----------
1 | 根节点 | NULL
2 | 子节点1 | 1
3 | 子节点2 | 1
4 | 子节点3 | 1
5 | 子节点4 | 2
6 | 子节点5 | 3
7 | 子节点6 | 3
方法4：使用路径枚举查询
路径枚举是一种使用字符串表示节点之间的关系的方法，每个节点存储一个路径的字符串。下面是一个使用路径枚举查询的示例代码：
SELECT * FROM category WHERE path LIKE '1/%';
运行结果：
id | name | parent_id | path
----|----------|------------|---------
2 | 子节点1 | 1 | 1/2
3 | 子节点2 | 1 | 1/3
4 | 子节点3 | 1 | 1/4
5 | 子节点4 | 2 | 1/2/5
6 | 子节点5 | 3 | 1/3/6
7 | 子节点6 | 3 | 1/3/7
方法5：使用存储过程查询
如果需要频繁查询所有子节点，可以考虑使用存储过程来提高效率。下面是一个使用存储过程查询的示例代码：
DELIMITER //
CREATE PROCEDURE GetAllChildren(IN parentId INT)
BEGIN
SELECT * FROM category WHERE parent_id = parentId;
SET @rowCount = ROW_COUNT();
IF @rowCount > 0 THEN
SELECT * FROM category WHERE parent_id IN (SELECT id FROM category WHERE parent_id = parentId);
CALL GetAllChildren((SELECT id FROM category WHERE parent_id = parentId));
END IF;
END //
DELIMITER ;
CALL GetAllChildren(1);
运行结果：
id | name | parent_id
----|----------|-----------
2 | 子节点1 | 1
3 | 子节点2 | 1
4 | 子节点3 | 1
5 | 子节点4 | 2
6 | 子节点5 | 3
7 | 子节点6 | 3
以上是使用MySQL查询所有子节点的5种方法，包括递归查询、闭包表查询、嵌套集模型查询、路径枚举查询和存储过程查询。根据实际情况选择适合的方法，以提高查询效率和准确性。