乐趣区

关于java:面试突击58truncatedelete和drop的6大区别

jiezi

在 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 类：

  1. DDL【Data Definition Language】数据定义语言，用来保护存储数据的构造代表指令: create、drop、alter、truncate。
  2. DML【Data Manipulation Language】数据操纵语言，用来对数据进行操作代表指令：insert，delete，update，DML 中又独自分了一个 DQL，数据查询语言，代表指令是 select。
  3. DCL【Data Control Language】数据管制语言，次要负责权限治理和事务代表指令：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 点：

  1. 执行速度：drop > truncate > detele。
  2. delete 和 truncate 只删除表数据，而 drop 会删除表数据和表构造以及表的索引、束缚和触发器。
  3. delete 能够加 where 条件实现局部数据删除，而 truncate 和 drop 不能加 where 条件是整体删除。
  4. truncate 和 drop 是立刻执行，且不能复原；而 delete 会走事务，能够撤回和复原。
  5. truncate 会重置自增列为 1，而 delete 不会重置自增列。
  6. truncate 和 drop 是 DDL 语句，而 delete 是 DML 语句。

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

公众号：Java 面试真题解析

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

退出移动版