MySQL TRUNCATE Table Command - Complete Tutorial

What is the TRUNCATE TABLE Command?

TRUNCATE TABLE is a MySQL command used to delete all rows from a table efficiently.

Unlike DELETE, which removes rows one by one, TRUNCATE deallocates the data pages used by the table, making it much faster.

• Removes all rows from a table instantly.
• Resets AUTO_INCREMENT counters to zero.
• Cannot be used with a WHERE clause.
• Is a DDL operation, not DML.

Syntax of TRUNCATE TABLE

The syntax for truncating a table is straightforward:

You specify the table name after the TRUNCATE TABLE keyword.

TRUNCATE TABLE vs DELETE

While both TRUNCATE and DELETE remove data from tables, they behave differently in several important ways.

• DELETE removes rows one at a time and can include a WHERE clause to filter rows.
• TRUNCATE removes all rows instantly without scanning them individually.
• DELETE operations can be rolled back if used within a transaction; TRUNCATE usually cannot.
• TRUNCATE resets AUTO_INCREMENT counters; DELETE does not.
• DELETE fires DELETE triggers; TRUNCATE does not.

Practical Example of TRUNCATE TABLE

Here is a simple example demonstrating how to use TRUNCATE TABLE.