MySQL 遞歸查詢樹結構 SQL 實踐：8-9 層無壓力，但還有更優解

原始方案：多層 EXISTS 巢狀查詢

作者嘗試用層層 EXISTS 子查詢實現樹結構查詢，測試 8-9 層正常。SQL 結構如下：

SELECT tbl1.id, tbl1.name, tbl1.pid
FROM testTree tbl1
WHERE EXISTS (
  SELECT tbl2.pid FROM testTree tbl2,
  (SELECT tbl4.pid FROM testTree tbl4
   WHERE EXISTS (SELECT tbl5.pid FROM testTree tbl5 WHERE tbl4.id = tbl5.pid)
  ) tbl3
  WHERE tbl2.id = tbl3.pid
);

缺點分析

• 硬編碼層數：每增加一層需新增一層子查詢，維護極難。
• 效能差：多層關聯子查詢，優化器難以轉換為高效連接，大數據量極慢。
• 只能向下查詢：無法輕易實現「查詢某節點的所有祖先」或「全路徑」。

現代最佳實踐：CTE 遞歸查詢 (MySQL 8.0+)

WITH RECURSIVE tree_path AS (
  -- 錨點：根節點或指定起始節點
  SELECT id, name, pid, 1 AS lvl, CAST(name AS CHAR(1000)) AS path
  FROM testTree
  WHERE pid = 0  -- 或 WHERE id = :start_id

  UNION ALL

  -- 遞歸部分：子節點
  SELECT t.id, t.name, t.pid, tp.lvl + 1, CONCAT(tp.path, ' > ', t.name)
  FROM testTree t
  JOIN tree_path tp ON t.pid = tp.id
  WHERE tp.lvl < 20  -- 防無限遞歸
)
SELECT * FROM tree_path ORDER BY path;

優勢：單條 SQL 支援任意深度、可輸出層級與路徑、優化器可生成高效執行計劃。

MySQL 5.7 兼容方案

方案 A：儲存過程 + 臨時表

CREATE PROCEDURE GetTree(IN root_id INT)
BEGIN
  CREATE TEMPORARY TABLE tmp_tree (id INT, name VARCHAR(100), pid INT, lvl INT);
  INSERT INTO tmp_tree SELECT id, name, pid, 1 FROM testTree WHERE id = root_id;
  
  REPEAT
    INSERT INTO tmp_tree
    SELECT t.id, t.name, t.pid, tt.lvl + 1
    FROM testTree t JOIN tmp_tree tt ON t.pid = tt.id
    WHERE tt.lvl = (SELECT MAX(lvl) FROM tmp_tree);
  UNTIL ROW_COUNT() = 0 END REPEAT;
  
  SELECT * FROM tmp_tree;
  DROP TEMPORARY TABLE tmp_tree;
END

方案 B：路徑枚舉

在表中新增 path VARCHAR(1000) 儲存 /1/5/12/ 格式，查詢子樹：

SELECT * FROM testTree WHERE path LIKE '/1/5/%';

寫入時由應用層維護 path，查詢極快，適合讀多寫少場景。

方案 C：Closure Table (關係閉包表)

CREATE TABLE tree_closure (
  ancestor INT, descendant INT, depth INT,
  PRIMARY KEY (ancestor, descendant)
);
-- 觸發器/應用層維護所有祖先-後代關係
-- 查詢所有後代：SELECT descendant FROM tree_closure WHERE ancestor = 5;
-- 查詢所有祖先：SELECT ancestor FROM tree_closure WHERE descendant = 5;

總結建議

放棄硬編碼巢狀查詢，擁抱 CTE 或成熟設計模式，樹結構查詢不再是痛點。