MySQL Truncate Table

Last updated: October 19, 2026Oct 19, 2026 4 min read

TRUNCATE TABLE removes all rows from a table in one fast operation and leaves the empty table in place, ready for new data. It is perfect for clearing staging, log or test tables. It behaves quite differently from DELETE, though, and those differences matter.

1. Syntax

TRUNCATE [TABLE] table_name;

• The word TABLE is optional.
• Only one table per statement.
• Needs the DROP privilege on the table.
• It is a DDL statement and commits implicitly.

2. Emptying a Table

SELECT * FROM app_logs;

Before

TRUNCATE TABLE app_logs;

Query OK, 0 rows affected

SELECT COUNT(*) AS rows_left FROM app_logs;
DESCRIBE app_logs;

Result: 1 row

Result: 3 rows

The rows are gone but the structure (columns, keys, AUTO_INCREMENT) is intact.

3. AUTO_INCREMENT Is Reset

After TRUNCATE, numbering starts again at 1. After DELETE, it continues from where it was:

INSERT INTO app_logs (level, message) VALUES ('INFO', 'after truncate');
SELECT log_id, message FROM app_logs;

Result: 1 row

CREATE TABLE app_logs_d LIKE app_logs;
INSERT INTO app_logs_d (level, message) VALUES ('INFO','a'),('INFO','b'),('INFO','c');
DELETE FROM app_logs_d;
INSERT INTO app_logs_d (level, message) VALUES ('INFO', 'after delete');
SELECT log_id, message FROM app_logs_d;

Result: 1 row

4. TRUNCATE Can't Be Rolled Back

INSERT INTO app_logs (level, message) VALUES ('INFO','x'),('INFO','y');
START TRANSACTION;
TRUNCATE TABLE app_logs;      -- implicit COMMIT happens here
ROLLBACK;
SELECT COUNT(*) AS rows_after_rollback FROM app_logs;

Result: 1 row

The ROLLBACK has nothing to undo. With DELETE inside a transaction, the rows would come back.

5. TRUNCATE and Foreign Keys

InnoDB refuses to truncate a table that is referenced by another table's foreign key, even if the child table is empty:

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(30));
CREATE TABLE invoices (id INT PRIMARY KEY, customer_id INT,
                       FOREIGN KEY (customer_id) REFERENCES customers(id));
INSERT INTO customers VALUES (1, 'Rhea');
TRUNCATE TABLE customers;

Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 1 row affected
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`appdb`.`invoices`, CONSTRAINT `invoices_ibfk_1`)

Options: truncate in a maintenance window with checks disabled, or use DELETE, which respects the foreign keys row by row:

SET foreign_key_checks = 0;
TRUNCATE TABLE customers;
SET foreign_key_checks = 1;
SELECT COUNT(*) AS customers_left FROM customers;

Result: 1 row

6. Triggers Don't Fire

A DELETE trigger runs for every deleted row; TRUNCATE skips it:

CREATE TABLE deleted_log (msg VARCHAR(50));
CREATE TABLE notes (id INT PRIMARY KEY, txt VARCHAR(30));
CREATE TRIGGER notes_ad AFTER DELETE ON notes
  FOR EACH ROW INSERT INTO deleted_log VALUES (CONCAT('deleted note ', OLD.id));

INSERT INTO notes VALUES (1,'a'),(2,'b');
DELETE FROM notes WHERE id = 1;
TRUNCATE TABLE notes;
SELECT * FROM deleted_log;

Result: 1 row

Only the DELETE was logged. If your audit relies on triggers, don't use TRUNCATE.

7. TRUNCATE vs DELETE vs DROP

8. When to Use TRUNCATE

• Clearing staging tables before a nightly import.
• Resetting test data between test runs.
• Emptying log or cache tables that nothing references.
• Not for removing some rows, for tables with audit triggers, or when you might need to roll back.

9. Summary

• TRUNCATE TABLE t; removes all rows quickly and keeps the structure.
• It resets AUTO_INCREMENT, skips triggers and can't be rolled back.
• InnoDB won't truncate a table referenced by a foreign key.
• Use DELETE when you need a WHERE clause, triggers or a rollback; use DROP to remove the table entirely.