MySQL查询所有子节点|极客笔记

MySQL查询所有子节点

1. 简介

在数据库中，我们经常会遇到需要查询树形结构数据的场景。树形结构数据是一种常见的数据结构，它由父节点和子节点组成，每个节点可以有多个子节点，但只能有一个父节点。

MySQL作为一种常用的关系型数据库，也可以存储和查询树形结构数据。本文将详细介绍如何使用MySQL查询树形结构数据中的所有子节点。

2. 数据模型

在开始查询之前，我们首先需要创建一个具有树形结构的数据模型。在本文中，我们将使用一个简单的示例来说明。

假设我们有一个部门表(department)，其中包含以下字段：

id: 部门的唯一标识
name: 部门的名称
parent_id: 父部门的id，根节点（最顶层的部门）的parent_id为NULL

表结构如下：

CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

为了简化示例，我们初始化部门表中的数据如下：

INSERT INTO department (id, name, parent_id) VALUES
(1, '总公司', NULL),
(2, '财务部', 1),
(3, '人力资源部', 1),
(4, '财务部一组', 2),
(5, '财务部二组', 2),
(6, '人力资源部一组', 3),
(7, '人力资源部二组', 3);

这样，我们就创建了一个包含树形结构的部门表。

3. 查询所有子节点

现在，我们可以开始查询部门表中的所有子节点。为了实现这个功能，在MySQL中，我们可以使用递归查询或者使用一条多级联接查询语句来实现。

3.1 递归查询

递归查询是使用递归算法来查询树形结构数据的一种常见方法。

首先，我们创建一个存储过程来实现递归查询。下面是一个示例：

DELIMITER //

CREATE PROCEDURE get_child_departments(IN p_parent_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE temp_id INT;
    DECLARE cur_depth INT DEFAULT 0;
    DECLARE cur_parent_id INT DEFAULT p_parent_id;
    DECLARE child_departments CURSOR FOR
        SELECT id FROM department WHERE parent_id = cur_parent_id;

    -- 创建临时表存储查询结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_result (id INT, depth INT);

    -- 开始递归查询
    OPEN child_departments;
    read_loop: LOOP
        FETCH child_departments INTO temp_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 递归插入子节点数据到临时表
        INSERT INTO temp_result (id, depth) VALUES (temp_id, cur_depth);
        CALL get_child_departments(temp_id);
    END LOOP;
    CLOSE child_departments;

    -- 查询结果
    SELECT d.id, d.name, d.parent_id, tr.depth FROM department d
    JOIN temp_result tr ON d.id = tr.id;

    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_result;
END //

DELIMITER ;

我们将递归查询实现在了一个存储过程中，这样可以方便地重复使用。存储过程的参数是p_parent_id，即要查询的父节点的id。存储过程中的变量done用来判断是否完成查询，temp_id用来暂存查询结果。cur_depth和cur_parent_id分别用来记录当前节点的深度和父节点的id。

在存储过程中，我们创建了一个临时表temp_result来存储查询结果。然后使用游标child_departments来查询当前父节点的所有子节点。在游标循环的每一次迭代中，我们插入子节点数据到临时表，并递归调用存储过程传递子节点作为新的父节点进行查询。

最后，我们在存储过程的末尾查询临时表temp_result和部门表department的联接结果，并返回查询结果。注意，我们使用了JOIN关键字来进行多表联接。

现在，我们调用存储过程来查询所有子节点。下面是一个示例：

CALL get_child_departments(1);

这条命令将会查询id为1的节点（总公司）的所有子节点，并返回结果。运行结果如下：

+----+----------+-----------+-------+
| id | name     | parent_id | depth |
+----+----------+-----------+-------+
| 2  | 财务部   | 1         | 1     |
| 3  | 人力资源部 | 1         | 1     |
| 4  | 财务部一组 | 2         | 2     |
| 5  | 财务部二组 | 2         | 2     |
| 6  | 人力资源部一组 | 3       | 2     |
| 7  | 人力资源部二组 | 3       | 2     |
+----+----------+-----------+-------+

这个结果展示了总公司(1)的所有子节点，包括财务部、人力资源部和它们的子节点。

3.2 多级联接查询

除了递归查询之外，我们还可以使用一条多级联接查询语句来实现查询所有子节点的功能。

下面是一条多级联接查询语句的示例：

SELECT d1.id, d1.name, d1.parent_id, d2.id, d2.name, d2.parent_id 
FROM department d1
LEFT JOIN department d2 ON d2.parent_id = d1.id
WHERE d1.parent_id = 1;

这条查询语句使用了两个部门表的别名（d1和d2）进行自联接。通过自联接查询，我们可以获取每个父节点的子节点。

在上面的查询语句中，我们通过WHERE子句来指定要查询的父节点的id为1。所以这个语句将会查询总公司(1)的所有子节点。运行结果如下：

+----+----------+-----------+----+------------+-----------+
| id | name     | parent_id | id | name       | parent_id |
+----+----------+-----------+----+------------+-----------+
| 2  | 财务部   | 1         | 4  | 财务部一组 | 2         |
| 2  | 财务部   | 1         | 5  | 财务部二组 | 2         |
| 3  | 人力资源部 | 1         | 6  | 人力资源部一组 | 3     |
| 3  | 人力资源部 | 1         | 7  | 人力资源部二组 | 3     |
+----+----------+-----------+----+------------+-----------+

这个结果展示了总公司(1)的所有子节点的信息，包括子节点的id、名称、父节点id等。