MySQL 實現 Oracle START WITH CONNECT BY 遞歸樹查詢：存儲過程/遞歸 CTE 方案

背景

Oracle 原生支持 START WITH ... CONNECT BY PRIOR 遞歸查詢，MySQL 8.0 前無此語法，需用存儲過程或應用層遞歸。MySQL 8.0+ 支持 **遞歸 CTE (Common Table Expressions)**。

MySQL 8.0+ 遞歸 CTE 方案 (推薦)

WITH RECURSIVE sub_tree AS (
    -- 錨點：根節點
    SELECT YWZYFL_ID, PARENT_ID, 1 AS lvl
    FROM SFL_YWZYFL
    WHERE YWZYFL_ID IN (:ids)  -- 參數化
    
    UNION ALL
    
    -- 遞歸：子節點
    SELECT t.YWZYFL_ID, t.PARENT_ID, st.lvl + 1
    FROM SFL_YWZYFL t
    INNER JOIN sub_tree st ON t.PARENT_ID = st.YWZYFL_ID
    WHERE st.lvl < 100 -- 防無限遞歸
)
DELETE FROM SFL_YWZYFL
WHERE YWZYFL_ID IN (SELECT YWZYFL_ID FROM sub_tree);

MySQL 5.7 及以下：存儲過程模擬

DELIMITER //
CREATE PROCEDURE delete_subtree(IN root_ids TEXT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_id BIGINT;
    DECLARE cur CURSOR FOR SELECT YWZYFL_ID FROM SFL_YWZYFL WHERE FIND_IN_SET(YWZYFL_ID, root_ids);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 臨時表存儲所有待刪除 ID
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_del (id BIGINT PRIMARY KEY);
    TRUNCATE TABLE tmp_del;
    
    -- 初始化根節點
    INSERT IGNORE INTO tmp_del (id) SELECT CAST(TRIM(id) AS UNSIGNED) FROM JSON_TABLE(CONCAT('["', REPLACE(root_ids, ',', '","'), '"]'), "$[*]" COLUMNS (id VARCHAR(50) PATH "$"));
    
    REPEAT
        SET done = FALSE;
        OPEN cur;
        read_loop: LOOP
            FETCH cur INTO current_id;
            IF done THEN LEAVE read_loop; END IF;
            INSERT IGNORE INTO tmp_del (id)
            SELECT YWZYFL_ID FROM SFL_YWZYFL WHERE PARENT_ID = current_id;
        END LOOP;
        CLOSE cur;
        -- 更新 root_ids 為新發現的子節點，繼續循環直到無新增
        -- (此處簡化，實際需動態構建新 root_ids)
    UNTIL done END REPEAT;
    
    DELETE FROM SFL_YWZYFL WHERE YWZYFL_ID IN (SELECT id FROM tmp_del);
    DROP TEMPORARY TABLE tmp_del;
END //
DELIMITER ;

應用層遞歸 (通用、簡單)

// Java 示例
public void deleteTree(Set rootIds) {
    Set allIds = new HashSet<>(rootIds);
    collectChildren(rootIds, allIds);
    repo.deleteByIds(allIds); // 批量刪除
}

private void collectChildren(Set parentIds, Set allIds) {
    List children = repo.findChildrenIds(parentIds); // SELECT id WHERE parent_id IN (...)
    Set newChildren = new HashSet<>(children);
    newChildren.removeAll(allIds);
    if (newChildren.isEmpty()) return;
    allIds.addAll(newChildren);
    collectChildren(newChildren, allIds);
}

總結

• MySQL 8.0+ 直接用 WITH RECURSIVE CTE，標準、高效、可單條 SQL 完成。
• 舊版本用存儲過程或應用層遞歸收集 ID 再批量刪除。
• 避免在大樹上無限制遞歸，設置最大深度或記錄訪問集合防循環。