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 in
MySQL 5.5:
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 TABLEfails for anInnoDBtable if there are anyFOREIGN KEYconstraints from other tables that reference the table. Foreign key constraints between columns of the same table are allowed.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 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.
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 21.7.4, “Performance Schema Summary Tables”.