MySQL ConnectBy——从层次结构中检索数据|极客笔记

MySQL ConnectBy——从层次结构中检索数据

在数据库中，层次结构是一种常见的数据组织形式。例如，在组织架构、产品分类和地理区域等领域，层次结构都是常见的数据模型。对于这些层次结构的数据，我们通常需要查询某个节点及其子节点，或者查询某个节点的父节点等操作。在MySQL中，我们可以使用ConnectBy特性方便地从层次结构中检索数据。

1. 创建测试数据

为了演示MySQL ConnectBy的用法，我们首先需要创建一个测试数据集。假设我们要创建一个组织架构表，其中每个节点包含一个唯一的ID和一个父节点ID。下面是创建测试表和插入测试数据的示例代码：

CREATE TABLE organization (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(100)
);

INSERT INTO organization (id, parent_id, name) VALUES
    (1, NULL, '总公司'),
    (2, 1, '分公司1'),
    (3, 1, '分公司2'),
    (4, 2, '部门1'),
    (5, 2, '部门2'),
    (6, 3, '部门3'),
    (7, 3, '部门4');

2. 查询节点及其子节点

ConnectBy可以帮助我们查询某个节点及其所有子节点。下面是查询节点ID为2及其子节点的示例代码：

SELECT *
FROM organization
START WITH id = 2
CONNECT BY PRIOR id = parent_id;

运行结果如下：

+----+-----------+------------+
| id | parent_id | name       |
+----+-----------+------------+
|  2 |         1 | 分公司1    |
|  4 |         2 | 部门1      |
|  5 |         2 | 部门2      |
+----+-----------+------------+

从上面的结果可以看出，查询结果包括节点ID为2的分公司1以及其子节点部门1和部门2。

3. 查询节点的父节点

ConnectBy还可以帮助我们查询某个节点的父节点。下面是查询节点ID为4的父节点的示例代码：

SELECT *
FROM organization
START WITH id = 4
CONNECT BY id = PRIOR parent_id;

运行结果如下：

+----+-----------+------------+
| id | parent_id | name       |
+----+-----------+------------+
|  4 |         2 | 部门1      |
|  2 |         1 | 分公司1    |
|  1 |      NULL | 总公司     |
+----+-----------+------------+

从上面的结果可以看出，查询结果包括节点ID为4的部门1、它的父节点分公司1，以及分公司1的父节点总公司。

4. 查询节点的祖先节点

ConnectBy还可以帮助我们查询某个节点的所有祖先节点。下面是查询节点ID为5的所有祖先节点的示例代码：

SELECT *
FROM organization
START WITH id = 5
CONNECT BY PRIOR parent_id = id;

运行结果如下：

+----+-----------+------------+
| id | parent_id | name       |
+----+-----------+------------+
|  5 |         2 | 部门2      |
|  2 |         1 | 分公司1    |
|  1 |      NULL | 总公司     |
+----+-----------+------------+

从上面的结果可以看出，查询结果包括节点ID为5的部门2、它的父节点分公司1，以及分公司1的父节点总公司。

5. 查询整个层次结构

如果我们希望查询整个层次结构，可以使用ConnectBy查询所有节点及其子节点。下面是查询整个组织架构的示例代码：

SELECT *
FROM organization
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

运行结果如下：

+----+-----------+------------+
| id | parent_id | name       |
+----+-----------+------------+
|  1 |      NULL | 总公司     |
|  2 |         1 | 分公司1    |
|  4 |         2 | 部门1      |
|  5 |         2 | 部门2      |
|  3 |         1 | 分公司2    |
|  6 |         3 | 部门3      |
|  7 |         3 | 部门4      |
+----+-----------+------------+

从上面的结果可以看出，查询结果包括所有节点及其子节点，按照层次关系展示。