萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL DELETE 刪除資料完整指南:條件刪除、多表關聯、TRUNCATE 差異與安全防護

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 對比

特性DELETETRUNCATEDROP
刪除範圍指定條件/全表全表表結構+資料+索引+權限
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
copyright © 萬盛學電腦網 all rights reserved