MySQL 8.0 前不支持 WITH RECURSIVE（CTE 遞歸），但利用 會話用戶變量（@var）在單條 SELECT 中保持狀態，可將樹形遞歸轉為有序迭代，一條 SQL 完成全樹路徑與層級計算。本文提供完整建表、數據、查詢、結果演示。
CREATE TABLE `treenodes` (
`id` INT,
`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);
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 | 當前節點層級（根=0） | 根節點=0；非根節點：若父節點已處理（@pathlevel 包含父標記）則取父層級+1，否則@le+1（順序保證父先處理） |
@pathlevel | 累積「節點ID:層級」映射串 | CONCAT(@pathlevel, '|', id, ':', @le, '|')，供子節點查找父層級 |
@pathnodes | 當前節點從根到自身的路徑（ID列表） | 根=',0'；非根：若父路徑已知則繼承父路徑+自身pid，否則繼承上一行@pathnodes+pid |
@pathall | 累積所有節點的路徑映射串 | CONCAT(@pathall, '|', id, ':', @pathnodes, '|')，供後續節點查找父路徑 |
**父節點必須在子節點之前被讀取**，否則父信息未寫入變量，子節點無法正確繼承層級與路徑。若表無序，需先建臨時表按 pid, 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 ... ... ... ...
WITH RECURSIVE cte AS (SELECT ... UNION ALL SELECT ... JOIN cte ...) SELECT * FROM cte; 標準、可優化、易維護。這個技巧展示了 MySQL 用戶變量在單語句中模擬狀態機的威力，也是理解「遞歸轉迭代」思想的經典案例。
