MySQL递归查询树状表的子节点、父节点具体实现

下面就为你详细讲解MySQL递归查询树状表的子节点、父节点具体实现的完整攻略。

1. 背景

在数据库中，常常会有树状结构的数据存在，例如部门管理、商品分类等。如何高效地查询树状表的子节点和父节点是我们面临的一个问题。

2. 子节点查询

子节点查询就是查询某个节点下的所有子节点，也就是树状结构的所有下级节点。下面是一个示例，我们以部门管理为例：

假设我们的部门管理表（dept）如下所示：

id	name	parent_id
1	A	0
2	B	1
3	C	1
4	D	2
5	E	2
6	F	3

其中，parent_id为0的表示根节点，其他节点的parent_id表示该节点的父节点。

现在我们要查询id为1的节点下所有的子节点，包括1、2、3、4、5、6。我们可以使用递归查询来解决该问题。

具体的代码实现如下所示：

WITH RECURSIVE cte AS (
  SELECT id, name, parent_id
  FROM dept
  WHERE id = 1
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM dept d
  JOIN cte ON d.parent_id = cte.id
)
SELECT id, name, parent_id
FROM cte;

上面的SQL语句使用了WITH RECURSIVE语法，实现了递归查询。

首先，我们以id为1的节点开始查询，将它的id、name和parent_id存入结果集（上文代码中的SELECT语句）。

然后，我们再次查询它的子节点（上文代码中的UNION ALL语句），此时查询的条件是d.parent_id = cte.id，也就是要查询parent_id等于前一次查询结果的记录，即查询id为1的节点的子节点。

查询出来后，还要将结果拼接到之前的结果集中。

如果这样查询下去，基本就可以得到树状表的所有子节点。

3. 父节点查询

查询某个节点的所有父节点，也就是沿着树状结构向上查找。下面还是以部门管理为例：

假设我们现在要查询id为6的节点的所有父节点，包括6、3、1。

查询代码如下所示：

WITH RECURSIVE cte AS (
  SELECT id, name, parent_id
  FROM dept
  WHERE id = 6
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM dept d
  JOIN cte ON d.id = cte.parent_id
)
SELECT id, name, parent_id
FROM cte;

这个查询语句和查询子节点的语句类似，只不过查询条件变成了d.id = cte.parent_id，也就是查询id等于前一次查询结果的parent_id的记录，即查询id为6的节点的父节点。