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

MySQL 遞歸查詢樹形結構實戰：存儲函數 + FIND_IN_SET 實現無限級分類子孫節點查找

編輯：MySQL 綜合教學

MySQL 遞歸查詢樹形結構實戰

MySQL 8.0 前不支援 WITH RECURSIVE (CTE 遞歸)，傳統做法是**存儲函數 + 迴圈 + FIND_IN_SET** 迭代收集子孫節點 ID。本文提供完整建表、測試數據、函數定義與調用示例，兼容 MySQL 5.6/5.7。

測試表結構與數據 (行政區劃 3 級)

CREATE TABLE t_areainfo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    level INT DEFAULT 0,
    name VARCHAR(255),
    parentId INT,
    status INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入: 1 中國 -> 2 華北/3 華南 -> 4 北京 -> 5-35 北京各區 + 46-64 多省份
-- (完整 INSERT 语句见原文，共 64 条记录)

存儲函數：查詢指定節點及所有後代 ID

DELIMITER //
DROP FUNCTION IF EXISTS queryChildrenAreaInfo //
CREATE FUNCTION queryChildrenAreaInfo (areaId INT) RETURNS VARCHAR(4000)
BEGIN
    DECLARE sTemp VARCHAR(4000);      -- 累積結果集 (以 $ 開頭防空字串)
    DECLARE sTempChd VARCHAR(4000);   -- 當前層級子節點 ID 列表
    
    SET sTemp = '$';
    SET sTempChd = CAST(areaId AS CHAR);  -- 種子：根節點自身
    
    WHILE sTempChd IS NOT NULL DO
        SET sTemp = CONCAT(sTemp, ',', sTempChd);
        -- 查找下一層子節點
        SELECT GROUP_CONCAT(id) INTO sTempChd 
        FROM t_areainfo 
        WHERE FIND_IN_SET(parentId, sTempChd) > 0;
    END WHILE;
    
    RETURN sTemp;  -- 格式: $,1,2,4,5,6...
END //
DELIMITER ;

調用示例

-- 1. 取得節點 1 (中國) 及所有後代 ID 列表
SELECT queryChildrenAreaInfo(1);
-- 結果: $,1,2,3,4,5,6...,64

-- 2. 直接查詢節點 20 (北京XX區13) 及其所有後代明細
SELECT * FROM t_areainfo 
WHERE FIND_IN_SET(id, queryChildrenAreaInfo(20));

核心函數解析

關鍵點	說明
`GROUP_CONCAT(id)`	將子查詢的多行 ID 聚合為逗號分隔字串，供下一輪 `FIND_IN_SET` 使用
`FIND_IN_SET(parentId, sTempChd)`	高效判斷 parentId 是否在當前層級 ID 列表中 (字串查找，利用索引需注意型別轉換)
`WHILE 迴圈`	模擬遞歸：每輪展開一層，直到無子節點 (sTempChd 為 NULL) 退出
返回值長度	`VARCHAR(4000)` 限制最大節點數 ~幾百，超大樹需改用臨時表或應用層遞歸

MySQL 8.0+ 現代寫法 (CTE 遞歸，效能更優、可讀性強)

WITH RECURSIVE cte AS (
    SELECT id, name, parentId, 1 AS lvl FROM t_areainfo WHERE id = 20  -- 根節點
    UNION ALL
    SELECT t.id, t.name, t.parentId, c.lvl + 1
    FROM t_areainfo t JOIN cte c ON t.parentId = c.id
)
SELECT * FROM cte;

避坑與優化建議

⚠️ **FIND_IN_SET 不走索引**：大表建議在應用層遞歸 (先查所有 ID 到內存，代碼組裝 IN 列表) 或用 parentId IN (...) 批次查詢。
⚠️ **函數裡 SELECT 會鎖表** (MyISAM) / 產生大量臨時表 (InnoDB)，高併發慎用。
✅ **閉包表** 設計：冗余存儲所有祖先-後代關係，查詢極快，寫入稍重，適合讀多寫少樹形數據。
✅ **物化路徑** : 存儲 path = '1/2/4/5/'，LIKE '1/2/4/%' 即可查後代，配合索引高效。

結語： 存儲函數+FIND_IN_SET 是 5.6/5.7 時代的經典妥協方案。**MySQL 8.0 請直接用 CTE 遞歸**；架構層面考慮閉包表或物化路徑徹底解決樹形查詢效能難題。