MySQL :: MySQL 8.4 参考手册 :: 15.1.37 TRUNCATE TABLE 语句

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 15 条 SQL 语句 / 15.1 数据定义语句 / 15.1.37 TRUNCATE TABLE 语句

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

15.1.37 TRUNCATE TABLE 语句15.1.37 TRUNCATE TABLE Statement

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE清空表。这需要 DROP 权限。逻辑上，TRUNCATE TABLE类似于 DELETE 语句删除所有行，或者一系列 DROP TABLE and CREATE TABLE 语句。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过了 DML 删除数据的方法。因此，它不会导致 ON DELETE 触发触发，也无法对具有父-子外键关系的 InnoDB，也不能像 DML 操作那样回滚。然而，TRUNCATE TABLE对于使用支持原子 DDL 的存储引擎的表，要么完全提交，要么如果服务器在操作过程中停止，则会被回滚。更多信息请参见第 15.1.1 节，“Atomic Data Definition Statement Support”。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 a storage engine which supports atomic DDL are either fully committed or rolled back if the server halts during their operation. For more information, see Section 15.1.1, “Atomic Data Definition Statement Support”.

尽管 TRUNCATE TABLE与 DELETE，但它被归类为 DDL 语句而非 DML 语句。它与 DELETEAlthough 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 operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
截断操作会导致隐式提交，因此无法回滚。参见第 15.3.3 节，”导致隐式提交的语句“。Truncate operations cause an implicit commit, and so cannot be rolled back. See Section 15.3.3, “Statements That Cause an Implicit Commit”.
如果会话持有活跃的表锁，则无法执行截断操作。Truncation operations cannot be performed if the session holds an active table lock.
TRUNCATE TABLE 在 InnoDB table 或 NDB table，如果存在引用该表的其他表的 FOREIGN KEY。同一表的列之间允许使用外键约束。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.
截断操作不会返回删除行数的有意义值。通常的结果是 “0 行受影响”，应解释为 “无信息。”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.”
只要表定义有效，表可以重新创建为空表，TRUNCATE TABLE即使数据或索引文件损坏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.
。任何 AUTO_INCREMENT 值都会重置为起始值。即使是 MyISAM 和 InnoDB，通常不会重用序列值，这也同样适用。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.
当用于分区表时，TRUNCATE TABLE 保留分区;也就是说，数据和索引文件会被丢弃并重新创建，而分区定义不受影响。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 语句不会调用 ON DELETE。The TRUNCATE TABLE statement does not invoke ON DELETE triggers.
支持截断损坏的 InnoDB 表。Truncating a corrupted InnoDB table is supported.

TRUNCATE TABLE二进制日志和复制中被视为 DDL 而非 DML，并且始终作为语句记录。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 for a table 会关闭所有用 HANDLER OPEN。TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.

TRUNCATE TABLE 可以与性能模式的汇总表一起使用，但其效果是将摘要列重置为 0 或 NULL而不是移除行。参见第 29.12.20 节，“Performance Schema Summary Tables”。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 29.12.20, “Performance Schema Summary Tables”.

Truncating an InnoDB table that resides in a file-per-table tablespace drops the existing tablespace and creates a new one. 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，以及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.Truncating an InnoDB table that resides in a file-per-table tablespace drops the existing tablespace and creates a new one. 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.

首页上一级下一节