MySQL 8.0-13.1.37 TRUNCATE TABLE Statement

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE empties a table completely. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements.

TRUNCATE TABLE完全清空一个表。它需要DROP特权。从逻辑上讲，TRUNCATE TABLE类似于删除所有行的DELETE语句，或一系列DROP TABLE和CREATE TABLE语句。

To achieve high performance, TRUNCATE TABLE bypasses the DML method of deleting data. Thus, it does not cause ON DELETE triggers to fire, it cannot be performed for InnoDB tables with parent-child foreign key relationships, and it cannot be rolled back like a DML operation. However, TRUNCATE TABLE operations on tables that use an atomic DDL-supported storage engine are either fully committed or rolled back if the server halts during their operation. For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.

为了实现高性能，TRUNCATE TABLE绕过了删除数据的DML方法。因此，它不会触发ON DELETE触发器，它不能对具有父子外键关系的InnoDB表执行，也不能像DML操作那样回滚。但是，使用原子ddl支持的存储引擎的表上的TRUNCATE TABLE操作要么完全提交，要么在操作期间服务器停止时回滚。

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways:

虽然TRUNCATE TABLE与DELETE类似，但它被归类为DDL语句而不是DML语句。它与DELETE的不同之处在于:

• Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

• 截断操作删除并重新创建表，这比逐个删除行要快得多，特别是对于大型表。

• Truncate operations cause an implicit commit, and so cannot be rolled back. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

• 截断操作导致隐式提交，因此不能回滚。见第13.3.3节“导致隐性提交的陈述”。

• Truncation operations cannot be performed if the session holds an active table lock.

• 如果会话持有活动表锁，则无法执行截断操作

• TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

• 对于InnoDB或NDB表，如果有来自其他引用该表的表的外键约束，则TRUNCATE TABLE将失败。允许同一表的列之间的外键约束。

• Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”

• 截断操作不会返回已删除行数的有意义的值。通常的结果是“0行受影响”，这应该被解释为“没有信息”。

• As long as the table definition is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

• 只要表定义有效，就可以使用TRUNCATE table将表重新创建为空表，即使数据或索引文件已损坏。

• Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

• 任何AUTO_INCREMENT值都被重置为它的起始值。即使对于MyISAM和InnoDB来说也是如此，因为它们通常不重用序列值。

• When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions are unaffected.

• 当与分区表一起使用时，TRUNCATE TABLE保留分区;也就是说，数据和索引文件被删除并重新创建，而分区定义不受影响。

• The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

• TRUNCATE TABLE语句不会调用ON DELETE触发器。

• Truncating a corrupted InnoDB table is supported.

• 支持截断已损坏的InnoDB表。

TRUNCATE TABLE is treated for purposes of binary logging and replication as DDL rather than DML, and is always logged as a statement.

为了进行二进制日志记录和复制，将TRUNCATE TABLE作为DDL而不是DML处理，并且始终作为一条语句记录日志。

TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.

表的TRUNCATE TABLE关闭用HANDLER OPEN打开的表的所有处理程序。

In MySQL 5.7 and earlier, on a system with a large buffer pool and innodb_adaptive_hash_index enabled, a TRUNCATE TABLE operation could cause a temporary drop in system performance due to an LRU scan that occurred when removing the table's adaptive hash index entries (Bug #68184). The remapping of TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 avoids the problematic LRU scan.

在MySQL 5.7及更早的版本中，在一个有较大缓冲池且启用了innodb_adaptive_hash_index的系统上，一个TRUNCATE TABLE操作可能会导致系统性能暂时下降，这是由于在删除表的自适应哈西索引项时发生了LRU扫描(Bug #68184)。在MySQL 8.0中，将TRUNCATE TABLE重新映射为DROP TABLE和CREATE TABLE，避免了LRU扫描的问题。

TRUNCATE TABLE can be used with Performance Schema summary tables, but the effect is to reset the summary columns to 0 or NULL, not to remove rows. See Section 27.12.20, “Performance Schema Summary Tables”.

TRUNCATE TABLE可以与Performance Schema汇总表一起使用，但其效果是将汇总表列重置为0或NULL，而不是删除行。

Truncating an InnoDB table that resides in a file-per-table tablespace drops the existing tablespace and creates a new one. As of MySQL 8.0.21, if the tablespace was created with an earlier version and resides in an unknown directory, InnoDB creates the new tablespace in the default location and writes the following warning to the error log: The DATA DIRECTORY location must be in a known directory. The DATA DIRECTORY location will be ignored and the file will be put into the default datadir location. Known directories are those defined by the datadir, innodb_data_home_dir, and innodb_directories variables. To have TRUNCATE TABLE create the tablespace in its current location, add the directory to the innodb_directories setting before running TRUNCATE TABLE.

截断位于“file-per-table”表空间中的InnoDB表会删除现有的表空间并创建一个新的表空间。在MySQL 8.0.21中，如果表空间是用一个较早的版本创建的，并且位于一个未知的目录中，InnoDB会在默认位置创建新的表空间，并将以下警告写到错误日志中:数据目录位置必须在已知的目录中。DATA DIRECTORY位置将被忽略，文件将被放到默认的datadir位置。已知目录是指datadir、innodb_data_home_dir和innodb_directories变量定义的目录。要让TRUNCATE TABLE在其当前位置创建表空间，请在运行TRUNCATE TABLE之前将该目录添加到innodb_directories设置中。