TRUNCATE [TABLE] tbl_name
TRUNCATE TABLE empties a table
completely. It requires the
TABLE is similar to a
DELETE statement that deletes all
rows, or a sequence of
CREATE TABLE statements.
To achieve high performance,
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,
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 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”.
Truncation operations cannot be performed if the session holds an active table lock.
TRUNCATE TABLEfails for an
NDBtable if there are any
FOREIGN KEYconstraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.
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.”
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_INCREMENTvalue is reset to its start value. This is true even for
InnoDB, which normally do not reuse sequence values.
When used with partitioned tables,
TRUNCATE TABLEpreserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions are unaffected.
TRUNCATE TABLEstatement does not invoke
Truncating a corrupted
InnoDBtable is supported.
TRUNCATE TABLE is treated for
purposes of binary logging and replication as DDL rather than DML,
and is always logged as a statement.
In MySQL 5.7 and earlier, on a system with a large buffer pool and
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
DROP TABLE and
CREATE TABLE in MySQL 8.0 avoids
the problematic LRU scan.
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”.
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
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
innodb_directories variables. To
TRUNCATE TABLE create the
tablespace in its current location, add the directory to the
innodb_directories setting before