MySQL事务不支持Truncate命令和其他操作一起回滚

Posted on Sep 10, 2022

Data Manipulation Language (DML) 数据操作语言（select，insert，update等，主要用于调整查询数据）
Data Definition Language (DDL) 数据定义语言（CREATE,TRUNCATE,DROP,ALTER等，主要用于调整数据库的定义结构等元数据）

根据https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html文档， mysql提供了原子性的DDL语句，每次执行truncate操作都会直接提交COMMIT，要么成功要么失败，但无法与其他语句一起组合成为事务
Atomic DDL is not transactional DDL. DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement. This means that DDL statements cannot be performed within another transaction, within transaction control statements such as START TRANSACTION … COMMIT, or combined with other statements within the same transaction.
偶尔遇到这样的需求，数据库某张表不断使用delete清理历史数据又插入新数据导致数据库的主键id不断增长，最后影响查询效率，需要定期truncate表格清空id后再插入新的数据，但truncate操作无法与后续的插入等操作一起组成事务。

只要表格没有外键的话可以用以下操作代替truncate操作，后续插入新数据失败时可以及时回滚数据到truncate之前。
创建一个和原表mytable结构一样的新表mytable_new，将原表改名为备份表mytable_backup，再将新表改名为原表的名字mytable。
使用RENAME命令同时对两张表改名是一个原子操作，可以确保数据库中任何时间都存在一张名叫mytable的表格。

  CREATE TABLE mytable_new LIKE mytable; 
  RENAME TABLE mytable TO mytable_backup,mytable_new TO mytable;

一些业务逻辑代码向当前的mytable插入数据，失败就抛出异常

如果插入成功，删除作为备份的原表

  DROP TABLE mytable_backup;

如果发现异常，将新表删除，将原表恢复

  RENAME TABLE mytable TO mytable_drop,mytable_backup TO mytable;
  DROP TABLE mytable_drop;