MySQL TRUNCATE TABLE

The TRUNCATE TABLE statement removes all rows from a table quickly and efficiently. Unlike DELETE, it does not process each row individually — it clears the entire table in one operation, making it much faster on large tables. The table structure, columns, indexes, and constraints are all preserved.

Syntax

TRUNCATE TABLE table_name;

Or simply:

TRUNCATE table_name;

Example

Clear all data from the session_logs table:

TRUNCATE TABLE session_logs;

After this runs, the session_logs table is empty, but it still exists and can accept new data.

How TRUNCATE Differs from DELETE

Feature	TRUNCATE	DELETE (no WHERE)
Removes all rows	Yes	Yes
Can target specific rows	No	Yes (with WHERE)
Speed on large tables	Very fast	Slower
Resets AUTO_INCREMENT counter	Yes	No
Can be rolled back	No (DDL operation)	Yes (inside a transaction)
Fires DELETE triggers	No	Yes

AUTO_INCREMENT Reset

One important behavior of TRUNCATE: it resets the AUTO_INCREMENT counter back to 1. This means the next inserted row gets an ID starting from 1 again.

With DELETE, the counter continues from where it left off.

-- Current table has rows with IDs 1–100
-- After TRUNCATE: next inserted row gets ID 1

TRUNCATE TABLE orders;

INSERT INTO orders (customer, amount) VALUES ('Alice', 99.00);
-- This new row gets order_id = 1

TRUNCATE and Foreign Keys

If a table is referenced by a foreign key in another table, TRUNCATE will fail. In that case, either use DELETE instead, or temporarily disable foreign key checks:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE orders;
SET FOREIGN_KEY_CHECKS = 1;

Re-enabling foreign key checks afterward is essential.

How TRUNCATE Differs from DROP

Feature	TRUNCATE	DROP TABLE
Removes data	Yes	Yes
Removes table structure	No	Yes
Table still usable after	Yes	No

When to Use TRUNCATE

Clearing test or staging data before a fresh run.
Resetting log tables or session tables that fill up over time.
When all rows need to be removed and performance matters.

Key Points

TRUNCATE TABLE removes all rows from a table instantly.
The table structure, columns, and indexes are preserved.
It resets the AUTO_INCREMENT counter to 1.
It cannot be rolled back — unlike DELETE inside a transaction.
It does not work on tables referenced by active foreign key constraints.

Previous lessons

Back to courses

Next lessons