MySQL 5.1 Reference Manual  /  ...  /  TRUNCATE TABLE Syntax

13.1.34 TRUNCATE TABLE Syntax


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:

  • If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.

  • When you use this fast truncation technique with the innodb_file_per_table option enabled, the operating system can reuse the freed disk space. For users of the InnoDB Plugin, the space is reclaimed automatically, as described in Reclaiming Disk Space with TRUNCATE TABLE. If you do not have the InnoDB Plugin installed, issue the OPTIMIZE TABLE statement to free the disk space for the table.

  • If there are any FOREIGN KEY constraints that reference the table, InnoDB processes TRUNCATE TABLE by deleting rows one by one, processing the constraints as it proceeds. If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the FOREIGN KEY constraint does not specify CASCADE, the TRUNCATE TABLE statement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error:

    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
    key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1`
    FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

    In MySQL 5.5 and higher, TRUNCATE TABLE is not allowed for InnoDB tables referenced by foreign keys. For ease of upgrading, rewrite such statements to use DELETE instead.

  • The AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE, regardless of whether there is a FOREIGN KEY constraint.

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:

  • Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

  • Truncate operations cause an implicit commit.

  • 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.

  • 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.

  • Since truncation of a table does not make any use of DELETE, the TRUNCATE TABLE statement does not invoke ON DELETE triggers.

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.

User Comments
  Posted by Yi Peng on August 29, 2010
On Windows, MySQL server 5.1 defaults to safe update mode, even though safe-updates is not enabled in my.ini (a bug possibly?). Truncating table results in error listed below.

Message: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

I found that the easiest way to disable the safe mode is to execute this SQL statement first to disable safe update mode temporarily.

SET sql_safe_updates=0

Defaulting to safe update mode is not bad after all.

Sign Up Login You must be logged in to post a comment.