mysql truncate table导致事务执行失败There is no active transaction

2022年7月25日10:59:44

开发环境 php8 laravel8 mysql8

在做一个动态文件管理的时候，发现如果我每次需要清空某个表的时候，在事务里面truncate table会触发
There is no active transaction的异常

DB::table('users')->delete();
DB::table('users')->truncate();

我使用delete的表，才没有问题，我就好奇delete truncate的区别


TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

TRUNCATE TABLE 不能用于参与了索引视图的表。所以它不能激活触发器。

对用TRUNCATE TABLE删除数据的表上增加数据时，要使用UPDATE STATISTICS来维护索引信息。
如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE不会撤销。

新增一条
如果在同一格表的事务里面使用了TRUNCATE TABLE 会造成事务处理失败，触发There is no active transaction

DELETE 和 TRUNCATE 之间的主要区别
以下几点解释了 delete 和 truncate 命令之间的区别：

当我们想从表中删除部分或全部记录时使用 DELETE 语句，而 TRUNCATE 语句将从表中删除整行。
DELETE 是一个 DML 命令，因为它只修改表数据，而 TRUNCATE 是一个 DDL 命令。
DELETE 命令可以使用 WHERE 子句过滤记录/元组。但是，TRUNCATE 命令不允许使用WHERE子句，因此我们无法在截断时过滤行。
DELETE 激活表上的所有删除触发器以触发。但是，不会在 truncate 操作上触发任何触发器，因为它不对单个行进行操作。
DELETE 从表中逐行执行删除，按处理顺序。但是，TRUNCATE 对数据页而不是行进行操作，因为它一次删除了整个表数据。
DELETE 语句只删除记录并且不重置表的身份，而 TRUNCATE 重置特定表的身份。
DELETE 命令需要更多的锁和数据库资源，因为它会在每个已删除的行上获取锁。相反，TRUNCATE 在删除数据页之前获取数据页上的锁；因此，它需要更少的锁和更少的资源。
DELETE 语句在事务日志中为每个删除的行创建一个条目，而 TRUNCATE 记录每个数据页的事务日志。
TRUNCATE 命令比 DELETE 命令快，因为它释放数据页而不是行，并在事务日志中记录数据页而不是行。
一旦使用 TRUNCATE 命令删除了记录，我们就无法将其恢复。相反，我们可以恢复从 DELETE 操作中删除的已删除数据。
DELETE 与 TRUNCATE 比较表
以下比较图表快速解释了它们的主要区别：

比较基础	DELETE							truncate 
定义	delete 语句用于根据指定条件从现有表中删除单个或多个记录。	truncate 命令从现有表中删除完整数据，而不是表本身。它保留了表结构或模式。
语	它是一个 DML(数据操作语言)命令。				它是一个 DDL(数据定义语言)命令。
在哪里	它可以使用 WHERE 子句过滤表中的任何特定行或数据。		它不使用 WHERE 子句从表中过滤记录。
允许	我们需要有 DELETE 权限才能使用这个命令。			我们需要有 ALTER 权限才能使用这个命令。
在职的	此命令会逐一删除记录。					此命令删除包含记录的整个数据页。
锁	它将在删除之前锁定该行。					它会在删除前锁定数据页。
表标识	此命令不会重置表标识，因为它只会删除数据。			它总是重置表标识。
交易	它维护每条已删除记录的事务日志。				它不维护每个已删除数据页面的事务日志。
速度	它的速度很慢，因为它维护了日志。				它的执行速度很快，因为它一次删除了整个数据而无需维护事务日志。
扳机	此命令还可以激活应用于表的触发器并触发它们。			此命令不会激活应用于要触发的表的触发器。
恢复	它允许我们使用 COMMIT 或 ROLLBACK 语句来恢复已删除的数据。	执行此命令后，我们无法恢复已删除的数据。
索引视图	它可以与索引视图一起使用。					它不能与索引视图一起使用。
空间	DELETE 语句比 truncate 占用更多的事务空间，因为它为每个删除的行维护一个日志。	TRUNCATE 语句占用较少的事务空间，因为它为整个数据页而不是每一行维护一个事务日志。