InnoDB table before version 5.0.3,
TABLE by deleting rows one by one. As of MySQL 5.0.3,
row by row deletion is used only if there are any
KEY constraints that reference the table. If there are
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. (When fast
truncation is used, it resets any
AUTO_INCREMENT counter to zero. From MySQL
5.0.13 on, the
AUTO_INCREMENT counter is reset
to zero by
regardless of whether there is a foreign key constraint.)
In the case that
FOREIGN KEY constraints
reference the table,
InnoDB deletes rows one by
one and processes the constraints on each one. If the
FOREIGN KEY constraint specifies
DELETE CASCADE, rows from the child
(referenced) table are deleted, and the truncated table becomes
empty. If the
FOREIGN KEY constraint does
TRUNCATE TABLE statement 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`))
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.
As of MySQL 5.0.8, truncate operations cause an implicit commit. Before 5.0.8, truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction.
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.