Online Documentation for SQL Manager for MySQL

Truncate Tables

Truncate Table Wizard empties table(s) completely.

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

In MySQL 3.23, TRUNCATE TABLE is mapped to COMMIT; DELETE FROM tbl_name, so it behaves like DELETE.

For InnoDB (before MySQL 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. The operation is still mapped to DELETE if there are foreign key constraints that reference the table.

For other storage engines, TRUNCATE TABLE differs from DELETE FROM in the following ways (since MySQL 4.0 and later):

• truncate operations drop and recreate the table which is much faster than deleting rows one by one;
• truncate operations are not transaction-safe; you get an error if you have an active transaction or an active table lock;
• the number of deleted rows is not returned;
• as long as the table definition file tbl_name.frm is valid, the table can be recreated 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.

To run the wizard, select the Services | Truncate Tables... main menu item, or open a table in Table Editor and use the Truncate table item of the Navigation bar.

• Selecting database
• Selecting tables for truncating
• Viewing routine results