核心差異速查表
| 特性 | TRUNCATE TABLE | DELETE FROM |
|---|
| 操作類型 | DDL（數據定義語言） | DML（數據操作語言） |
| 執行速度 | 極快（元數據操作） | 較慢（逐行刪除+日誌） |
| 自增 ID | 重置為初始值（通常為 1） | 保持不變，繼續遞增 |
| 事務支持 | 不可回滾（隱式提交） | 可回滾（在事務中） |
| 觸發器 | 不觸發 DELETE 觸發器 | 觸發 BEFORE/AFTER DELETE |
| 外鍵約束 | 被引用表不可 TRUNCATE | 受外鍵約束限制 |
| 返回值 | 不返回影響行數 | 返回刪除行數 |
| Binlog 記錄 | 記錄為 DROP+CREATE | 記錄每行刪除（ROW 格式） |
實戰演示
-- 建表示例
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
-- 插入測試數據
INSERT INTO `user` (`name`) VALUES ('bob'), ('mark'), ('alex'), ('julia');
-- id: 1,2,3,4
-- TRUNCATE 測試
TRUNCATE TABLE `user`;
INSERT INTO `user` (`name`) VALUES ('bill');
-- 新記錄 id = 1（重置）
-- 對比 DELETE
INSERT INTO `user` (`name`) VALUES ('bob'), ('mark'), ('alex'), ('julia');
DELETE FROM `user`;
INSERT INTO `user` (`name`) VALUES ('bill');
-- 新記錄 id = 5（繼續遞增）
選擇指南
- 使用 TRUNCATE：全表清空、重置自增 ID、大表快速清理、測試數據重置、不需要觸發器/事務
- 使用 DELETE：條件刪除、需要事務回滾、需觸發器審計、被外鍵引用的父表、需獲取影響行數
大表清空最佳實踐
-- 方案一：TRUNCATE（最快，無條件）
TRUNCATE TABLE big_table;
-- 方案二：分批 DELETE（避免長事務、鎖表、Binlog 膨脹）
DELETE FROM big_table WHERE id BETWEEN 1 AND 10000;
-- 循環執行，每批 COMMIT，間隔 SLEEP(0.1)
-- 方案三：重建表（保留結構、索引、權限）
CREATE TABLE new_table LIKE big_table;
RENAME TABLE big_table TO old_table, new_table TO big_table;
DROP TABLE old_table;
注意事項
- MySQL 8.0+
TRUNCATE 在某些場景下可回滾（數據字典原子化），但不建議依賴此特性
- InnoDB 中
TRUNCATE 實際執行 DROP TABLE + CREATE TABLE，需 DROP 權限
- 外鍵約束下
TRUNCATE 會報錯：ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
- 分區表
TRUNCATE PARTITION 可清空特定分區
