MySQL 5.7 Release Notes
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. 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.
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 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 anInnoDBtable orNDBtable if there are anyFOREIGN 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 format file
tbl_name.frmTRUNCATE TABLE, even if the data or index files have become corrupted.Any
AUTO_INCREMENTvalue is reset to its start value. This is true even forMyISAMandInnoDB, 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 (.par) file is unaffected.The
TRUNCATE TABLEstatement does not invokeON DELETEtriggers.
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 closes
all handlers for the table that were opened with
HANDLER OPEN.
On a system with a large InnoDB buffer pool and
innodb_adaptive_hash_index
enabled, 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 25.12.15, “Performance Schema Summary Tables”.