基本語法

DELETE FROM table_name [WHERE condition];

核心示例

1. 條件刪除

DELETE FROM `users` WHERE `status` = 'deleted' AND `updated_at` < '2023-01-01';

2. 關聯刪除（JOIN 刪除）

-- 刪除無訂單的用戶
DELETE u FROM `users` u
LEFT JOIN `orders` o ON u.id = o.user_id
WHERE o.id IS NULL;

3. 限制刪除行數（防誤刪/分批）

DELETE FROM `logs` WHERE `created_at` < '2023-01-01' LIMIT 10000;

4. 清空表（保留結構、自增、權限）

DELETE FROM `table_name`;           -- 逐行刪除，記錄 binlog，觸發觸發器，慢
TRUNCATE TABLE `table_name`;        -- 重建表，極快，不記 binlog（非事務），重置自增
-- 區別詳見下表

DELETE vs TRUNCATE 對比

特性	DELETE	TRUNCATE
速度	慢（逐行、記錄 undo/redo/binlog）	極快（元數據操作、釋放數據頁）
事務	支持回滾	隱式提交，不可回滾
觸發器	觸發 BEFORE/AFTER DELETE	不觸發
自增計數器	保留	重置為 1
外鍵約束	檢查/級聯	禁止（有 FK 時報錯）
WHERE 條件	支持	不支持
權限	DELETE 權限	DROP 權限（實為 DDL）

大表分批刪除模板（避免長事務/鎖升級/主從延遲）

DELIMITER $$
CREATE PROCEDURE `batch_delete_old_logs`(
    IN p_batch_size INT DEFAULT 5000,
    IN p_max_runtime_sec INT DEFAULT 300,
    IN p_cutoff_date DATETIME
)
BEGIN
    DECLARE v_deleted INT DEFAULT 1;
    DECLARE v_start TIMESTAMP DEFAULT NOW();
    
    WHILE v_deleted > 0 AND TIMESTAMPDIFF(SECOND, v_start, NOW()) < p_max_runtime_sec DO
        DELETE FROM `logs` 
        WHERE `created_at` < p_cutoff_date 
        LIMIT p_batch_size;
        
        SET v_deleted = ROW_COUNT();
        -- 可選：釋放鎖、減輕主從壓力
        DO SLEEP(0.1);
    END WHILE;
END$$
DELIMITER ;

-- 調用
CALL batch_delete_old_logs(5000, 300, '2023-01-01');

性能優化關鍵

1. WHERE 列必須有索引：避免全表掃描鎖全表
2. 分批提交：每批 1000-10000 行，配合 LIMIT 或游標
3. 順序刪除：按主鍵順序 WHERE id BETWEEN ... AND ... 減少頁分裂
4. 禁用非必要索引：大批量刪除前 DROP INDEX，後重建
5. 考慮分區表：ALTER TABLE ... DROP PARTITION 秒級清理歷史分區

常見陷阱

現象	原因	解決
刪除極慢、阻塞所有查詢	無索引全表掃描 + 行鎖升級表鎖	加索引、分批、低峰期執行
自增 ID 不連續	DELETE 不重置、事務回滾、併發插入	業務勿依賴連續性；需連續用序列表
外鍵報錯 Cannot delete or update a parent row	子表存在關聯數據	先刪子表、或定義 ON DELETE CASCADE、或暫時禁用 FK
主從延遲飆升	大事務 binlog 傳輸/回放慢	分批、ROW 格式 binlog、並行複製

軟刪除模式（推薦替代物理刪除）

ALTER TABLE `users` ADD `deleted_at` DATETIME NULL DEFAULT NULL;
CREATE INDEX `idx_deleted_at` ON `users`(`deleted_at`);

-- 刪除 → 更新標記
UPDATE `users` SET `deleted_at` = NOW() WHERE `id` = 123;

-- 查詢自動過濾（視圖/應用層）
CREATE VIEW `v_active_users` AS SELECT * FROM `users` WHERE `deleted_at` IS NULL;

優點：數據可恢復、審計追蹤、避免外鍵級聯、索引維護成本低。