If there are no
InnoDBperforms 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.
When you use this fast truncation technique with the
innodb_file_per_tableoption enabled, the operating system can reuse the freed disk space. For users of the InnoDB Plugin, the space is reclaimed automatically, as described in Reclaiming Disk Space with TRUNCATE TABLE. If you do not have the InnoDB Plugin installed, issue the
OPTIMIZE TABLEstatement to free the disk space for the table.
If there are any
FOREIGN KEYconstraints that reference the table,
TRUNCATE TABLEby deleting rows one by one, processing the constraints as it proceeds. If the
FOREIGN KEYconstraint specifies
DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the
FOREIGN KEYconstraint does not specify
TRUNCATE TABLEstatement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))Note
In MySQL 5.5 and higher,
TRUNCATE TABLEis not allowed for
InnoDBtables referenced by foreign keys. For ease of upgrading, rewrite such statements to use
AUTO_INCREMENTcounter is reset to zero by
TRUNCATE TABLE, regardless of whether there is a
This is the same as a
statement with no
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
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.
The table handler does not remember the last used
AUTO_INCREMENTvalue, but starts counting from the beginning. This is true even for
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.
Beginning with MySQL 5.1.32,
TABLE is treated for purposes of binary logging and
DROP TABLE followed
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