DELETE 基礎語法
DELETE FROM table_name
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];
核心安全鐵律
永遠加上 WHERE 條件!
DELETE FROM table 會清空整張表 (可回滾),TRUNCATE TABLE 也會清空 (不可回滾、重置自增 ID、速度更快)。生產環境嚴禁無 WHERE 的 DELETE。
單表刪除範例
-- 條件刪除
DELETE FROM persons WHERE last_name = 'Griffin';
-- 刪除前 N 筆 (配合 ORDER BY)
DELETE FROM logs WHERE created_at < '2023-01-01' ORDER BY id ASC LIMIT 1000;
-- 子查詢刪除
DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'deleted');
多表關聯刪除 (MySQL 特有語法)
內連接刪除 (刪除兩表中匹配的行)
DELETE o, oi
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'cancelled';
左連接刪除 (刪除左表中無匹配右表的行 - 孤兒資料清理)
DELETE p
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE c.id IS NULL;
使用別名的標準寫法
DELETE t1 FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.ref_id
WHERE t2.status = 'invalid';
DELETE vs TRUNCATE vs DROP 對比
| 特性 | DELETE | TRUNCATE | DROP |
| 刪除範圍 | 指定條件/全表 | 全表 | 表結構+資料+索引+權限 |
| WHERE 支援 | ✅ | ❌ | ❌ |
| 觸發器 | ✅ 觸發 | ❌ 不觸發 | ❌ |
| 事務/回滾 | ✅ 支援 | ❌ 隱含提交 | ❌ |
| 自增 ID | 保留最大值+1 | 重置為 1 | 表不存在 |
| 速度 | 逐行刪除,慢 | 釋放頁面,極快 | 最快 |
| 外鍵限制 | 受限制 | 受限制 (需先刪除 FK) | 受限制 |
| 空間回收 | 不立即回收 (需 OPTIMIZE) | 立即回收 | 立即回收 |
PHP 實作:預處理語句防注入
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// 單筆刪除
$stmt = $mysqli->prepare("DELETE FROM members WHERE id = ?");
$stmt->bind_param('i', $id);
$id = 123;
$stmt->execute();
echo "刪除行數: " . $stmt->affected_rows;
$stmt->close();
// 批次刪除 (IN 列表) - 注意參數綁定技巧
$ids = [1, 2, 3, 4, 5];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "DELETE FROM members WHERE id IN ($placeholders)";
$stmt = $mysqli->prepare($sql);
$types = str_repeat('i', count($ids));
$stmt->bind_param($types, ...$ids); // PHP 5.6+ 支援 ... 操作符
$stmt->execute();
echo "批次刪除行數: " . $stmt->affected_rows;
$stmt->close();
$mysqli->close();
?>
大量資料刪除效能策略
策略一:分批刪除 (避免長事務、鎖升級、Binlog 暴增)
<?php
$batchSize = 1000;
$deletedTotal = 0;
do {
$stmt = $mysqli->prepare("
DELETE FROM logs
WHERE created_at < '2023-01-01'
LIMIT ?
");
$stmt->bind_param('i', $batchSize);
$stmt->execute();
$deleted = $stmt->affected_rows;
$deletedTotal += $deleted;
$stmt->close();
// 避免過度佔用資源
if ($deleted > 0) usleep(100000); // 0.1 秒間隔
} while ($deleted > 0);
echo "總共刪除 $deletedTotal 筆";
?>
策略二:建新表保留需保留資料 (超大量刪除最快)
-- 1. 建立新表結構
CREATE TABLE logs_new LIKE logs;
-- 2. 複製保留資料 (利用索引快速查詢)
INSERT INTO logs_new SELECT * FROM logs WHERE created_at >= '2023-01-01';
-- 3. 重命名表 (原子操作,極短鎖定)
RENAME TABLE logs TO logs_old, logs_new TO logs;
-- 4. 確認無誤後刪除舊表
DROP TABLE logs_old;
策略三:分區表直接 DROP PARTITION (最快)
-- 假設按月分區
ALTER TABLE logs DROP PARTITION p202201, p202202, p202203;
外鍵約束下的刪除處理
| ON DELETE 行為 | 父表刪除時子表動作 |
RESTRICT / NO ACTION | 阻止刪除,報錯 (預設) |
CASCADE | 級聯刪除子表對應行 |
SET NULL | 子表外鍵欄位設為 NULL (欄位必須允許 NULL) |
SET DEFAULT | 子表外鍵欄位設為預設值 |
軟刪除模式 (推薦業務層面)
-- 表結構加欄位
ALTER TABLE members ADD COLUMN deleted_at DATETIME DEFAULT NULL;
ALTER TABLE members ADD INDEX idx_deleted_at (deleted_at);
-- 刪除變更新
UPDATE members SET deleted_at = NOW() WHERE id = 123;
-- 查詢自動排除 (視圖或 WHERE)
CREATE VIEW active_members AS SELECT * FROM members WHERE deleted_at IS NULL;
SELECT * FROM members WHERE deleted_at IS NULL;
優點:資料可恢復、審計追蹤、避免外鍵約束問題、不影響自增 ID。
常見錯誤排查
| 錯誤 | 原因 | 解決 |
| Cannot delete or update a parent row | 外鍵約束阻止 | 先刪子表資料、或設 CASCADE、或軟刪除 |
| Lock wait timeout exceeded | 行鎖等待超時 | 分批刪除、優化索引、縮短事務 |
| Table is full | 磁碟空間不足 / tmpdir 滿 | 清理空間、調整 tmpdir |