TRUNCATE TABLE is similar to a
DELETE statement that deletes all rows, or a sequence of
DROP TABLE and
TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it
cannot be rolled back, it does not cause
ON DELETE triggers to fire, and it cannot
be performed for
InnoDB tables with parent-child foreign key relationships.
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.
Truncation operations cannot be performed if the session holds an active table lock.
TABLE fails for an
InnoDB 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.
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 format file
is valid, the table can be re-created as
an empty table with
TABLE, even if the data or index files have become corrupted.
AUTO_INCREMENT value is reset to its start value.
This is true even for
normally do not reuse sequence values.
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 (
file is unaffected.
TABLE statement does not invoke
ON DELETE triggers.
TRUNCATE TABLE is treated for purposes of binary logging and replication as
TABLE followed by
TABLE—that is, as DDL rather than DML. This is due to the fact that, when using
InnoDB and other transactional storage engines where the transaction isolation
level does not permit statement-based logging (
READ COMMITTED or
READ UNCOMMITTED), the statement was not logged and replicated when using
MIXED logging mode. (Bug #36763)
However, it is still applied on replication slaves using
InnoDB in the manner described previously.
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 20.9.8, "Performance Schema Summary