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)
);
<?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();
}
遞迴查詢會產生 N+1 問題 (N = 樹深度)。大量資料時建議改用 遞迴 CTE (MySQL 8.0+, PostgreSQL, SQL Server):
<?php
function deleteRecursiveCTE(PDO $pdo, int $rootId): int {
$sql = <<prepare($sql);
$stmt->execute([':root' => $rootId]);
return $stmt->rowCount();
}
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)");
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)、無遞迴 | 寫入較複雜、路徑長度限制 |
| 生產系統 / 需審計 | 軟刪除 | 資料不遺失、可恢復、合規 | 查詢需額外過濾、佔空間 |
根據業務規模與資料庫版本選擇適當策略,並在開發階段以測試資料驗證遞迴邏輯正確性。