TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE TABLE by deleting rows one by one. As of MySQL 5.0.3, row by row deletion is used only if there are any FOREIGN KEY constraints that reference the 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. (When fast truncation is used, it resets any AUTO_INCREMENT counter. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset by TRUNCATE TABLE, 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 not specify CASCADE, the TRUNCATE 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 DELETE statement with no WHERE clause.

The count of rows affected by TRUNCATE TABLE is accurate only when it is mapped to a DELETE statement.

For other storage engines, TRUNCATE TABLE differs from DELETE in the following ways in MySQL 5.0: