How to Use TRUNCATE in MySQL: Syntax and Examples

In MySQL, use the TRUNCATE TABLE table_name; statement to quickly remove all rows from a table and reset its auto-increment counter. It is faster than DELETE because it does not log individual row deletions.

Syntax

The basic syntax of the TRUNCATE statement is:

• TRUNCATE TABLE table_name; - Removes all rows from the specified table.

This command deletes all data instantly and resets any auto-increment counters to the starting value (usually 1).

sql

TRUNCATE TABLE table_name;

Example

This example shows how to remove all rows from a table named employees using TRUNCATE. It deletes all data quickly and resets the auto-increment value.

sql

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO employees (name) VALUES ('Alice'), ('Bob'), ('Charlie');

SELECT * FROM employees;

TRUNCATE TABLE employees;

SELECT * FROM employees;

Output

+----+---------+
| id | name    |
+----+---------+
|  1 | Alice   |
|  2 | Bob     |
|  3 | Charlie |
+----+---------+

Empty set (0.00 sec)

Common Pitfalls

1. TRUNCATE cannot be used with a WHERE clause. It removes all rows without filtering.

2. TRUNCATE is DDL, not DML, so it cannot be rolled back in some storage engines. Use with caution in transactions.

3. Foreign key constraints may prevent truncation if related tables exist. You may need to disable foreign key checks temporarily.

sql

/* Wrong: Trying to delete specific rows with TRUNCATE (not allowed) */
-- TRUNCATE TABLE employees WHERE id = 1; -- Syntax error

/* Right: Use DELETE for conditional removal */
DELETE FROM employees WHERE id = 1;

Quick Reference

Feature	Description
Purpose	Remove all rows from a table quickly
Syntax	TRUNCATE TABLE table_name;
Auto-increment	Resets auto-increment counter to the starting value (usually 1)
Transaction	May not be rollback-able in some engines
WHERE clause	Not supported
Foreign keys	May block truncation if constraints exist

Key Takeaways

Use TRUNCATE TABLE to quickly delete all rows and reset auto-increment counters.

TRUNCATE cannot filter rows; use DELETE for conditional removal.

TRUNCATE is faster but may not be rollback-able in transactions.

Foreign key constraints can prevent truncation; disable checks if necessary.

Always backup data before truncating tables to avoid accidental loss.