How to Truncate a Table in MySQL

Truncating a table is a fast and efficient way to delete all the records in a table without removing the table itself. The TRUNCATE TABLE statement in MySQL is often preferred over the DELETE statement when you want to remove all rows from a table, as it doesn't generate individual row delete operations, making it faster for large datasets.

Syntax

TRUNCATE TABLE table_name;

The TRUNCATE TABLE statement is used to remove all rows from a table quickly and reset any auto-increment counters to zero. This action is irreversible, so be cautious when using it.

Steps to Truncate a Table

1. Open your MySQL command-line client or any MySQL interface you prefer.
2. Select the database that contains the table you want to truncate using the command:

USE database_name;

3. Execute the truncate statement to remove all rows from the table:

TRUNCATE TABLE your_table_name;

4. Check if the table is empty:

SELECT * FROM your_table_name;

Considerations

• Speed: Truncating a table is faster than deleting rows one by one using the DELETE statement.
• Auto-Increment: The TRUNCATE command resets the auto-increment counter for the table, which might not be the case with DELETE.
• Locks: Unlike DELETE, which can be rolled back, TRUNCATE is a DDL command and can't be rolled back if used outside of a transaction.
• Triggers: Truncating a table does not activate any DELETE triggers that may be set up on the table.

Example

Suppose we have a table named employees. If we want to remove all records from this table, we can simply execute:

TRUNCATE TABLE employees;

After executing this command, the table will be empty, and the auto-increment counter will be reset to zero.