MySQL中delete，drop，truncate的区别是什么？

发表于 分类于 阅读次数： 本文字数： 2.3k 阅读时长 ≈ 2 分钟

你好，我是猿java。

在 MySQL 中，DELETE、DROP 和 TRUNCATE 是三种用来操作数据库表及其数据的数据定义语言（DDL）和数据操作语言（DML）语句。它们各自的功能、特性和应用场景均有所不同。在这篇文章中，我们将详细探讨 DELETE、DROP 和 TRUNCATE 之间的区别，通过语法、作用、性能、事务支持等方面进行深入分析。

1. DELETE

DELETE 是一种数据操作语言（DML）语句，用于删除表中满足条件的记录。其作用是按行删除表中的数据，而不是删除表本身。

1.1 语法

1
DELETE FROM table_name [WHERE condition];

1.2 特性

  1. 针对行操作：DELETE 会从表中逐行删除符合特定条件的数据。如果不提供 WHERE 子句，则会删除表中的所有行。
  2. 事务支持：DELETE 是可回滚的，因为它遵循事务机制。如果在删除操作后未提交（COMMIT），可以通过 ROLLBACK 恢复被删除的数据。
  3. 数据日志：DELETE 操作会记录行级日志，每删除一行都会被记录下来，因此日志量大，执行速度相对较慢。
  4. 触发器支持：删除时可以触发 BEFORE DELETE 或 AFTER DELETE 类型的触发器，以便在操作前后执行指定逻辑。
  5. 空间回收：DELETE 仅释放数据所占用的行空间，但不释放表中的索引等占用的空间。

1.3 使用场景

  • 删除表中特定的行数据。
  • 删除全部数据，但保留表的结构，例如当需要保留索引时。

1.4 示例

1
2
3
4
5
6
7
8
-- 删除满足条件的数据
DELETE FROM employees WHERE department = 'HR';

-- 删除表中所有数据
DELETE FROM employees;

-- 注意，这种全表删除可以重置自增 ID：
DELETE FROM employees WHERE 1 = 1;

2. DROP

DROP 是一种数据定义语言（DDL）语句，用于删除整个对象（如表、数据库、视图等）。一旦执行，目标对象（如表）和表中的数据都会彻底删除，且无法恢复。

2.1 语法

1
2
DROP TABLE table_name; 
DROP DATABASE database_name;

2.2 特性

  1. 删除整个对象：DROP 会完全删除所指定的数据库表或其他对象，且连同所有数据一并消失。
  2. 不可回滚：DROP 是不可回滚的，一旦执行，表和数据都会被永久删除，无法通过事务（ROLLBACK）恢复。
  3. 不记录日志：DROP 不会记录事务日志，因此执行效率较高。
  4. 释放空间：当表被更新或替代时，DROP 可以释放其占用的空间，包括数据块、索引、统计信息等。
  5. 不支持触发器：DROP 是直接删除整个表，触发器无法应用于此对象级别的操作。

2.3 使用场景

  • 删除整个表、数据库或视图不再需要的情况下。
  • 数据表结构需要被重新彻底设计时。

2.4 示例

1
2
3
4
5
-- 删除整个表及其中数据：
DROP TABLE employees;

-- 删除整个数据库：
DROP DATABASE company_db;

3. TRUNCATE

TRUNCATE 是一种数据定义语言（DDL）语句，尽管它的主要作用是删除表中所有行，但它和 DELETE 不同。它更侧重于快速删除表的所有数据，同时保留表的结构。

3.1 语法

1
TRUNCATE TABLE table_name;

3.2 特性

  1. 删除所有数据：TRUNCATE 会清空整个表的数据，就像 DELETE 没有设置 WHERE 条件一样，但其效率更高。
  2. 不可回滚：TRUNCATE 在事务中无法回滚，数据删除后不能通过 ROLLBACK 恢复。
  3. 更高效：TRUNCATE 删除数据的效率高于 DELETE，因为它直接重置表，而不是逐行删除数据。它不会触发行级别日志的生成。
  4. 自增值重置：TRUNCATE 操作通常会将主键的自增值（AUTO_INCREMENT）重置为初始状态。
  5. 触发器限制：TRUNCATE 不会触发 BEFORE DELETE 和 AFTER DELETE 类型的触发器，因为它删除的是整个数据表内容。
  6. 空间释放：TRUNCATE 能有效释放表和索引占用的空间。

3.3 使用场景

  • 清空表中的所有数据，但保留表结构。
  • 希望快速删除大量数据（无条件删除）。

3.4 示例

1
2
-- 清空表中所有数据
TRUNCATE TABLE employees;

4. DELETE、DROP 和 TRUNCATE 的区别总结

以下是三者在主要方面的区别：

特性 DELETE DROP TRUNCATE
操作类型 数据操作语言（DML） 数据定义语言（DDL） 数据定义语言（DDL）
作用 删除特定行或全表 删除整个表或数据库 删除所有数据
是否保留表结构 是 否 是
事务支持 支持，可回滚 不支持，不可回滚 不支持，不可回滚
是否记录日志 是（逐行日志） 否 否
效率 相对较低 高（直接删除整个对象） 高（快速清空数据）
触发器支持 支持 不支持 不支持
自增主键处理 不重置 不重置 重置
内存/空间释放 仅释放行空间 释放所有关联的内存和空间 释放表内容及索引的占用空间

5. 总结

  • DELETE： 用于有选择性地删除表中的部分或全部数据，同时保留表和其结构。它支持事务，可回滚，但执行速度相对慢，适用于需要逐行删除或有条件地删除数据的场景。
  • DROP： 用于删除整个表或数据库，包括表结构和数据，执行后无法恢复。通常用于完全舍弃不需要的表时进行操作。
  • TRUNCATE： 用于快速清空表中的所有数据，保留表结构。当你需要快速清除数据，并不需要逐行记录日志或触发器时，是一种高效的选择。

在实践中，选择哪种操作要根据具体需求来确定。如果需要精确控制，可以使用 DELETE；如果不再需要表的结构和数据，选择 DROP；如果需要快速清空表中的数据，选择 TRUNCATE 更为合适。

6. 学习交流

如果你觉得文章有帮助，请帮忙转发给更多的好友，或关注公众号：猿java，持续输出硬核文章。

drawing