【Mysql实现递归树查询】

大家好! 在我们日常工作中，经常会遇到一些问题,它们的一些解决方案通常会用到递归这一强大的技术手段。递归不仅能帮助我们更高效的解决问题,还可以使代码更简介、更易于理解, 今天我来给大家分享如何在实际工作中使用mysql8+实现递归

💡Tip !!! 需要Mysql 8+上的版本支持

在大型组织中,理解员工建的层级关系至关重要。无论是管理、报告和策划，一个清晰的组织层级视图都是必不可少的。但是如何有效地从一个大型、复杂的员工数据库中提取到这样的层级信息呢?

递归执行分析

mysql8+递归查询的实现是基于 WITH RECURSIVE语句。它从一个初始的"基础情况"开始,然后不断的重复或"递归" 的一个连接操作,直到满足某个条件为止。
以下是递归查询的基本结构:
1. 基础情况: 这是递归的起点,也就是根节点,在我们场景中根节点是CEO或者它的上级为null。
2. 递归情况: 基于基础情况，查询将继续扩展，包括下一级的员工，然后类推。

WITH RECURSIVE hierarchy AS (
	-- 基础情况
	...
	UNION ALL 
	-- 递归情况
	...
)
select xxx from hierarchy
1
2
3
4
5
6
7
8

demo数据

模拟插入10w条数据, 层级最高6层

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);
-- 新增根节点数据
INSERT INTO employees (name) VALUES ('CEO');
-- 创建执行过程
DELIMITER $$
CREATE PROCEDURE InsertLargeAmountOfData()
BEGIN
    DECLARE id INT DEFAULT 2;
    DECLARE parentId INT DEFAULT 1;
    DECLARE counter INT DEFAULT 0;

    -- 第2层
    WHILE counter < 10 DO
        INSERT INTO employees (name, manager_id) VALUES (CONCAT('L2-', id), 1);
        SET id = id + 1;
        SET counter = counter + 1;
    END WHILE;

    SET counter = 0;
    -- 第3层
    WHILE counter < 100 DO
        SET parentId = 1 + FLOOR(counter / 10) + 1;
        INSERT INTO employees (name, manager_id) VALUES (CONCAT('L3-', id), parentId);
        SET id = id + 1;
        SET counter = counter + 1;
    END WHILE;

    SET counter = 0;
    -- 第4层
    WHILE counter < 1000 DO
        SET parentId = 11 + FLOOR(counter / 100);
        INSERT INTO employees (name, manager_id) VALUES (CONCAT('L4-', id), parentId);
        SET id = id + 1;
        SET counter = counter + 1;
    END WHILE;

    SET counter = 0;
    -- 第5层
    WHILE counter < 10000 DO
        SET parentId = 111 + FLOOR(counter / 1000);
        INSERT INTO employees (name, manager_id) VALUES (CONCAT('L5-', id), parentId);
        SET id = id + 1;
        SET counter = counter + 1;
    END WHILE;

    SET counter = 0;
    -- 第6层
    WHILE counter < 88889 DO
        SET parentId = 1111 + FLOOR(counter / 10000);
        INSERT INTO employees (name, manager_id) VALUES (CONCAT('L6-', id), parentId);
        SET id = id + 1;
        SET counter = counter + 1;
    END WHILE;

END$$
DELIMITER ;
-- 执行执行过程
CALL InsertLargeAmountOfData();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

查询demo数据

WITH RECURSIVE hierarchy AS (
	-- 基础情况
	select id,name,manager_id from employees where manager_id is null
	UNION ALL 
	-- 递归情况
	select e.* from employees as e  inner join hierarchy as h on e.manager_id = h.id
)
select id,name,manager_id from hierarchy
1
2
3
4
5
6
7
8

结果

在以上图例中,通过调整manager_id is null 可以配置要查询某条数据及该数据所有子数据的查询出来的内容。

💡Tip! 查询的结果将以列表形式展现。若业务代码中需要完整的树状结构，可以在每个节点中关联其上级节点。最后，通过取manager_id is null的记录下的子节点数据，即可得到完整的树状数据。

扩展

字段扩展

在业务中常常会需要查询某条记录并返回该字段在组织层次中的位置，你可以参考以下代码:

WITH RECURSIVE hierarchy AS (
    -- 基础情况：从CEO开始
    SELECT id, name, manager_id, CAST(name AS CHAR(255)) AS hierarchy_path
    FROM employees
    WHERE manager_id IS NULL 

    UNION ALL

    -- 递归情况：为每个下级员工添加上级
    SELECT e.id, e.name, e.manager_id, CONCAT(h.hierarchy_path, ' > ', e.name)
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT id, name, manager_id, hierarchy_path 
FROM hierarchy
-- 查询某条数据条件
WHERE name = 'L3-111'
ORDER BY id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

结果如下图: