TRUNCATE TABLE empties a table completely.
Logically, this is equivalent to a
statement that deletes all rows, but there are practical
differences under some circumstances.
TABLE is mapped to
DELETE if there
are foreign key constraints that reference the table; otherwise
fast truncation (dropping and re-creating the table) is used.
AUTO_INCREMENT counter is reset by
TRUNCATE TABLE, regardless of whether there
is a foreign key constraint.
For other storage engines,
DELETE in the following ways in
Truncate operations drop and re-create the table, which is
much faster than deleting rows one by one.
Truncate operations are not transaction-safe; an error
occurs when attempting one in the course of an active
transaction or active table lock.
The number of deleted rows is not returned.
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_INCREMENT value, but starts counting
from the beginning. This is true even for
which normally do not reuse sequence values.
When used with partitioned tables,
TABLE preserves the partitioning; that is, the
data and index files are dropped and re-created, while the
partition definitions (
.par) file is
TRUNCATE TABLE is an Oracle SQL extension
adopted in MySQL.