MySQL 遞迴查詢：樹狀結構子樹查詢完整解決方案

MySQL 遞迴查詢：樹狀結構子樹查詢完整解決方案

MySQL 8.0 前不支援 WITH RECURSIVE (CTE)，處理鄰接表 (Adjacency List) 模型的樹狀查詢需用儲存函數/程序迴圈。本文提供 5.7/8.0 雙版本解法，以「員工-主管」關係為例。

資料模型

CREATE TABLE Employee (
    SSN VARCHAR(64) PRIMARY KEY,
    Name VARCHAR(64),
    Designation VARCHAR(128),
    MSSN VARCHAR(64),  -- Manager SSN
    FOREIGN KEY (MSSN) REFERENCES Employee(SSN)
) ENGINE=InnoDB;

INSERT INTO Employee VALUES
('1','A','OWNER','1'),   -- 根節點，自己管理自己
('2','B','BOSS','1'),
('3','F','BOSS','1'),
('4','C','BOSS','2'),
('5','H','BOSS','2'),
('6','L','WORKER','2'),
('7','I','BOSS','2'),
('8','K','WORKER','3'),
('9','J','WORKER','7'),
('10','G','WORKER','5'),
('11','D','WORKER','4'),
('12','E','WORKER','4');

樹狀結構：

        A (OWNER)
      / | \
     B  F  (其他)
    /|\  \
   C H I  L
  /| |  \
 D E G   J
     |
     K

需求：給定節點名稱，輸出其完整子樹 (所有後代)

輸入 'B'，預期輸出 B 及其所有下層：C, H, I, L, D, E, G, J。

方案一：MySQL 8.0+ CTE 遞迴查詢 (標準、高效、可讀)

WITH RECURSIVE SubTree AS (
    -- 錨點：起始節點
    SELECT SSN, Name, MSSN, 0 AS lvl
    FROM Employee
    WHERE Name = 'B'
    
    UNION ALL
    
    -- 遞迴：尋找直屬下屬
    SELECT e.SSN, e.Name, e.MSSN, st.lvl + 1
    FROM Employee e
    INNER JOIN SubTree st ON e.MSSN = st.SSN
)
SELECT Name, lvl AS depth
FROM SubTree
ORDER BY lvl, Name;

結果：

+------+-------+
| Name | depth |
+------+-------+
| B    | 0     |
| C    | 1     |
| H    | 1     |
| I    | 1     |
| L    | 1     |
| D    | 2     |
| E    | 2     |
| G    | 2     |
| J    | 2     |
+------+-------+

方案二：MySQL 5.7 儲存函數迴圈 (生產環境可用)

DELIMITER $$

CREATE FUNCTION GetSubTree(root_name VARCHAR(64))
RETURNS VARCHAR(1024)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE rv VARCHAR(1024) DEFAULT '';
    DECLARE q VARCHAR(1024) DEFAULT '';
    DECLARE queue VARCHAR(1024) DEFAULT '';
    DECLARE queue_children VARCHAR(1024) DEFAULT '';
    DECLARE queue_names VARCHAR(1024) DEFAULT '';
    DECLARE queue_len INT DEFAULT 0;
    DECLARE pos INT;
    DECLARE root_ssn VARCHAR(64);
    DECLARE front_ssn VARCHAR(64);

    -- 取得根節點 SSN
    SELECT SSN INTO root_ssn FROM Employee WHERE Name = root_name AND Designation <> 'OWNER';
    IF root_ssn IS NULL THEN RETURN ''; END IF;

    SET queue = root_ssn;
    SET queue_len = 1;

    WHILE queue_len > 0 DO
        -- 取隊列首元素
        IF queue_len = 1 THEN
            SET front_ssn = queue;
            SET queue = '';
        ELSE
            SET pos = LOCATE(',', queue);
            SET front_ssn = LEFT(queue, pos - 1);
            SET queue = SUBSTR(queue, pos + 1);
        END IF;
        SET queue_len = queue_len - 1;

        -- 找直屬下屬
        SELECT IFNULL(GROUP_CONCAT(SSN), '') INTO queue_children
        FROM Employee WHERE MSSN = front_ssn AND Designation <> 'OWNER';
        
        SELECT IFNULL(GROUP_CONCAT(Name), '') INTO queue_names
        FROM Employee WHERE MSSN = front_ssn AND Designation <> 'OWNER';

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN SET queue_len = 0; END IF;
        ELSE
            -- 累加結果
            IF LENGTH(rv) = 0 THEN SET rv = queue_names;
            ELSE SET rv = CONCAT(rv, ',', queue_names); END IF;
            
            -- 入隊
            IF LENGTH(queue) = 0 THEN SET queue = queue_children;
            ELSE SET queue = CONCAT(queue, ',', queue_children); END IF;
            
            SET queue_len = LENGTH(queue) - LENGTH(REPLACE(queue, ',', '')) + 1;
        END IF;
    END WHILE;

    RETURN rv;
END$$

DELIMITER ;

呼叫函數

SELECT Name, GetSubTree(Name) AS SubTree
FROM Employee
WHERE Designation <> 'OWNER';

輸出：

+------+---------------------------+
| Name | SubTree                   |
+------+---------------------------+
| A    | B,F,C,H,L,I,K,D,E,G,J     |
| B    | C,H,L,I,D,E,G,J           |
| C    | D,E                       |
| H    | G                         |
| I    | J                         |
| ...  | ...                       |
+------+---------------------------+

方案三：路徑枚舉法 設計階段優化 (查詢極快)

在表中新增 path VARCHAR(1000) 欄位，儲存從根到該節點的路徑 (如 /1/2/4/)。

ALTER TABLE Employee ADD COLUMN path VARCHAR(1000);
-- 觸發器自動維護 path
UPDATE Employee SET path = CONCAT('/', SSN, '/') WHERE MSSN = SSN; -- 根
UPDATE Employee e JOIN Employee m ON e.MSSN = m.SSN 
SET e.path = CONCAT(m.path, e.SSN, '/') WHERE e.path IS NULL;

查詢子樹：

SELECT * FROM Employee 
WHERE path LIKE (SELECT CONCAT(path, '%') FROM Employee WHERE Name = 'B');

優點：單次索引掃描，無遞迴開銷。缺點：移動節點需更新整棵子樹 path。

方案四：巢狀集合模型

每節點儲存 lft, rgt 左右邊界值，子樹查詢：WHERE lft > parent.lft AND rgt < parent.rgt。適合讀多寫少場景。

效能與選型建議

常見陷阱

• 循環引用：資料髒污導致無限遞迴 → 加 MAX_DEPTH 限制或檢測 visited 集合。
• 根節點自關聯：MSSN = SSN 需特殊處理 (範例中 A 自己管自己)。
• GROUP_CONCAT 長度限制：預設 1024 字元，大樹需調大 group_concat_max_len。
• 字元集不一致：SSN/VARCHAR 比較隱含轉換導致索引失效。

MySQL 8.0 已原生支援 CTE，新專案請直接使用 WITH RECURSIVE；舊版本以儲存函數或路徑枚舉過渡，避免在應用層遞迴查詢造成 N+1 問題。