MySql8 WITH RECURSIVE递归查询父子集的方法

当我们需要查询数据中某个节点的所有子节点或父节点时，使用递归查询是一种非常方便的解决方案。MySQL 8 提供了一个WITH RECURSIVE语法来实现递归查询。下面是递归查询父子集的完整攻略：

1. 建立测试数据库

为了演示示例，我们需要先建立一个测试数据库，并在该数据库中建立一个包含parent_id字段的表。

CREATE DATABASE test;
USE test;

CREATE TABLE nodes(
id INT PRIMARY KEY,
name VARCHAR(20),
parent_id INT DEFAULT NULL
);

INSERT INTO nodes(id, name, parent_id) VALUES(1, 'Node-1', NULL);
INSERT INTO nodes(id, name, parent_id) VALUES(2, 'Node-1-1', 1);
INSERT INTO nodes(id, name, parent_id) VALUES(3, 'Node-1-2', 1);
INSERT INTO nodes(id, name, parent_id) VALUES(4, 'Node-1-1-1', 2);
INSERT INTO nodes(id, name, parent_id) VALUES(5, 'Node-1-1-2', 2);
INSERT INTO nodes(id, name, parent_id) VALUES(6, 'Node-1-2-1', 3);
INSERT INTO nodes(id, name, parent_id) VALUES(7, 'Node-1-2-2', 3);

以上代码建立了一个名为test的数据库，并在该数据库中建立了一个名为nodes的表。表中包含id、name、parent_id三个字段。其中parent_id字段用来存储节点的父节点id。数据如下：

id	name	parent_id
1	Node-1	NULL
2	Node-1-1	1
3	Node-1-2	1
4	Node-1-1-1	2
5	Node-1-1-2	2
6	Node-1-2-1	3
7	Node-1-2-2	3

2. 查询子节点集

我们现在需要查询id=2的节点的所有子节点。以下是使用递归查询查询子节点集的示例代码：

WITH RECURSIVE cte AS (
SELECT id, name, parent_id FROM nodes WHERE id = 2
UNION ALL
SELECT n.id, n.name, n.parent_id FROM nodes n JOIN cte ON cte.id = n.parent_id
)
SELECT * FROM cte;

以上代码中，我们创建了一个CTE(公共表表达式) cte。该CTE包含两个部分，第一个部分是查询id=2的节点信息，第二个部分使用JOIN语法连接nodes表和cte表，实现递归查询。执行以上查询后，我们可以得到以下结果：

id	name	parent_id
2	Node-1-1	1
4	Node-1-1-1	2
5	Node-1-1-2	2

结果包含了id=2节点的所有子节点的信息。可以看出，递归查询的核心语法就是UNION ALL和JOIN语句的结合使用。

3. 查询父节点集

我们现在需要查询id=4的节点的所有父节点。以下是使用递归查询查询父节点集的示例代码：

WITH RECURSIVE cte AS (
SELECT id, name, parent_id FROM nodes WHERE id = 4
UNION ALL
SELECT n.id, n.name, n.parent_id FROM nodes n JOIN cte ON n.id = cte.parent_id
)
SELECT * FROM cte;

以上代码中，我们创建了一个CTE cte。该CTE包含两个部分，第一个部分是查询id=4的节点信息，第二个部分使用JOIN语法连接nodes表和cte表，实现递归查询。执行以上查询后，我们可以得到以下结果：