SQL中TRUNCATE vs. DELETE 命令对比

TRUNCATE vs. DELETE 详细对比

特性	TRUNCATE	DELETE
分类	DDL (数据定义语言)	DML (数据操作语言)
工作原理	直接释放存储表数据的数据页，并在事务日志中记录页的释放。效率极高。	在事务日志中逐行记录每一条被删除的记录。系统会为每行数据执行删除操作，效率较低。
WHERE 子句	不支持。只能删除整个表中的所有数据。	支持。可以带WHERE条件来删除部分数据。
事务处理	由于是DDL操作，它隐式提交当前事务。这意味着TRUNCATE操作无法回滚（在某些数据库如SQL Server中，如果它被包含在显式事务中，可以回滚）。	作为DML操作，它可以被回滚。执行DELETE后，只要没有COMMIT，就可以用ROLLBACK撤销操作。
触发器	不会激活触发器。因为它不逐行处理数据。	会激活DELETE触发器。
锁的粒度	通常会对表施加表级锁，立即释放所有数据行和索引占用的空间。	通常会施加行级锁（每行删除时都上锁），因此可能会产生大量的锁，占用更多资源。
标识列重置	会重置标识列（如Auto Increment）的计数器，恢复到初始值（通常是1）。	不会重置标识列。下一个插入的值会从之前的最大值继续递增。
空间回收	立即释放磁盘空间给操作系统。	删除数据后，占用的磁盘空间不会立即释放给操作系统，而是被标记为“可重用”，后续的插入操作可以覆盖这部分空间。
性能	非常快。因为它不记录详细的日志，只记录整个数据页的释放。	相对较慢。尤其是对于大表，因为需要记录每一条删除操作的日志，并且要维护索引等。
外键约束	如果表被其他表的外键约束引用，在大多数数据库（如PostgreSQL, Oracle）中无法使用TRUNCATE（除非使用CASCADE选项）。	可以正常使用。它会检查外键约束，如果违反约束，删除操作会失败。

---

如何选择？

• 需要删除全部数据时：优先使用 TRUNCATE TABLE，因为它更快，使用的系统资源和事务日志资源更少，并且能重置标识列。

• 需要删除部分数据时：必须使用 DELETE FROM table_name WHERE ...。

• 需要保留标识列计数时：使用 DELETE。

• 需要操作可回滚时：使用 DELETE（并在事务中操作）。

• 表有外键引用时：需要特别注意，TRUNCATE可能会被阻止，此时可能需要先删除约束或使用DELETE。

补充：DROP

有时人们会混淆这三个命令，这里简单对比一下：

• DROP TABLE：属于DDL。它的作用是删除整个表，包括表结构、数据、索引、约束、触发器等。这个表将不复存在。

• TRUNCATE TABLE：删除表中的所有数据，但保留表结构。

• DELETE FROM TABLE：删除表中的全部或部分数据，保留表结构。

简单比喻：

• DELETE：用橡皮擦掉本子上的所有字（可以只擦几行，擦完本子还在，页码继续）。

• TRUNCATE：直接把写满字的本子页撕掉，换上一叠全新的空白页（页码从头开始）。

• DROP：把整个本子（包括封面和所有内页）都扔进碎纸机。