How to Quickly Reset MySQL Tables with the Truncate Command

In MySQL, truncating a table lets you quickly delete all rows and reset the table to an empty state. It’s useful if you want to clear out sample data or reset tables during development or testing. It’s also irreversible, so you should obviously be careful when using it.

This post covers how to truncate a table in MySQL while also being careful not to mess up your database.

What is truncate table in MySQL?

The TRUNCATE TABLE command provides a speed advantage over the DELETE FROM command for removing all rows in a table because it operates faster and consumes fewer system and transaction log resources. TRUNCATE TABLE bypasses the logging of individual row deletions that DELETE FROM entails, performing the operation in a single step.

How to use truncate table in MySQL?

To perform a table truncation, you can follow this simple SQL syntax:

TRUNCATE TABLE table_name;

Simply replace table_name with the name of your target table. For instance, to truncate a users table, you would write:

TRUNCATE TABLE users;

Some things to bear in mind:

• Permissions: Make sure you have the DROP privilege on the table you wish to truncate.
• Referential integrity: If a foreign key constraint references the table, you might need to remove the constraint or truncate the referencing table first.
• Auto-increment values: Truncating resets any auto-increment value back to its start value, typically 1, which does not happen with DELETE FROM.
• Non-transactional: Truncating a table is usually non-transactional, meaning you cannot undo it once executed. However, with InnoDB tables, truncating is transactional and can be rolled back if part of a larger transaction.