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

MySQL 單條 SQL 實現遞歸查詢：變量迭代法詳解

編輯：MySQL 綜合教學

MySQL 單條 SQL 實現遞歸查詢：變量迭代法詳解

MySQL 8.0 前不支援 WITH RECURSIVE（CTE），但可利用用戶變量（@var）在單條 SQL 中完成樹形數據的遞歸遍歷。本文提供完整可運行示例。

一、測試數據準備

CREATE TABLE treenodes (
    id INT PRIMARY KEY,
    nodename VARCHAR(60),
    pid INT
);

INSERT INTO treenodes (id, nodename, pid) 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),
(18,'R',3),(19,'S',2),(20,'T',6),(21,'U',8);

結構：兩棵樹（根 1、8），最大深度 4 層。

二、核心 SQL

SELECT 
    id AS ID,
    pid AS 父ID,
    levels AS 父到子之間級數,
    paths AS 父到子路徑
FROM (
    SELECT 
        id, pid,
        @le := IF(pid = 0, 0,
            IF(LOCATE(CONCAT('|',pid,':'), @pathlevel) > 0,
                SUBSTRING_INDEX(SUBSTRING_INDEX(@pathlevel, CONCAT('|',pid,':'), -1), '|', 1) + 1,
                @le + 1)
        ) AS levels,
        @pathlevel := CONCAT(@pathlevel, '|', id, ':', @le, '|') AS pathlevel,
        @pathnodes := IF(pid = 0, ',0',
            CONCAT_WS(',',
                IF(LOCATE(CONCAT('|',pid,':'), @pathall) > 0,
                    SUBSTRING_INDEX(SUBSTRING_INDEX(@pathall, CONCAT('|',pid,':'), -1), '|', 1),
                    @pathnodes
                ),
                pid
            )
        ) AS paths,
        @pathall := CONCAT(@pathall, '|', id, ':', @pathnodes, '|') AS pathall
    FROM treenodes,
         (SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='') vv
    ORDER BY pid, id
) src
ORDER BY id;

三、變量作用解析

變量	含義
`@le`	當前節點層級（深度）
`@pathlevel`	記錄「節點ID:層級」的路徑串，用於查找父節點層級
`@pathnodes`	當前節點的完整祖先路徑（如 `,0,1,3,6`）
`@pathall`	全局記錄所有節點的「ID:祖先路徑」映射

四、關鍵邏輯

ORDER BY pid, id 保證父節點先於子節點處理，變量能正確累積
LOCATE(CONCAT('|',pid,':'), @pathlevel) 判斷父節點是否已處理
若父節點已處理，從 @pathlevel 取出父層級 +1；否則 @le+1（應不發生）
@pathnodes 同理構建祖先 ID 鏈

五、輸出結果

ID  父ID  級數  路徑
1   0     0     ,0
2   1     1     ,0,1
3   1     1     ,0,1
4   2     2     ,0,1,2
5   2     2     ,0,1,2
6   3     2     ,0,1,3
7   6     3     ,0,1,3,6
8   0     0     ,0
9   8     1     ,0,8
...
21  8     1     ,0,8

六、MySQL 8.0+ 現代寫法（CTE）

WITH RECURSIVE tree AS (
    SELECT id, pid, nodename, 0 AS lvl, CAST(id AS CHAR(1000)) AS path
    FROM treenodes WHERE pid = 0
    UNION ALL
    SELECT t.id, t.pid, t.nodename, tree.lvl + 1, CONCAT(tree.path, ',', t.id)
    FROM treenodes t JOIN tree ON t.pid = tree.id
)
SELECT * FROM tree ORDER BY id;

CTE 可讀性強、標準化，生產環境建議升級至 8.0+ 使用。老版本變量法仍是實用技巧。