再谈TRUNCATE TABLE

大家都知道，关系数据库中常用的删除数据命令有：

• DELETE;
• TRUNCATE TABLE;

如果在一个事务中，首先使用DELETE命令删除了某个表的数据，之后再执行了其它命令操作后发生了事务回滚，那么数据库会将被DELETE删除的数据恢复到事务开始之前的状态，就像什么都没有发生一样。

如果在上面这个例子中，删除数据使用的是TRUNCATE命令，而不是DELETE，那么发生了事务回滚后，结果会是怎样的呢？

SQL Server

在SQL Server中，发生事务回滚后，数据会恢复到事务最开始的状态。
这是因为在SQ Server中，针对表的DDL操作也都可以有事务控制的（TRUNCATE TABLE是DDL命令。另外一些特殊的DDL则无法被事务控制，例如，针对数据库以及全文索引的DDL）。

MySQL的TRUNCATE TABLE命令

在MySQL中，TRUNCATE命令是DDL命令，此命令运行后会自动触发COMMIT。所以发生事务回滚后，数据库会将数据恢复到TRUNCATE命令执行后的状态。这是熟悉SQL Server的同学在使用MySQL时最容易踩坑的地方之一。

接下来我们在MySQL中做几个测试验证下。

先要准备测试环境：

1. 禁用自动提交

在MySQL WorkBench的菜单Edit > Preferences > SQL Editor > SQL Execution设置中，uncheck New connections use auto commit mode复选框，这样就停用SQL查询的autocommit功能了。
关掉当前的连接窗口，重新在MySQL WorkBench的主页中打开一个新的连接，然后在查询窗口执行下面命令查看，@@autommit变成0了。

select @@autocommit;

2. 创建测试表

在MySQL WorkBench的查询窗口执行下面这段命令，创建测试用的表ab：

create table ab(a int primary key, b int);

现在可以做第一个测试了。在查询窗口执行下面的命令：

insert into ab values(1,1);
begin;
insert into ab values(2,2);
rollback;
SELECT * FROM ab;

执行结果返回了1条记录。第一条insert命令插入成功，而第二条插入命令被回滚了。

a	b
1	1

接下来是第二个测试，在查询窗口执行下面的命令：

insert into ab values(2,2);
begin;
truncate table ab;
insert into ab values(3,3);
rollback;
SELECT * FROM ab;

执行结果返回了0条记录，说明第一次测试的记录(1,1)也全都被truncate命令清除了，而且回滚操作并没有将清除的数据回滚。

这是因为在MySQL中，一些DDL命令会触发隐式的事务提交的。而truncate命令是DDL命令。所以会出现这个现象。另外，细心的同学可能在第一个测试例子中也发现了这个问题，insert into ab values(1,1);语句执行后并没有执行过commit命令，但是为什么最终的结果却是被提交了？也是因为begin命令触发了之前事务的隐式提交。

参考：https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html
13.3.3 Statements That Cause an Implicit Commit

• Data definition language (DDL) statements that define or modify database objects. ...TRUNCATE TABLE...
• Transaction-control and locking statements. BEGIN...