mysql 5.x 快速查询所有子节点|极客笔记

mysql 5.x 快速查询所有子节点

在关系型数据库中，经常会遇到需要查询某个节点下的所有子节点的需求，比如查询某个部门下的所有员工，或者查询某个分类下的所有商品。本文将详细介绍如何在MySQL 5.x版本中快速查询所有子节点的方法。

准备工作

在开始之前，我们先准备一张示例表来演示查询所有子节点的方法。假设我们有一个名为category的表，该表中存储了一个分类树结构，其中包括id和parent_id两个字段，id表示节点的id，parent_id表示节点的父节点id。示例表结构如下：

CREATE TABLE category (
    id INT,
    parent_id INT,
    name VARCHAR(50)
);

INSERT INTO category VALUES
(1, NULL, 'Root'),
(2, 1, 'A'),
(3, 1, 'B'),
(4, 2, 'A1'),
(5, 2, 'A2'),
(6, 4, 'A1.1'),
(7, 4, 'A1.2'),
(8, 6, 'A1.1.1'),
(9, 6, 'A1.1.2');

方法一：递归查询

一种常见的方法是使用递归查询来获取所有子节点。递归查询的实现需要使用MySQL的存储过程或者递归查询语句。下面是一个使用存储过程实现递归查询的示例：

DELIMITER //

CREATE PROCEDURE GetCategoryPath(IN rootId INT)
BEGIN
    CREATE TEMPORARY TABLE temp_result AS
    SELECT * FROM category WHERE id = rootId;

    CREATE TEMPORARY TABLE temp_children AS
    SELECT * FROM category WHERE parent_id = rootId;

    IF (SELECT COUNT(*) FROM temp_children) > 0 THEN
        DECLARE done INT DEFAULT 0;
        DECLARE childId INT;
        DECLARE cur CURSOR FOR SELECT id FROM temp_children;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        OPEN cur;

        read_loop: LOOP
            FETCH cur INTO childId;
            IF done = 1 THEN
                LEAVE read_loop;
            END IF;

            CALL GetCategoryPath(childId);
        END LOOP;

        CLOSE cur;
    END IF;

    SELECT * FROM temp_result;
END//

DELIMITER ;

CALL GetCategoryPath(1);

在上面的示例中，首先创建了一个存储过程GetCategoryPath，该存储过程接受一个参数rootId，表示根节点的id。存储过程中创建了两个临时表temp_result和temp_children，分别用于存储当前节点和子节点信息。然后通过递归调用GetCategoryPath存储过程，逐级查询子节点，并最终返回所有子节点的信息。

运行以上代码后，将会得到结果：

+----+-----------+--------+
| id | parent_id | name   |
+----+-----------+--------+
|  1 | NULL      | Root   |
|  2 | 1         | A      |
| 4  | 2         | A1     |
| 6  | 4         | A1.1   |
| 8  | 6         | A1.1.1 |
| 9  | 6         | A1.1.2 |
| 7  | 4         | A1.2   |
| 5  | 2         | A2     |
| 3  | 1         | B      |
+----+-----------+--------+

方法二：使用临时表和循环

除了使用存储过程实现递归查询外，还可以使用临时表和循环的方式来查询所有子节点。这种方法比较直接和简单，不需要使用存储过程。下面是一个使用临时表和循环实现子节点查询的示例：

CREATE PROCEDURE GetCategoryPath2(IN rootId INT)
BEGIN
    CREATE TEMPORARY TABLE temp_result AS
    SELECT * FROM category WHERE id = rootId;

    CREATE TEMPORARY TABLE temp_queue AS
    SELECT id FROM category WHERE parent_id = rootId;

    WHILE (SELECT COUNT(*) FROM temp_queue) > 0 DO
        CREATE TEMPORARY TABLE temp_queue_tmp AS
        SELECT id FROM category WHERE parent_id IN (SELECT * FROM temp_queue);

        TRUNCATE TABLE temp_queue;

        INSERT INTO temp_queue SELECT * FROM temp_queue_tmp;
    END WHILE;

    INSERT INTO temp_result SELECT * FROM category WHERE id IN (SELECT * FROM temp_queue);

    SELECT * FROM temp_result;

    DROP TEMPORARY TABLE temp_result, temp_queue, temp_queue_tmp;
END//

DELIMITER ;

CALL GetCategoryPath2(1);

在上面的示例中，首先创建了一个存储过程GetCategoryPath2，该存储过程与方法一类似，使用了临时表temp_result和temp_queue，通过循环的方式逐级查询子节点，并最终返回所有子节点的信息。

运行以上代码后，将会得到和方法一相同的结果。