DROP and TRUNCATE
Three ways to remove data — DELETE, TRUNCATE and DROP — and what each one does to the table and its AUTO_INCREMENT counter.
TRUNCATE [TABLE] tbl_name | DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...Three statements remove data, at three different levels.
| DELETE | TRUNCATE TABLE | DROP TABLE | |
|---|---|---|---|
| Removes | matching rows | all rows | rows and the table |
| Kind | DML | DDL | DDL |
| WHERE clause | yes | no | no |
| Rollback | yes | no | no |
| Fires triggers | yes | no | drops them |
| AUTO_INCREMENT | unchanged | reset to start | gone |
| Rows affected | accurate | always 0 | n/a |
TRUNCATE is not a fast DELETE
It drops the table and recreates it empty, which is why it is much faster than deleting row by row, why no ON DELETE trigger runs, and why it needs the DROP privilege rather than DELETE. It also "does not return a meaningful value for the number of deleted rows. The usual result is '0 rows affected,' which should be interpreted as 'no information.'" And it resets the AUTO_INCREMENT counter to its start value even on InnoDB, which otherwise never reuses sequence values.
Shorthand and longhand
TRUNCATE t and TRUNCATE TABLE t are the same statement. On DROP TABLE, the trailing RESTRICT and CASCADE keywords do nothing at all — they exist only to ease porting.
What trips people up
TRUNCATE TABLE fails on an InnoDB table if any other table has a FOREIGN KEY referencing it.
Example
-- DELETE: selective, transactional, counts rows, leaves the counter alone
START TRANSACTION;
DELETE FROM sessions WHERE last_seen_at < NOW() - INTERVAL 30 DAY;
SELECT ROW_COUNT(); -- how many rows went
COMMIT; -- or ROLLBACK, and nothing was lost
-- TRUNCATE: empties the table and resets AUTO_INCREMENT to 1.
-- Implicit commit: there is no undo.
TRUNCATE TABLE import_staging;
-- DROP: the table itself, its data, its indexes and its triggers
DROP TABLE IF EXISTS import_staging_old;
-- Emptying a table without resetting ids: DELETE, not TRUNCATE
DELETE FROM sessions; -- next insert continues from the old high-water markWhen to use it
- Empty a nightly import staging table before reloading it.
- Clear test fixtures between runs and have ids start at 1 again.
- Remove a table left behind by a migration that has since been reverted.
- Purge old rows in batches without losing the ability to roll back.
More examples
Truncating a table that a foreign key points at
The constraint is checked against the schema, not the data: an empty child table still blocks the truncate. Emptying in dependency order — children first — is the routine that works, and it is the one to build into fixture teardown scripts.
TRUNCATE TABLE customers;
-- ERROR 1701 (42000): Cannot truncate a table referenced in a
-- foreign key constraint (`shop`.`orders`, CONSTRAINT `fk_orders_customer` ...)
-- Option 1: delete instead. Slower, but respects the constraints,
-- fires triggers, and can be rolled back.
DELETE FROM orders;
DELETE FROM customers;
-- Option 2: drop the child data first, in dependency order
TRUNCATE TABLE orders; -- nothing references orders
TRUNCATE TABLE customers; -- now permittedResetting the counter without emptying the table
Use this when tests assert on literal ids but the table has referential dependencies that rule out TRUNCATE. The restriction is what keeps it safe: on a table with rows, the counter can only be raised, so you cannot hand out an id that already exists.
-- DELETE leaves the AUTO_INCREMENT high-water mark where it was
DELETE FROM invoices;
INSERT INTO invoices (number) VALUES ('INV-A'); -- id continues at, say, 5001
-- Set the counter explicitly. InnoDB will not accept a value at or
-- below the largest id currently in use.
ALTER TABLE invoices AUTO_INCREMENT = 1;
-- Inspect the current counter for every table in this schema
SELECT table_name, auto_increment
FROM information_schema.tables
WHERE table_schema = DATABASE() AND auto_increment IS NOT NULL;Idempotent teardown in a migration
The down-migration idiom. DROP TABLE removes the table's triggers along with it, so a teardown that drops the table does not need to drop its triggers separately — but the leftover grants are a genuine trap when a table name is recycled for a different purpose.
-- IF EXISTS turns "table already gone" from an error into a NOTE
DROP TABLE IF EXISTS orders_2023, orders_2024;
SHOW WARNINGS; -- Note 1051: Unknown table 'shop.orders_2023'
-- TEMPORARY restricts the drop to session-local tables, so a typo
-- cannot take out the permanent table of the same name
DROP TEMPORARY TABLE IF EXISTS tmp_report_rows;
-- Note: dropping a table does not drop privileges granted on its name.
-- Recreating it later restores access for anyone who had it.
Discussion