乐趣区

关于mysql:面试突击55deletedroptruncate有什么区别

jiezi

在 MySQL 中，删除的办法总共有 3 种：delete、truncate、drop，而三者的用法和应用场景又齐全不同，接下来咱们具体来看。

1.delete

detele 可用于删除表的局部或所有数据，它的应用语法如下：

delete from table_name [where...] [order by...] [limit...]

PS：[] 中的命令为可选命令，能够被省略。

如果咱们要删除学生表中数学成绩排名最高的前 3 位学生，能够应用以下 SQL：

delete from student order by math desc limit 3;

1.1 delete 实现原理

在 InnoDB 引擎中，delete 操作并不是真的把数据删除掉了，而是给数据打上删除标记，标记为删除状态，这一点咱们能够通过将 MySQL 设置为非主动提交模式，来测试验证一下。
非主动提交模式的设置 SQL 如下：

set autocommit=0;

之后先将一个数据 delete 删除掉，而后再应用 rollback 回滚操作，最初验证一下咱们之前删除的数据是否还存在，如果数据还存在就阐明 delete 并不是真的将数据删除掉了，只是标识数据为删除状态而已，验证 SQL 和执行后果如下图所示：

1.2 对于自增列

在 InnoDB 引擎中，应用了 delete 删除所有的数据之后，并不会重置自增列为初始值，咱们能够通过以下命令来验证一下：

2.truncate

truncate 执行成果和 delete 相似，也是用来删除表中的所有行数据的，它的应用语法如下：

truncate [table] table_name

truncate 在应用上和 delete 最大的区别是，delete 能够应用条件表达式删除局部数据，而 truncate 不能加条件表达式，所以它只能删除所有的行数据 ，比方以下 truncate 增加了 where 命令之后就会报错：

2.1 truncate 实现原理

truncate 看似只删除了行数据，但它却是 DDL 语句，也就是 Data Definition Language 数据定义语言，它是用来保护存储数据的构造指令，所以这点也是和 delete 命令是不同的，delete 语句属于 DML，Data Manipulation Language 数据操纵语言，用来对数据进行操作的。
为什么 truncate 只是删除了行数据，没有删除列数据（字段和索引等数据）却是 DDL 语言呢？
这是因为 truncate 实质上是新建了一个表构造，再把原先的表删除掉，所以它属于 DDL 语言，而非 DML 语言。

2.2 重置自增列

truncate 在 InnoDB 引擎中会重置自增列，如下命令所示：

3.drop

drop 和前两个命令只删除表的行数据不同，drop 会把整张表的行数据和表构造一起删除掉，它的语法如下：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name]

其中 TEMPORARY 是长期表的意思，个别状况下此命令都会被疏忽。

drop 应用示例如下：

三者的区别

  1. 数据恢复方面：delete 能够复原删除的数据，而 truncate 和 drop 不能复原删除的数据。
  2. 执行速度方面：drop > truncate > delete。
  3. 删除数据方面：drop 是删除整张表，蕴含行数据和字段、索引等数据，而 truncate 和 drop 只删除了行数据。
  4. 增加条件方面：delete 能够应用 where 表达式增加查问条件，而 truncate 和 drop 不能增加 where 查问条件。
  5. 重置自增列方面：在 InnoDB 引擎中，truncate 能够重置自增列，而 delete 不能重置自增列。

总结

delete、truncate 可用于删除表中的行数据，而 drop 是把整张表全副删除了，删除的数据蕴含所有行数据和字段、索引等数据，其中 delete 删除的数据能够被复原，而 truncate 和 drop 是不可复原的，但在执行效率上，后两种删除形式又有很大的劣势，所以要依据理论场景来抉择相应的删除命令，当然 truncate 和 drop 这些不可复原数据的删除形式应用的时候也要小心。

是非审之于己，毁誉听之于人，得失安之于数。

公众号：Java 面试真题解析

面试合集：https://gitee.com/mydb/interview

退出移动版