13.1.34 TRUNCATE TABLE Syntax

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE empties a table completely. It requires the DROP privilege as of MySQL 5.1.16. (Before 5.1.16, it requires the DELETE privilege).

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

For an InnoDB table:

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.1:

As of MySQL 5.1.39, TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.

Beginning with MySQL 5.1.32, TRUNCATE TABLE is treated for purposes of binary logging and replication as DROP TABLE followed by CREATE TABLE—that is, as DDL rather than DML. This is due to the fact that, when using InnoDB and other transactional storage engines where the transaction isolation level does not permit statement-based logging (READ COMMITTED or READ UNCOMMITTED), the statement was not logged and replicated when using STATEMENT or MIXED logging mode. (Bug #36763) However, it is still applied on replication slaves using InnoDB in the manner described previously.