您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 程式設計疑難解答

MySQL 遞歸查詢父級下所有子孫級：存儲過程實現多級分類查詢

編輯：程式設計疑難解答

問題場景

表結構：id、name、pid（父級 ID）。需求：給定一級父類 ID（如「水果蔬菜」），一次性查出所有子級與孫級（水果、蔬菜、白菜、蘋果、紅富士……），要求 SQL 效率高。

方案對比

方案	優點	缺點	適用場景
遞歸 CTE (MySQL 8.0+)	標準 SQL、可讀性強、單語句完成	舊版本不支持	MySQL 8.0+、層級不深
存儲過程 + 臨時表	兼容 5.7、可控制邏輯、性能穩定	代碼較多、需創建臨時表權限	MySQL 5.7、大數據量、深層級
應用層遞歸查詢	簡單、無數據庫版本限制	多次交互、延遲高	數據量小、層級固定
路徑枚舉 (Path Enumeration)	查詢極快、`LIKE '1/%'` 即可	寫入維護成本高、長度受限	讀多寫少、樹結構穩定
閉包表	查詢靈活、支持任意深度	存儲空間大、維護複雜	高頻多維度查詢

推薦：存儲過程實現（兼容 5.7）

DELIMITER //
CREATE PROCEDURE GetAllChildren(IN rootId INT)
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_tree (id INT PRIMARY KEY);
    TRUNCATE TABLE tmp_tree;
    INSERT INTO tmp_tree SELECT rootId;

    REPEAT
        INSERT IGNORE INTO tmp_tree
        SELECT t.id FROM your_table t
        JOIN tmp_tree tmp ON t.pid = tmp.id;
    UNTIL ROW_COUNT() = 0 END REPEAT;

    SELECT * FROM your_table WHERE id IN (SELECT id FROM tmp_tree);
    DROP TEMPORARY TABLE tmp_tree;
END //
DELIMITER ;

調用：CALL GetAllChildren(1);

MySQL 8.0+ 遞歸 CTE 寫法

WITH RECURSIVE cte AS (
    SELECT id, name, pid FROM your_table WHERE id = 1
    UNION ALL
    SELECT t.id, t.name, t.pid FROM your_table t
    JOIN cte ON t.pid = cte.id
)
SELECT * FROM cte;