萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 遞迴刪除樹狀結構節點:資料庫分類/部門/選單無限級聯刪除實作

應用場景

  • 商品分類刪除:刪除父分類時連同所有子分類、孫分類一併刪除
  • 組織架構:刪除部門連同下屬子部門
  • 選單管理:刪除選單項目含所有子選單
  • 評論回覆樹:刪除留言連同所有回覆

資料表結構 (鄰接表模型 Adjacency List)

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED DEFAULT 0 COMMENT '父分類 ID,0 為頂層',
    name VARCHAR(100) NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_parent (parent_id)
);

遞迴刪除實作 (PDO 版)

<?php
class CategoryManager {
    private PDO $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /**
     * 遞迴刪除分類及其所有後代
     * @param int $categoryId
     * @return int 刪除總筆數
     * @throws Exception
     */
    public function deleteRecursive(int $categoryId): int {
        return $this->pdo->transaction(function() use ($categoryId) {
            $deletedCount = 0;

            // 1. 先遞迴刪除所有子分類
            $children = $this->getDirectChildren($categoryId);
            foreach ($children as $child) {
                $deletedCount += $this->deleteRecursive($child['id']);
            }

            // 2. 刪除自身
            $stmt = $this->pdo->prepare('DELETE FROM categories WHERE id = ?');
            $stmt->execute([$categoryId]);
            $deletedCount += $stmt->rowCount();

            // 3. (可選) 清理關聯資料:商品、文章等
            // $this->cleanupRelations($categoryId);

            return $deletedCount;
        });
    }

    /** 取得直系子分類 */
    private function getDirectChildren(int $parentId): array {
        $stmt = $this->pdo->prepare('SELECT id FROM categories WHERE parent_id = ?');
        $stmt->execute([$parentId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** 事務包裝器 (PHP 8.1+ PDO::transaction) */
    private function transaction(callable $callback): mixed {
        if (method_exists($this->pdo, 'transaction')) {
            return $this->pdo->transaction($callback);
        }
        // 手動事務相容
        $this->pdo->beginTransaction();
        try {
            $result = $callback();
            $this->pdo->commit();
            return $result;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

// 使用
$pdo = new PDO('mysql:host=localhost;dbname=shop', 'user', 'pass');
$mgr = new CategoryManager($pdo);
try {
    $count = $mgr->deleteRecursive(5); // 刪除 ID=5 及其所有後代
    echo "已刪除 $count 筆分類記錄";
} catch (Exception $e) {
    echo "刪除失敗: " . $e->getMessage();
}

效能優化:單次查詢取得所有後代 ID

遞迴查詢會產生 N+1 問題 (N = 樹深度)。大量資料時建議改用 遞迴 CTE (MySQL 8.0+, PostgreSQL, SQL Server):

MySQL 8.0+ 遞迴 CTE 一次取得所有後代

<?php
function deleteRecursiveCTE(PDO $pdo, int $rootId): int {
    $sql = <<prepare($sql);
    $stmt->execute([':root' => $rootId]);
    return $stmt->rowCount();
}

MySQL 5.7 / 不支援 CTE:儲存過程

DELIMITER //
CREATE PROCEDURE DeleteCategoryTree(IN rootId INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE childId INT;
    DECLARE cur CURSOR FOR SELECT id FROM categories WHERE parent_id = rootId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO childId;
        IF done THEN LEAVE read_loop; END IF;
        CALL DeleteCategoryTree(childId);
    END LOOP;
    CLOSE cur;

    DELETE FROM categories WHERE id = rootId;
END//
DELIMITER ;

-- PHP 呼叫
$pdo->exec("CALL DeleteCategoryTree(5)");

路徑枚舉模型 (Materialized Path) - 避免遞迴

ALTER TABLE categories ADD COLUMN path VARCHAR(1000) NOT NULL DEFAULT '';
-- 範例:頂層 path='1/',子分類 path='1/5/',孫分類 path='1/5/12/'

-- 刪除某節點及所有後代:單條 SQL
DELETE FROM categories WHERE path LIKE '1/5/%' OR id = 5;
-- 或
DELETE FROM categories WHERE path LIKE (SELECT CONCAT(path, id, '/') FROM categories WHERE id = 5) || '%';

關聯資料清理策略

private function cleanupRelations(int $categoryId): void {
    // 1. 將商品移至「未分類」或標記為孤兒
    $this->pdo->prepare('UPDATE products SET category_id = 0 WHERE category_id = ?')
          ->execute([$categoryId]);

    // 2. 或軟刪除商品
    // $this->pdo->prepare('UPDATE products SET deleted_at = NOW() WHERE category_id = ?')->execute([$categoryId]);

    // 3. 刪除關聯表記錄
    $this->pdo->prepare('DELETE FROM category_attributes WHERE category_id = ?')->execute([$categoryId]);
}

軟刪除替代方案 (推薦生產環境)

ALTER TABLE categories ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE categories ADD INDEX idx_deleted (deleted_at);

-- 刪除操作改為更新
UPDATE categories SET deleted_at = NOW() WHERE id = 5 OR path LIKE '1/5/%';

-- 查詢時自動排除
SELECT * FROM categories WHERE deleted_at IS NULL;

測試資料驗證

<?php
// 建立測試樹:1-2-3-4-5 (深度 5)
$pdo->exec("INSERT INTO categories (id, parent_id, name) VALUES
(1,0,'Root'), (2,1,'Child1'), (3,2,'Grandchild'), (4,3,'GreatGrand'), (5,4,'Leaf')");

$mgr = new CategoryManager($pdo);
$count = $mgr->deleteRecursive(2); // 刪除 Child1 及其後代
echo "Deleted: $count\n"; // 預期 4 (2,3,4,5)

// 驗證剩餘
$stmt = $pdo->query('SELECT id, name FROM categories');
print_r($stmt->fetchAll());
/* 
Array
(
    [0] => Array ( [id] => 1 [name] => Root )
)
*/

總結選型建議

資料量/深度推薦方案優點缺點
小量 (<1000) / 任意深度PHP 遞迴 + 事務簡單、可攜、易維護N+1 查詢、深度受限
中大量 / MySQL 8.0+遞迴 CTE單查詢、原子性、效能佳需新版資料庫
超大量 / 高併發路徑枚舉查詢/刪除 O(1)、無遞迴寫入較複雜、路徑長度限制
生產系統 / 需審計軟刪除資料不遺失、可恢復、合規查詢需額外過濾、佔空間

根據業務規模與資料庫版本選擇適當策略,並在開發階段以測試資料驗證遞迴邏輯正確性。

copyright © 萬盛學電腦網 all rights reserved