MySQL 存儲過程實現遞歸查詢員工層級關係完整框架

業務場景建模

表 `thuman`：`human_id` (PK), `manager_id` (FK 自關聯), `name`...
目標：給定 `manager_id`，遞歸查詢所有下屬 (含間接下屬)，結果存入臨時表/結果表。

存儲過程框架 (MySQL 8.0+ 支持 CTE，優先使用)

-- MySQL 8.0+ 遞歸 CTE (無需存儲過程)
WITH RECURSIVE subordinates AS (
    SELECT human_id, manager_id, name, 1 AS level
    FROM thuman WHERE manager_id = @start_id
    UNION ALL
    SELECT t.human_id, t.manager_id, t.name, s.level + 1
    FROM thuman t JOIN subordinates s ON t.manager_id = s.human_id
)
SELECT * FROM subordinates;

MySQL 5.7 兼容：存儲過程 + 臨時表

DELIMITER //
CREATE PROCEDURE GetAllSubordinates(IN p_manager_id INT)
BEGIN
    -- 1. 創建結果臨時表
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_subordinates (
        human_id INT PRIMARY KEY, manager_id INT, name VARCHAR(50), level INT
    );
    TRUNCATE TABLE tmp_subordinates;
    
    -- 2. 種子數據：直接下屬
    INSERT INTO tmp_subordinates
    SELECT human_id, manager_id, name, 1 FROM thuman WHERE manager_id = p_manager_id;
    
    -- 3. 迴圈遞歸擴展
    SET @rowcount = ROW_COUNT();
    WHILE @rowcount > 0 DO
        INSERT IGNORE INTO tmp_subordinates
        SELECT t.human_id, t.manager_id, t.name, s.level + 1
        FROM thuman t
        JOIN tmp_subordinates s ON t.manager_id = s.human_id
        WHERE s.level = (SELECT MAX(level) FROM tmp_subordinates); -- 只擴展最新層
        SET @rowcount = ROW_COUNT();
    END WHILE;
    
    -- 4. 返回結果
    SELECT * FROM tmp_subordinates ORDER BY level, human_id;
END //
DELIMITER ;

調用方式

CALL GetAllSubordinates(1001); -- 查詢 1001 的所有下屬

關鍵點說明

• `INSERT IGNORE` 防止重複 `human_id` 報錯 (主鍵衝突)。
• 每輪只擴展 `MAX(level)` 層，避免重複掃描已處理層級。
• 臨時表會話結束自動銷毀，無需手動清理。
• 大數據量 (萬級+) 建議加索引 `INDEX idx_mgr (manager_id)`。< /li>