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
, TRUNCATE
TABLE
is mapped to
DELETE
, so there is no difference.
For other storage engines, TRUNCATE
TABLE
differs from DELETE
in the following ways from MySQL 4.0 onward:
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 4.1.13, truncate operations cause an implicit commit. Before 4.1.13, 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
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. (Some older versions
may not reset the AUTO_INCREMENT
value. In
this case, you can use ALTER TABLE
after the tbl_name
AUTO_INCREMENT=1TRUNCATE TABLE
statement.)
In MySQL 3.23, TRUNCATE TABLE
is
mapped to COMMIT; DELETE FROM
, so it behaves like
tbl_name
DELETE
. See
Section 12.2.1, “DELETE
Syntax”.
TRUNCATE TABLE
was added in MySQL
3.23.28, although from 3.23.28 to 3.23.32, the keyword
TABLE
must be omitted.
User Comments
Add your own comment.