社区编辑申请
注册/登录
Truncate、Delete和Drop的六大区别!你知道几个?
开发 前端
delete 是逐行执行的,并且在执行时会把操作日志记录下来,以备日后回滚使用,所以 delete 的执行速度是比较慢的;而 truncate 的操作是先复制一个新的表结构,再把原先的表整体删除,所以它的执行速度居中,而 drop 的执行速度最快。

在 MySQL  中,使用 truncate、delete 和 drop 都可以实现表删除,但它们 3 个的使用场景和执行效果完全不同,接下来我们来盘点一下。

truncate、delete、drop区别概述

它们 3 个的区别如下表所示:

区别点

drop

truncate

delete

执行速度

较快

命令分类

DDL(数据定义语言)

DDL(数据定义语言)

DML(数据操作语言)

删除对象

删除整张表和表结构,以及表的索引、约束和触发器。

只删除表数据,表的结构、索引、约束等会被保留。

只删除表的全部或部分数据,表结构、索引、约束等会被保留。

删除条件(where)

不能用

不能用

可使用

回滚

不可回滚

不可回滚

可回滚

自增初始值

-

重置

不重置

接下来我们用案例来演示一下它们的区别。

准备工作

正式开始之前,我们先来创建一个用户表和用户测试数据,方便后续演示使用:

CREATE TABLE `userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT comment '编号',
`name` varchar(250) NOT NULL comment '姓名' unique,
`balance` decimal(10,2) NOT NULL DEFAULT '0.00' comment '账户余额',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
insert into userinfo values(1,'张三',1000),(2,'李四',500),(3,'王五',2000),(4,'李六',500);

创建的表结构和数据如下图所示:

图片

1.删除对象不同

delete 和 truncate 只删除表数据,不删除表结构,其中 delete 删除之后的结果如下:

图片

我们先将表还原到初始状态,再使用 truncate 执行删除操作,执行结果如下图所示:

图片

把表还原到初始状态,执行 drop 删除语句,执行结果如下图所示:

图片

从上述结果可以看出,delete 和 truncate 只删除表数据,而 drop 把表结构和表数据都删除了。

2.删除条件支持不同

truncate 和 drop 不支持添加 where 条件,而 delete 支持 where 条件,如下图所示:

图片

3.命令分类不同

truncate、delete 和 drop 所属 SQL 分类不同,SQL 分为以下 3 类:

  • DDL【DataDefinitionLanguage】数据定义语言,用来维护存储数据的结构代表指令: create、drop、alter、truncate。
  • DML【DataManipulationLanguage】数据操纵语言,用来对数据进行操作代表指令:insert,delete,update,DML 中又单独分了一个 DQL,数据查询语言,代表指令是 select。
  • DCL【DataControlLanguage】数据控制语言,主要负责权限管理和事务代表指令:grant,revoke,commit。

其中 delete 属于 DML,而 truncate 和 drop 属于 DDL。

PS:truncate 是先复制一个新的表结构,再把原有旧表结构和数据一起删除,所以它属于数据定义语言 DDL,而非数据操纵语言 DML。

4.回滚支持不同

delete 属于 DML 支持事务回滚操作,而 truncate 和 drop 属于 DDL,执行之后立马生效,且数据是不可恢复的,接下来我们来验证一下。首先先将 MySQL 的自动事务提交关闭,自动事务提交的默认值是“ON”也就是开启了自动提交,如下图所示:

图片

我们使用以下命令将自动提交(事务)关掉:

set autocommit=off;

再次查询事务自动提交的设置结果如下:

图片

接下来我们演示一下 delete 的回滚操作,如下图所示:

图片

从上述结果可以看出 delete 之后是可以进行恢复(回滚)的,而 truncate 和 drop 之后是不能回滚的,各位老铁可以使用相同的方法自行测试一下后两种 SQL 的执行。

5.自增初始化不同

delete 不会重置自增字段的初始值,如下图所示:

图片

而 truncate 会重置自增字段的初始值,如下图所示:

图片

6.执行速度不同

delete 是逐行执行的,并且在执行时会把操作日志记录下来,以备日后回滚使用,所以 delete 的执行速度是比较慢的;而 truncate 的操作是先复制一个新的表结构,再把原先的表整体删除,所以它的执行速度居中,而 drop 的执行速度最快。

总结

truncate、drop 和 delete 的区别主要有以下 6 点:

  • 执行速度:drop > truncate > detele。
  • delete 和 truncate 只删除表数据,而 drop 会删除表数据和表结构以及表的索引、约束和触发器。
  • delete 可以加 where 条件实现部分数据删除,而 truncate 和 drop 不能加 where 条件是整体删除。
  • truncate 和 drop 是立即执行,且不能恢复;而 delete 会走事务,可以撤回和恢复。
  • truncate 会重置自增列为 1,而 delete 不会重置自增列。
  • truncate 和 drop 是 DDL 语句,而 delete 是 DML 语句。
责任编辑:武晓燕 来源: Java面试真题解析
相关推荐

2022-06-08 07:34:25

InnoDBdeleteMySQL

2020-10-21 10:30:24

deletetruncatedrop

2021-10-12 06:56:05

2010-10-08 16:05:30

MySQL DELET

2010-05-20 09:01:22

MySQL数据库

2010-09-16 16:17:03

TRUNCATE TA

2010-10-22 16:40:27

SQL TRUNCAT

2020-12-07 06:25:14

同话题下的热门内容

Java 服务 Docker 容器化优秀实践程序员不得不知道的 API 接口常识TestOps完全手册:工作流、生命周期、团队和流程11个 ES2022(ES13)中惊人的 JavaScript 新特性不要在 Python 中使用循环,这些方法其实更棒!使用 Vite 和 TypeScript 带你从零打造一个属于自己的 Vue3 组件库你需要知道的TypeScript高级类型Hooks时代,如何写出高质量的react和vue组件?

编辑推荐

太厉害了,终于有人能把TCP/IP协议讲的明明白白了!牛人5次面试腾讯不成功的经验HBase原理–所有Region切分的细节都在这里了Javascript如何监听页面刷新和关闭事件如何搭建一个HTTPS服务端
我收藏的内容
点赞
收藏

51CTO技术栈公众号