Additional languages
« 13.2.8.11 Rewriting Subqueries as Joins for Earlier MySQL Versions
13.2.10 UPDATE Syntax »
Section Navigation      [Toggle]

13.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 before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference. Starting with MySQL 5.0.3, fast TRUNCATE TABLE is available. However, the operation is still mapped to DELETE if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any AUTO_INCREMENT counter. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)

For other storage engines, TRUNCATE TABLE differs from DELETE in the following ways in MySQL 5.0:

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

  • Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.

  • The number of deleted rows is not returned.

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

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

TRUNCATE TABLE is an Oracle SQL extension adopted in MySQL.


User Comments

Posted by Kris Craig on February 27 2007 11:28pm[Delete] [Edit]

This comment is specifically regarding version 4.1.21. I keep trying to post it to the "truncate syntax" entry for the "3.23, 4.0, 4.1" manual, but after I post it goes to 5.x instead. Instead of just deleting it, could you guys perhaps move it to the appropriate section, or fix your website so I can post it there myself?.... Here's the comment:

In certain versions of MySQL, truncate does NOT reset the auto_increment value to 0! Instead, it is mapped to DELETE. Make sure you consider this when designing your database, as the current manual does not seem to address this issue.

More information can be found here: http://bugs.mysql.com/11946

The above URL states that the bug is limited to unpatched versions 5.0.xx prior to version 5.0.3. However, I have noticed the same behavior in version 4.1.21.

That being said, here's a very simple way to get around this problem without having to look for a patch and whatnot. After you use truncate to delete the contents of the table, you can use the alter table command to reset the auto increment manually. With that in mind, try this on the
table you want to clear:

truncate <table name>;
alter table <table name> auto_increment=1;


Adding that extra command at the bottom should make-up for the reduced truncate functionality. I hope this helps! =)

Add your own comment.