写点什么

面试突击 55:delete、drop、truncate 有什么区别?

作者:王磊
  • 2022 年 6 月 08 日
  • 本文字数:1446 字

    阅读完需:约 5 分钟

面试突击55:delete、drop、truncate有什么区别?

在 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

用户头像

王磊

关注

公众号:Java中文社群 2018.08.25 加入

公众号:Java中文社群

评论

发布
暂无评论
面试突击55:delete、drop、truncate有什么区别?_Java_王磊_InfoQ写作社区