MySQL TRUNCATE TABLE: Empty a Table Fast and Reset IDs

TRUNCATE TABLE empties a table very quickly. The table itself, with its columns and indexes, stays.

query.sql

SQL

CREATE TABLE sessions (id INT AUTO_INCREMENT PRIMARY KEY, token VARCHAR(20));
INSERT INTO sessions (token) VALUES ('a'), ('b'), ('c');

TRUNCATE TABLE sessions;
SELECT COUNT(*) AS rows_left FROM sessions;

Output

OUTPUT

+-----------+
| rows_left |
+-----------+
|         0 |
+-----------+
1 row in set

It resets AUTO_INCREMENT

query.sql

SQL

1
2

INSERT INTO sessions (token) VALUES ('new');
SELECT id, token FROM sessions;

Output

OUTPUT

+----+-------+
| id | token |
+----+-------+
|  1 | new   |
+----+-------+
1 row in set

The next id is 1 again. With DELETE FROM sessions the counter would continue where it stopped:

query.sql

SQL

1
2
3

DELETE FROM sessions;
INSERT INTO sessions (token) VALUES ('after delete');
SELECT id, token FROM sessions;

Output

OUTPUT

+----+--------------+
| id | token        |
+----+--------------+
|  2 | after delete |
+----+--------------+
1 row in set

DELETE vs TRUNCATE

	`DELETE FROM t`	`TRUNCATE TABLE t`
Removes	Rows one by one (can use `WHERE`)	All rows at once
Type of statement	DML	DDL
Rollback	Yes, in a transaction	No
`AUTO_INCREMENT`	Continues	Reset to start
Speed on a big table	Slow	Very fast
Triggers	`DELETE` triggers run	Do not run
Foreign keys	Works if rows are not referenced	Refused if another table references this one

query.sql

SQL

CREATE TABLE teams (id INT PRIMARY KEY);
CREATE TABLE players (id INT PRIMARY KEY, team_id INT, FOREIGN KEY (team_id) REFERENCES teams (id));
INSERT INTO teams VALUES (1);
TRUNCATE TABLE teams;

Output

OUTPUT

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`learn`.`players`, CONSTRAINT `players_ibfk_1`)

Even when the child table is empty, MySQL refuses to truncate a parent table. Use DELETE FROM teams in that case.

When to use it

Resetting test data between test runs.
Clearing a staging table before a nightly import.
Emptying a huge log table you do not need.

When not to

When you need a rollback.
On a table referenced by foreign keys.
When you need the delete to be written to triggers or be replicated row by row.

dangerous

Check the table name twice

TRUNCATE TABLE orders; on the wrong database is the same as DROP: the data is gone, with no undo. Take a backup before you truncate anything you did not just create yourself.