Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.4Mb
PDF (A4) - 37.5Mb
PDF (RPM) - 33.2Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.3Mb
Man Pages (TGZ) - 130.1Kb
Man Pages (Zip) - 185.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb

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

Pre-General Availability Draft: 2018-02-25

13.1.34 TRUNCATE TABLE Syntax


TRUNCATE TABLE empties a table completely. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements.

To achieve high performance, TRUNCATE TABLE bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways:

  • 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, and so cannot be rolled back. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

  • Truncation operations cannot be performed if the session holds an active table lock.

  • TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

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

  • Any AUTO_INCREMENT value is reset to its start value. 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 are unaffected.

  • The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.

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.

In MySQL 5.7 and earlier, on a system with a large buffer pool and innodb_adaptive_hash_index enabled, a TRUNCATE TABLE operation could cause a temporary drop in system performance due to an LRU scan that occurred when removing the table's adaptive hash index entries (Bug #68184). The remapping of TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 avoids the problematic LRU scan.

TRUNCATE TABLE can be used with Performance Schema summary tables, but the effect is to reset the summary columns to 0 or NULL, not to remove rows. See Section 26.11.15, “Performance Schema Summary Tables”.

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.