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

MySQL 遞歸查詢樹狀結構：存儲函數實現子節點與父節點全遍歷

編輯：MySQL 綜合教學

背景與挑戰

MySQL 5.0~5.7 版本長期缺乏原生遞歸 CTE（Common Table Expression）支援（直到 8.0 才引入），面對樹狀表（如員工架構、分類目錄、權限體系）的層層遍歷，開發者常被迫在應用層多次查詢或編寫存儲程序。本文提供兩個生產可用的存儲函數，分別解決「查詢所有子孫節點」與「查詢所有祖先節點」需求。

表結構假設

CREATE TABLE `user_role` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `parentid` INT DEFAULT 0 COMMENT '父節點ID，0為根節點',
  `name` VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心函數：獲取所有子節點（含遞歸後代）

CREATE FUNCTION `getChildList`(rootId INT) RETURNS VARCHAR(1000)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE sChildList VARCHAR(1000) DEFAULT '';
    DECLARE sChildTemp VARCHAR(1000);
    
    SET sChildTemp = CAST(rootId AS CHAR);
    
    WHILE sChildTemp IS NOT NULL AND sChildTemp != '' DO
        IF sChildList != '' THEN
            SET sChildList = CONCAT(sChildList, ',', sChildTemp);
        ELSE
            SET sChildList = sChildTemp;
        END IF;
        
        -- 核心：FIND_IN_SET 配合 GROUP_CONCAT 實現層級擴展
        SELECT GROUP_CONCAT(id) INTO sChildTemp
        FROM user_role
        WHERE FIND_IN_SET(parentid, sChildTemp) > 0;
    END WHILE;
    
    RETURN sChildList;
END;

調用範例

-- 1. 直接獲取 ID 列表
SELECT getChildList(0) AS all_descendants;

-- 2. 關聯查詢完整資訊
SELECT * FROM user_role 
WHERE FIND_IN_SET(id, getChildList(2));

核心函數：獲取所有父節點（遞歸祖先鏈）

CREATE FUNCTION `getParentList`(rootId INT) RETURNS VARCHAR(1000)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE sParentList VARCHAR(1000) DEFAULT '';
    DECLARE sParentTemp VARCHAR(1000);
    
    SET sParentTemp = CAST(rootId AS CHAR);
    
    WHILE sParentTemp IS NOT NULL AND sParentTemp != '' DO
        IF sParentList != '' THEN
            SET sParentList = CONCAT(sParentTemp, ',', sParentList); -- 祖先在前
        ELSE
            SET sParentList = sParentTemp;
        END IF;
        
        SELECT GROUP_CONCAT(parentid) INTO sParentTemp
        FROM user_role
        WHERE FIND_IN_SET(id, sParentTemp) > 0;
    END WHILE;
    
    RETURN sParentList;
END;

調用範例

SELECT getParentList(6) AS ancestor_chain;
SELECT * FROM user_role WHERE FIND_IN_SET(id, getParentList(2));

原理解析

迭代擴展：每輪循環將當前層級的 ID 集合（sChildTemp/sParentTemp）作為下一輪的查詢條件
集合運算：FIND_IN_SET + GROUP_CONCAT 將多行 ID 聚合為逗號分隔字串，實現「集合當條件」
終止條件：下一輪查無結果時 GROUP_CONCAT 返回 NULL，循環自然結束

效能與替代方案

方案	優點	缺點	適用場景
存儲函數	單次呼叫、邏輯封裝、SQL 層完成	增加 DB 負載、VARCHAR(1000) 長度限制、大樹效能下降	中小型樹、低頻查詢
應用層遞歸查詢	減輕 DB 壓力、無長度限制、易除錯	多次網路往返、N+1 查詢問題	大型樹、高頻查詢、微服務架構
Closure Table（關係閉包表）	查詢極快、支援任意深度、可存路徑長度	寫入維護成本高、儲存空間大	頻繁讀取、深度大、讀多寫少
MySQL 8.0 CTE	標準語法、效能優化、可讀性強	需升級版本	新專案、版本可控環境