快捷導(dǎo)航

Mysql實(shí)現(xiàn)遞歸樹查詢的使用示例

更新時(shí)間：2023年10月11日 10:42:03 作者：餅干，

Mysql我們可以使用遞歸查詢來構(gòu)建一個(gè)遞歸樹,本文就來介紹一下Mysql實(shí)現(xiàn)遞歸樹查詢的使用示例,具有一定的參考價(jià)值,感興趣的可以了解一下

大家好! 在我們?nèi)粘９ぷ髦?，?jīng)常會(huì)遇到一些問題,它們的一些解決方案通常會(huì)用到遞歸這一強(qiáng)大的技術(shù)手段。遞歸不僅能幫助我們更高效的解決問題,還可以使代碼更簡(jiǎn)介、更易于理解, 今天我來給大家分享如何在實(shí)際工作中使用mysql8+實(shí)現(xiàn)遞歸

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

在大型組織中,理解員工建的層級(jí)關(guān)系至關(guān)重要。無論是管理、報(bào)告和策劃，一個(gè)清晰的組織層級(jí)視圖都是必不可少的。但是如何有效地從一個(gè)大型、復(fù)雜的員工數(shù)據(jù)庫(kù)中提取到這樣的層級(jí)信息呢?

遞歸執(zhí)行分析

mysql8+遞歸查詢的實(shí)現(xiàn)是基于 WITH RECURSIVE語(yǔ)句。它從一個(gè)初始的"基礎(chǔ)情況"開始,然后不斷的重復(fù)或"遞歸" 的一個(gè)連接操作,直到滿足某個(gè)條件為止。
以下是遞歸查詢的基本結(jié)構(gòu):
1. 基礎(chǔ)情況: 這是遞歸的起點(diǎn),也就是根節(jié)點(diǎn),在我們場(chǎng)景中根節(jié)點(diǎn)是CEO或者它的上級(jí)為null。
2. 遞歸情況: 基于基礎(chǔ)情況，查詢將繼續(xù)擴(kuò)展，包括下一級(jí)的員工，然后類推。

WITH RECURSIVE hierarchy AS (
	-- 基礎(chǔ)情況
	...
	UNION ALL 
	-- 遞歸情況
	...
)
select xxx from hierarchy

demo數(shù)據(jù)

模擬插入10w條數(shù)據(jù), 層級(jí)最高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)
);
-- 新增根節(jié)點(diǎn)數(shù)據(jù)
INSERT INTO employees (name) VALUES ('CEO');
-- 創(chuàng)建執(zhí)行過程
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 ;
-- 執(zhí)行執(zhí)行過程
CALL InsertLargeAmountOfData();

查詢demo數(shù)據(jù)

WITH RECURSIVE hierarchy AS (
	-- 基礎(chǔ)情況
	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

結(jié)果

在以上圖例中,通過調(diào)整manager_id is null 可以配置要查詢某條數(shù)據(jù)及該數(shù)據(jù)所有子數(shù)據(jù)的查詢出來的內(nèi)容。

??Tip! 查詢的結(jié)果將以列表形式展現(xiàn)。若業(yè)務(wù)代碼中需要完整的樹狀結(jié)構(gòu)，可以在每個(gè)節(jié)點(diǎn)中關(guān)聯(lián)其上級(jí)節(jié)點(diǎn)。最后，通過取manager_id is null的記錄下的子節(jié)點(diǎn)數(shù)據(jù)，即可得到完整的樹狀數(shù)據(jù)。

擴(kuò)展

字段擴(kuò)展

在業(yè)務(wù)中常常會(huì)需要查詢某條記錄并返回該字段在組織層次中的位置，你可以參考以下代碼:

WITH RECURSIVE hierarchy AS (
    -- 基礎(chǔ)情況：從CEO開始
    SELECT id, name, manager_id, CAST(name AS CHAR(255)) AS hierarchy_path
    FROM employees
    WHERE manager_id IS NULL 
    UNION ALL
    -- 遞歸情況：為每個(gè)下級(jí)員工添加上級(jí)
    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
-- 查詢某條數(shù)據(jù)條件
WHERE name = 'L3-111'
ORDER BY id;

結(jié)果如下圖: