TRUNCATE
和 DELETE
是 SQL 中用于删除表中数据的两种命令,它们有一些关键区别:
1. 基本区别
-
DELETE
:- 删除表中的数据,但不会删除表结构和索引。
- 可以使用
WHERE
子句来删除特定的记录,也可以不使用WHERE
子句来删除所有记录。 - 会逐行删除数据,并记录每一行的删除操作(如果启用了事务日志)。
- 可以触发
DELETE
触发器(如果存在)。 - 事务性:可以在事务中使用,并且可以回滚。
-
TRUNCATE
:- 删除表中的所有数据,但不删除表结构、索引或表的元数据。
- 不支持使用
WHERE
子句,只能删除整个表的数据。 - 通过快速释放数据页的方式删除所有行,因此比
DELETE
更快。 - 不会触发
DELETE
触发器。 - 事务性:在支持事务的数据库系统中,
TRUNCATE
操作通常也可以回滚,但有些数据库系统(如 MySQL)将其视为 DDL 操作,因此不支持回滚。
2. 性能
-
DELETE
:- 性能较低,因为逐行删除并记录操作到事务日志中,特别是在删除大量数据时。
- 对表的锁定是行级锁或页级锁,取决于具体实现。
-
TRUNCATE
:- 性能较高,因为它不会逐行删除数据,而是快速释放整个数据页。
- 通常会加表锁而不是行锁,因此操作速度较快。
3. 自增计数器
-
DELETE
:- 删除表数据时,表的自增主键计数器不会被重置。
- 自增值将继续从最后的值开始,而不是从1开始。
-
TRUNCATE
:- 删除表数据时,表的自增主键计数器通常会被重置为起始值(例如0或1),具体行为取决于数据库系统。
4. 事务处理
-
DELETE
:- 可以在事务中使用,操作可以被提交或回滚。
- 操作的每一步都记录到事务日志中。
-
TRUNCATE
:- 在许多数据库系统中,
TRUNCATE
是一个 DDL 操作而非 DML 操作,因此在某些情况下可能不支持回滚(如早期版本的 MySQL)。 - 在支持事务的数据库中,
TRUNCATE
也可以回滚,但行为可能因数据库实现而异。
- 在许多数据库系统中,
5. 触发器
-
DELETE
:- 可以触发
DELETE
触发器,这些触发器可以在删除数据前后执行自定义操作。
- 可以触发
-
TRUNCATE
:- 不会触发
DELETE
触发器。
- 不会触发
6. 使用示例
-
使用
DELETE
:-- 删除表中所有记录 DELETE FROM your_table_name; -- 删除表中符合条件的记录 DELETE FROM your_table_name WHERE condition;
-
使用
TRUNCATE
:-- 删除表中所有记录,并重置自增计数器 TRUNCATE TABLE your_table_name;
总结
- 使用
DELETE
当你需要逐行删除记录、触发器支持或需要回滚操作时。 - 使用
TRUNCATE
当你需要快速清空表中的所有数据,并且不关心触发器或自增计数器的重置时。