核心差異速查表

維度	DELETE	TRUNCATE TABLE
語法分類	DML（數據操作語言）	DDL（數據定義語言）
WHERE 條件	支持，可精確刪除子集	不支持，只能清空整表
觸發器	觸發 ON DELETE 觸發器	不觸發觸發器
事務日誌	記錄每行刪除（全日誌）	僅記錄頁釋放（最小日誌）
執行速度	慢（逐行、產生日誌、鎖行）	極快（元數據操作、釋放頁）
自增列重置	不重置（保留最大值）	重置為種子值（通常 1）
返回值	受影響行數	固定返回 0（不計數）
回滾支持	支持（在事務中）	支持（在事務中，但日誌極少）
權限需求	DELETE 權限	ALTER 權限（隱含 DROP/CREATE）
外鍵約束	檢查引用完整性	被引用表不可 TRUNCATE
索引視圖	允許	禁止

DELETE 進階用法

1. 帶排序與限制的刪除（MySQL/MariaDB/PostgreSQL 支持）

-- 刪除前 6 筆 name='Mike' 的記錄（按 id 降序）
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;

2. 多表關聯刪除

-- MySQL 語法
DELETE t1 FROM t1 INNER JOIN t2 ON t1.id = t2.ref_id WHERE t2.status = 0;

-- SQL Server 語法
DELETE t1 FROM t1 INNER JOIN t2 ON t1.id = t2.ref_id WHERE t2.status = 0;

3. 保留自增最大值的「偽清空」

DELETE FROM tbl WHERE 1=1;  -- 或 WHERE TRUE
-- 掃描全表，逐行刪除，日誌巨大，自增值不重置

警告：大表執行極慢，產生海量日誌，可能填滿磁盤。僅在極少數「必須保留自增值」場景使用。

TRUNCATE TABLE 實戰細節

TRUNCATE TABLE [schema.]table_name;  -- TABLE 關鍵字可選

• 實質：DROP TABLE + CREATE TABLE（保留結構、權限、索引、統計信息）。
• 最小日誌：僅記錄頁釋放，恢復模式為 SIMPLE/BULK_LOGGED 時不阻塞日誌截斷。
• 不可用於：被外鍵引用的表、發布訂閱發布表、系統版本表。

大表清空最佳實踐

1. 首選 TRUNCATE TABLE：毫秒級完成，釋放空間（不釋放文件大小，但標記頁可用）。
2. 需保留自增值：DELETE TOP (10000) FROM tbl WHERE 1=1 分批循環，配合 WAITFOR DELAY 避免長事務阻塞。
3. 需保留部分數據：DELETE FROM tbl WHERE create_time < '2023-01-01'; 配合索引、分批提交。
4. 分區表：ALTER TABLE ... SWITCH PARTITION ... TO staging_table; TRUNCATE TABLE staging_table; 秒級歸檔歷史分區。

事務與恢復行為

BEGIN TRAN;
TRUNCATE TABLE big_table;  -- 事務中可回滾（元數據操作可回滾）
ROLLBACK;  -- 表結構與數據恢復如初

BEGIN TRAN;
DELETE FROM big_table;  -- 產生海量日誌，回滾極慢
ROLLBACK;  -- 需等待日誌反向應用

MySQL 特有差異

• MyISAM：TRUNCATE 相當於 DROP + CREATE，極快；DELETE 逐行刪除。
• InnoDB：TRUNCATE 重建表（新建 .ibd 文件），DELETE 標記刪除標記，由 Purge 線程異步清理。
• MySQL 8.0 TRUNCATE 為原子操作（DDL），崩潰安全。

口訣：要篩選用 DELETE，要清空用 TRUNCATE，要保留自增值分批 DELETE，要極致速度分區 SWITCH。理解底層日誌與鎖機制，才能在生產環境游刃有餘。