13.1.34 TRUNCATE TABLE Statement
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 TABLE
fails for anInnoDB
table orNDB
table if there are anyFOREIGN 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 withtbl_name
.frmTRUNCATE TABLE
, even if the data or index files have become corrupted.Any
AUTO_INCREMENT
value is reset to its start value. This is true even forMyISAM
andInnoDB
, which 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 (.par
) file is unaffected.The
TRUNCATE TABLE
statement does not invokeON DELETE
triggers.
TRUNCATE TABLE
for a table closes all handlers for the table that were opened with HANDLER OPEN
.
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 COMMITTED
or READ UNCOMMITTED
), the statement was not logged and replicated when using STATEMENT
or MIXED
logging mode. (Bug #36763) However, it is still applied on replication slaves using InnoDB
in the manner described previously.
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”.