MySQL 遞歸查詢樹狀結構所有子節點：三種存儲過程方案完整實現

問題背景

MySQL 無 CONNECT BY (Oracle) / WITH RECURSIVE (8.0 前)，需自行實現樹狀結構遞歸查詢。

測試數據準備

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 (無遞歸深度限制，但有長度限制)

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 ;

-- 使用
SELECT * FROM treeNodes WHERE FIND_IN_SET(id, getChildLst(1));
-- 返回 1,2,3,4,5,6,7

優缺點

• ✅ 無遞歸深度限制 (while 循環)
• ✅ 單函數調用簡潔
• ❌ 返回 VARCHAR 最大 65535，深層大樹會截斷
• ❌ MySQL 5.7 不支援函數遞歸調用

方案二：存儲過程遞歸 + 臨時表 (支援深度顯示，有 255 層限制)

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 ;

-- 設置遞歸深度上限 (默認 0 禁用，最大 255)
SET MAX_SP_RECURSION_DEPTH = 100;

-- 調用
CALL showChildLst(1);

優缺點

• ✅ 返回深度、排序號，便於樹形展示
• ✅ 可 JOIN 臨時表做關聯查詢
• ❌ 遞歸深度限制 255 層
• ❌ 臨時表同一語句不可多次引用限制

方案三：普通表 + 迴圈迭代 (無遞歸限制，需手動清理)

DELIMITER //
CREATE PROCEDURE showTreeNodes(IN rootId INT)
BEGIN
    DECLARE lvl INT DEFAULT 0;
    
    DROP TABLE IF EXISTS tmpLst;
    CREATE TABLE tmpLst (
        id INT,
        nLevel INT,
        sCort VARCHAR(8000)
    );
    
    INSERT INTO tmpLst SELECT id, lvl, CAST(id AS CHAR) 
    FROM treeNodes WHERE pid = rootId;
    
    WHILE ROW_COUNT() > 0 DO
        SET lvl = lvl + 1;
        INSERT INTO tmpLst 
        SELECT A.id, lvl, CONCAT(B.sCort, ',', A.id)
        FROM treeNodes A JOIN tmpLst B ON A.pid = B.id AND B.nLevel = lvl - 1;
    END WHILE;
END //
DELIMITER ;

-- 調用
CALL showTreeNodes(0);

-- 格式化輸出
SELECT CONCAT(SPACE(B.nLevel*2), '+--', A.nodename)
FROM treeNodes A JOIN tmpLst B ON A.id = B.id
ORDER BY B.sCort;

優缺點

• ✅ 無遞歸深度限制
• ✅ 生成排序路徑，保證樹遍歷順序
• ❌ 使用普通表需手動清理 (或定時任務清理)
• ❌ sCort 長度限制 (8000)，極深樹可能不足

MySQL 8.0+ 原生遞歸 CTE (最佳方案)

WITH RECURSIVE tree_cte AS (
    -- 錨點：根節點
    SELECT id, nodename, pid, 0 AS depth, CAST(id AS CHAR) AS path
    FROM treeNodes WHERE pid = 0
    
    UNION ALL
    
    -- 遞歸：子節點
    SELECT t.id, t.nodename, t.pid, c.depth + 1, CONCAT(c.path, ',', t.id)
    FROM treeNodes t
    JOIN tree_cte c ON t.pid = c.id
)
SELECT * FROM tree_cte ORDER BY path;

方案選型決策表

總結

樹狀遞歸查詢在 MySQL 5.7 時代是「硬骨頭」，三種存儲過程方案各有取捨。**MySQL 8.0 CTE 徹底解決此痛點**，遷移 8.0 是根治之道。存量系統按「深度、性能、維護性」三角權衡選型，並建立「應用層遞歸為主、DB 層輔助」的架構原則。