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 InnoDB tables, TRUNCATE
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.
The AUTO_INCREMENT counter is reset by
TRUNCATE TABLE, regardless of whether there
is a foreign key constraint.
For other storage engines, TRUNCATE TABLE
differs from DELETE in the following ways in
MySQL 5.1:
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
tbl_name.frmTRUNCATE 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
MyISAM and InnoDB,
which normally do not reuse sequence values.
When used with partitioned tables, TRUNCATE
TABLE preserves the partitioning; that is, the
data and index files are dropped and re-created, while the
partition definitions (.par) file is
unaffected.
TRUNCATE TABLE is an Oracle SQL extension
adopted in MySQL.
