Skip navigation links

Additional languages
12.2.10 UPDATE Syntax »
Section Navigation      [Toggle]

12.2.9. TRUNCATE Syntax

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 tbl_name.frm 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 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 tbl_name AUTO_INCREMENT=1 after the TRUNCATE TABLE statement.)

In MySQL 3.23, TRUNCATE TABLE is mapped to COMMIT; DELETE FROM tbl_name, so it behaves like 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.