TRUNCATE TABLE is
similar to a
DELETE statement that
deletes all rows, or a sequence of
statements. To achieve high performance, it bypasses the DML
method of deleting data. Thus, it cannot be rolled back, it does
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.
TRUNCATE 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
TRUNCATE 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
InnoDB, which normally do not reuse
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
.par) file is unaffected.
TRUNCATE TABLE statement
does not invoke
ON DELETE triggers.
TRUNCATE TABLE is treated for
purposes of binary logging and replication as
DROP TABLE followed by
CREATE 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 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
On a system with a large
InnoDB buffer pool and
TRUNCATE TABLE operations may cause a
temporary drop in system performance due to an LRU scan that
occurs when removing an
InnoDB table's adaptive
hash index entries. The problem was addressed for
DROP TABLE in MySQL 5.5.23 (Bug
#13704145, Bug #64284) but remains a known issue for
TRUNCATE TABLE (Bug #68184).
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 22.7.5, “Performance Schema Summary Tables”.