MySQL 遞歸查詢樹狀表：存儲函數實現子節點/父節點全鏈路查找

MySQL 5.x/5.5/5.6/5.7 均不支援標準 WITH RECURSIVE（8.0+ 才支援），需自寫存儲函數模擬遞歸遍歷。本文提供子節點、父節點兩個通用函數。

表結構假設

CREATE TABLE user_role (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50)
);

1. 獲取所有子節點（含自身）

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 DO
        IF sChildList <> '' THEN
            SET sChildList = CONCAT(sChildList, ',', sChildTemp);
        ELSE
            SET sChildList = sChildTemp;
        END IF;

        SELECT GROUP_CONCAT(id) INTO sChildTemp
        FROM user_role
        WHERE FIND_IN_SET(parent_id, sChildTemp) > 0;
    END WHILE;

    RETURN sChildList;
END;

調用方式

-- 1. 直接獲取 ID 串
SELECT getChildList(0) AS ids;

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

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 DO
        IF sParentList <> '' THEN
            SET sParentList = CONCAT(sParentTemp, ',', sParentList);
        ELSE
            SET sParentList = sParentTemp;
        END IF;

        SELECT GROUP_CONCAT(parent_id) INTO sParentTemp
        FROM user_role
        WHERE FIND_IN_SET(id, sParentTemp) > 0;
    END WHILE;

    RETURN sParentList;
END;

調用方式

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

核心邏輯

• 用 VARCHAR 存「當前層級 ID 列表」，循環迭代。
• FIND_IN_SET(parent_id, current_list) 找下一層子節點；FIND_IN_SET(id, current_list) 找上一層父節點。
• GROUP_CONCAT 將多行 ID 合併為逗號串供下輪 FIND_IN_SET 使用。
• 返回字串包含自身 ID，便於直接 FIND_IN_SET 關聯。

性能與替代方案

• 函數裡隱式循環 + 多次掃表，深度/廣度大時較慢。
• 生產環境建議：
  1. 閉包表：冗餘存儲所有祖先-後代關係，查詢 O(1)。
  2. 物化路徑：path VARCHAR(1000) 存 /1/5/12/，LIKE '/1/5/%' 查子樹。
  3. 升級 MySQL 8.0 用原生 WITH RECURSIVE。
• PM 建議「Java 多查幾次」也是務實選擇：應用層遞歸查詢，減輕資料庫壓力，適合樹深度可控、併發不極高場景。

函數返回長度受限於 group_concat_max_len（默認 1024），大樹需調大或改用閉包表。