如何查询mysql所有子节点

一、什么是子节点

1、在数据库中，一般我们会采用树形结构来存储数据，每个节点都有若干个子节点；

2、子节点指的是在树结构中，当前节点的下一级节点。

二、如何查询一个节点的所有子节点

在mysql中，我们可以通过自关联的方式来查询一个节点的所有子节点。比如，我们有一张表叫做"category"，它的字段如下：

| id | name       | parent_id |
|----|------------|-----------|
| 1  | 电器       | 0         |
| 2  | 手机       | 1         |
| 3  | 平板电脑   | 1         |
| 4  | 游戏机     | 1         |
| 5  | 华为       | 2         |
| 6  | 小米       | 2         |
| 7  | iPad      | 3         |
| 8  | Switch    | 4         |
| 9  | PS5       | 4         |
| 10 | Xbox Series X | 4     |

如果要查询电器这个节点的所有子节点，可以使用如下的SQL语句：

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

上述SQL语句中，WITH RECURSIVE关键字表示我们要使用递归查询，cte是一个递归查询的临时表，我们先定义了一个初始节点，然后不断地与子节点进行连接操作，直到查询出所有的子节点。

三、如何查询多个节点的所有子节点

如果要查询多个节点的所有子节点，可以使用IN关键字。比如，我们要查询电器、手机和游戏机这三个节点的所有子节点，可以使用如下的SQL语句：

WITH RECURSIVE cte AS (
  SELECT id, name, parent_id
  FROM category
  WHERE id IN (1,2,4)
  UNION ALL
  SELECT child.id, child.name, child.parent_id
  FROM category child
  JOIN cte parent ON parent.id = child.parent_id
)
SELECT * FROM cte;

四、如何查询所有节点的子节点

如果要查询所有节点的子节点，可以不指定初始节点。比如，我们要查询所有电器类别的子节点，可以使用如下的SQL语句：

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

上述SQL语句中，我们指定初始节点的parent_id为1，表示要查询所有电器类别的子节点。

五、如何查询所有节点及其子节点的深度

如果在查询子节点时，希望能够同时将每个子节点的深度一起查询出来，可以使用如下的SQL语句：

WITH RECURSIVE cte(id, name, parent_id, depth) AS (
  SELECT id, name, parent_id, 1
  FROM category
  WHERE parent_id = 1
  UNION ALL
  SELECT child.id, child.name, child.parent_id, parent.depth + 1
  FROM category child
  JOIN cte parent ON parent.id = child.parent_id
)
SELECT * FROM cte;

上述SQL语句中，我们在cte表中增加了一个depth字段，表示每个节点的深度，初始节点的深度为1，每个子节点的深度都是其父节点的深度+1。

六、如何查询所有节点及其子节点的叶节点

如果在查询子节点时，希望将每个节点的叶节点一起查询出来，可以使用如下的SQL语句：

WITH RECURSIVE cte(id, name, parent_id, path) AS (
  SELECT id, name, parent_id, CAST(name as char(200))
  FROM category
  WHERE parent_id = 1
  UNION ALL
  SELECT child.id, child.name, child.parent_id, CONCAT(parent.path, ' >> ', child.name)
  FROM category child
  JOIN cte parent ON parent.id = child.parent_id
)
SELECT id, name, path
FROM cte
WHERE NOT EXISTS (
  SELECT 1 FROM category child WHERE child.parent_id = cte.id
);

上述SQL语句中，我们在cte表中增加了一个path字段，表示每个节点到根节点的路径，通过在path中查找">>"字符串，可以得到每个节点的深度。同时在筛选叶节点时，使用NOT EXISTS子查询来排除含有子节点的节点。

七、总结

本文介绍了如何在mysql中查询所有子节点，包括如何查询一个、多个或者所有节点的子节点，以及如何查询每个节点的深度和叶节点。这些查询方法可以为我们在实际项目中处理树形结构的数据提供帮助。