您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 實現樹狀結構所有子節點查詢的三種高效方法

編輯：MySQL 綜合教學

MySQL 樹狀結構子節點查詢完整指南

在資料庫設計中，樹狀結構（如分類目錄、組織架構、評論回覆等）是常見的資料模型。Oracle 提供了 CONNECT BY 關鍵字支援遞歸查詢，但 MySQL 直到 8.0 版本引入 CTE（通用表表達式）之前，原生不支援遞歸查詢。本文詳細介紹三種在 MySQL 中實現「查詢指定節點所有子孫節點」的經典方案，並附帶完整測試數據與程式碼。

測試環境與樣本數據

首先建立測試表 treeNodes 並插入 17 筆模擬數據，構成一棵最大深度為 4 層的樹：

CREATE TABLE treeNodes (
    id INT PRIMARY KEY,
    nodename VARCHAR(20),
    pid INT
);

INSERT INTO treeNodes VALUES
(1,'A',0),(2,'B',1),(3,'C',1),(4,'D',2),(5,'E',2),
(6,'F',3),(7,'G',6),(8,'H',0),(9,'I',8),(10,'J',8),
(11,'K',8),(12,'L',9),(13,'M',9),(14,'N',12),(15,'O',12),
(16,'P',15),(17,'Q',15);

樹形結構圖示：

1:A
+-- 2:B
|   +-- 4:D
|   +-- 5:E
+-- 3:C
    +-- 6:F
        +-- 7:G
8:H
+-- 9:I
|   +-- 12:L
|   |   +-- 14:N
|   |   +-- 15:O
|   |       +-- 16:P
|   |       +-- 17:Q
|   +-- 13:M
+-- 10:J
+-- 11:K

方法一：自定義函數 + FIND_IN_SET（適用於中小型樹）

建立一個函數 getChildLst(rootId)，利用 WHILE 迴圈配合 FIND_IN_SET 逐層收集子節點 ID，最終回傳逗號分隔的字串（如 $,1,2,3,4,5,6,7）。查詢時直接用 FIND_IN_SET(id, getChildLst(1)) 過濾。

DELIMITER //
CREATE FUNCTION `getChildLst`(rootId INT) RETURNS VARCHAR(1000)
BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);
    SET sTemp = '$';
    SET sTempChd = CAST(rootId AS CHAR);
    WHILE sTempChd IS NOT NULL DO
        SET sTemp = CONCAT(sTemp, ',', sTempChd);
        SELECT GROUP_CONCAT(id) INTO sTempChd
        FROM treeNodes WHERE FIND_IN_SET(pid, sTempChd) > 0;
    END WHILE;
    RETURN sTemp;
END//
DELIMITER ;

優點：語法簡單、無遞歸深度限制（受限於 max_sp_recursion_depth 255）。
缺點：回傳字串長度有上限（預設 1000，可調大但終有極限）；MySQL 5.x 不支援函數遞歸調用。

方法二：臨時表 + 存儲過程遞歸（支援深度追蹤）

建立兩個存儲過程：showChildLst 為入口，負責建立臨時表 tmpLst 並初始化；createChildLst 為遞歸過程，使用游標遍歷子節點，將節點 ID 與深度寫入臨時表。最後關聯原表輸出。

DELIMITER //
CREATE PROCEDURE showChildLst(IN rootId INT)
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst (
        sno INT PRIMARY KEY AUTO_INCREMENT,
        id INT, depth INT
    );
    DELETE FROM tmpLst;
    CALL createChildLst(rootId, 0);
    SELECT tmpLst.*, treeNodes.*
    FROM tmpLst JOIN treeNodes ON tmpLst.id = treeNodes.id
    ORDER BY tmpLst.sno;
END//

CREATE PROCEDURE createChildLst(IN rootId INT, IN nDepth INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE b INT;
    DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes WHERE pid = rootId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    INSERT INTO tmpLst VALUES (NULL, rootId, nDepth);
    OPEN cur1;
    FETCH cur1 INTO b;
    WHILE done = 0 DO
        CALL createChildLst(b, nDepth + 1);
        FETCH cur1 INTO b;
    END WHILE;
    CLOSE cur1;
END//
DELIMITER ;

呼叫 CALL showChildLst(1); 即可獲得含 depth 欄位的結果，方便前端縮排顯示。
注意：需先設定 SET max_sp_recursion_depth=12;（最大 255）才能正常遞歸。

方法三：普通表 + 迴圈迭代（無遞歸限制、效能較佳）

由於 MySQL 限制同一語句中不能多次引用臨時表，改用普通表 tmpLst。過程中維護 Level（層級）與 sCort（排序串），通過 WHILE ROW_COUNT() > 0 逐層向下擴展，直到無新增記錄為止。

DELIMITER //
DROP PROCEDURE IF EXISTS showTreeNodes_yongyupost2000//
CREATE PROCEDURE showTreeNodes_yongyupost2000(IN rootid INT)
BEGIN
    DECLARE Level INT;
    DROP TABLE IF EXISTS tmpLst;
    CREATE TABLE tmpLst (id INT, nLevel INT, sCort VARCHAR(8000));
    SET Level = 0;
    INSERT INTO tmpLst SELECT id, Level, ID FROM treeNodes WHERE PID = rootid;
    WHILE ROW_COUNT() > 0 DO
        SET Level = Level + 1;
        INSERT INTO tmpLst
        SELECT A.ID, Level, CONCAT(B.sCort, A.ID)
        FROM treeNodes A JOIN tmpLst B ON A.PID = B.ID AND B.nLevel = Level - 1;
    END WHILE;
END//
DELIMITER ;

執行後查詢：

CALL showTreeNodes_yongyupost2000(0);
SELECT CONCAT(SPACE(B.nLevel*2), '+--', A.nodename)
FROM treeNodes A JOIN tmpLst B ON A.ID = B.ID
ORDER BY B.sCort;

優點：無遞歸深度限制、可獲取遍歷順序與層級。
缺點：需手動清理普通表、佔用磁碟空間。