TRUNCATE [TABLE] tbl_name [WAIT n | NOWAIT]
TRUNCATE TABLE empties a table completely. It requires the
DROP privilege (before 5.1.16, it required the
DELETE privilege.) See
tbl_name can also be specified in the form
tbl_name (see Identifier Qualifiers).
TRUNCATE TABLE is equivalent to a
DELETE statement that deletes all rows, but there are practical differences under some circumstances.
TRUNCATE TABLE is faster than
DELETE, because it drops and re-creates a table. With XtraDB/InnoDB, this is faster if the innodb_file_per_table variable is ON.
TRUNCATE TABLE will fail for an InnoDB table if any FOREIGN KEY constraints from other tables reference the table, returning the error:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
Constraints between columns in the same table are permitted.
For an InnoDB table, if there are no
FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one. The AUTO_INCREMENT counter is reset by
TRUNCATE TABLE, regardless of whether there is a
FOREIGN KEY constraint.
The count of rows affected by
TRUNCATE TABLE is accurate only
when it is mapped to a
For other storage engines,
TRUNCATE TABLE 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.
- Truncation operations cannot be performed if the session holds an active table lock.
- 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.frmis valid, the table can be re-created as an empty table with
TRUNCATE TABLE, even if the data or index files have become corrupted.
- The table handler does not remember the last
AUTO_INCREMENTvalue, but starts counting from the beginning. This is true even for MyISAM and InnoDB, 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.
- Since truncation of a table does not make any use of
TRUNCATEstatement does not invoke
TRUNCATE TABLEwill only reset the values in the Performance Schema summary tables to zero or null, and will not remove the rows.
For the purposes of binary logging and replication,
TRUNCATE TABLE is treated as
DROP TABLE followed by
CREATE TABLE (DDL rather than DML).
TRUNCATE TABLE does not work on views.