MySQL TRUNCATE TABLE: Foreign Keys, Rollback and DELETE

TRUNCATE TABLE deletes every row in a table and keeps the table, its columns, indexes and grants. MySQL does this by dropping and recreating the table, so it takes a fraction of a second whatever the size, resets AUTO_INCREMENT and cannot be undone.

TRUNCATE TABLE order_items;

The examples use the 1bench demo database, saas_app, where orders, subscriptions and audit_log_v2 reference users, and order_items references orders and products. Every statement was run on MySQL 8.4 LTS and 9.7, and they behave the same on both.

TRUNCATE TABLE syntax

The full syntax is one table name, with the TABLE keyword optional. There is no WHERE, no IF EXISTS, no CASCADE and no way to list several tables.

TRUNCATE TABLE order_items;
TRUNCATE order_items;
TRUNCATE TABLE saas_app.order_items;

MySQL answers Query OK, 0 rows affected however many rows were removed, because it never counts them. A view is not a table here: TRUNCATE TABLE on a view fails with ERROR 1146 (42S02): Table 'saas_app.v_orders' doesn't exist. To remove only some rows, use DELETE ... WHERE.

Truncate a table with foreign keys

MySQL refuses to truncate a table that another table references, even when the referencing table is empty. Trying it on users gives:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`saas_app`.`orders`, CONSTRAINT `fk_orders_user`)

The message names only the first constraint it finds. ON DELETE CASCADE on the child does not help, since TRUNCATE ignores it, and TRUNCATE TABLE users CASCADE is a syntax error (ERROR 1064). List every table that points at the one you want to clear:

SELECT TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'saas_app'
  AND REFERENCED_TABLE_NAME = 'users';

Then switch off foreign key checks for your session, truncate the children and the parent, and switch them back on. FOREIGN_KEY_CHECKS is a session setting, so all of it has to run on one connection.

Example: empty users and everything that depends on it.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE order_items;
TRUNCATE TABLE orders;
TRUNCATE TABLE subscriptions;
TRUNCATE TABLE audit_log_v2;
TRUNCATE TABLE users;
SET FOREIGN_KEY_CHECKS = 1;

Truncate the children too, not just the parent. With checks off, MySQL lets you truncate users alone and leaves orders full of rows pointing at user ids that no longer exist. Since TRUNCATE also resets AUTO_INCREMENT, the next user you insert gets id 1 and quietly inherits the old orders of user 1. Turning the checks back on does not re-check existing rows.

If you would rather keep foreign keys on, delete in dependency order instead (children first). It is slower, but every row is checked.

Reset AUTO_INCREMENT

TRUNCATE always resets the counter to 1, even when the table was created with a higher start such as AUTO_INCREMENT = 1000. DELETE keeps counting from where it was.

Example: compare the next id after each.

TRUNCATE TABLE order_items;
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 1, 1, 9.99);
SELECT id FROM order_items;   -- 1

DELETE FROM order_items;
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 1, 1, 9.99);
SELECT id FROM order_items;   -- 2

To reset the counter after a DELETE, or to start somewhere other than 1 after a TRUNCATE, set it yourself. On an empty table InnoDB takes any value; on a table with rows it uses the next id above the current maximum.

ALTER TABLE order_items AUTO_INCREMENT = 1;

TRUNCATE vs DELETE vs DROP

All three get rid of rows, but they differ in what they keep and what they cost. The timings are from a 500,000-row InnoDB table on the same machine.

The binary log row matters on replicas: a DELETE of every row ships every row, while TRUNCATE ships one short statement even with binlog_format = ROW. Use DELETE when you need a WHERE clause, triggers or a rollback, TRUNCATE to empty a table you are keeping, and DROP when the table itself should go.

TRUNCATE inside a transaction

TRUNCATE causes an implicit commit. Inside a transaction, it first commits whatever you had already done, then truncates, and a later ROLLBACK has nothing left to undo.

Example: the update survives the rollback, and so does the truncate.

START TRANSACTION;
UPDATE users SET status = 'inactive' WHERE id = 1;
TRUNCATE TABLE order_items;
ROLLBACK;

SELECT COUNT(*) FROM order_items;           -- 0
SELECT status FROM users WHERE id = 1;      -- inactive

If you need to empty a table and keep the option to back out, use DELETE FROM order_items; inside the transaction instead.

Truncate all tables in a database

MySQL has no single statement for this. Generate one TRUNCATE per base table from information_schema, skipping views, and run the list with foreign key checks off.

SELECT CONCAT('TRUNCATE TABLE `', TABLE_NAME, '`;') AS stmt
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'saas_app'
  AND TABLE_TYPE = 'BASE TABLE';

Example: do it in one line from the shell, which pipes the generated statements straight back into mysql.

mysql -u root -p -N -e "SELECT CONCAT('TRUNCATE TABLE \`', TABLE_NAME, '\`;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'saas_app' AND TABLE_TYPE = 'BASE TABLE'" \
  | (echo 'SET FOREIGN_KEY_CHECKS = 0;'; cat) \
  | mysql -u root -p saas_app

The order of the tables does not matter while the checks are off, and they are back on when that mysql session ends. To also remove the tables themselves, DROP DATABASE saas_app; CREATE DATABASE saas_app; is shorter.

Truncate a partition

On a partitioned table, ALTER TABLE ... TRUNCATE PARTITION empties one or more partitions and leaves the rest. It is the fast way to purge old data from a table split by date.

Example: a log table partitioned by year.

CREATE TABLE events (
  id INT NOT NULL,
  created_at DATE NOT NULL,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

ALTER TABLE events TRUNCATE PARTITION p2024;
ALTER TABLE events TRUNCATE PARTITION p2025, pmax;
ALTER TABLE events TRUNCATE PARTITION ALL;

A wrong name fails with ERROR 1735 (HY000): Unknown partition 'p1999' in table 'events'. Do not check the result in information_schema.PARTITIONS: its TABLE_ROWS column is an estimate from table statistics and can show 0 for partitions that still hold rows. Use SELECT ... FROM events PARTITION (p2025) instead.

TRUNCATE privileges

There is no TRUNCATE privilege in MySQL. TRUNCATE needs DROP on the table, and the DELETE privilege alone gets ERROR 1142 (42000): DROP command denied to user 'app'@'localhost' for table 'order_items'.

GRANT DROP ON saas_app.order_items TO 'app'@'%';

Grant it on single tables rather than the whole database where you can. The same privilege lets the user run DROP TABLE order_items, and in testing that is exactly what it allowed.

When TRUNCATE hangs

TRUNCATE needs an exclusive metadata lock on the table, so it waits for any open transaction that has read or written that table, even an idle one. While it waits, SHOW PROCESSLIST shows the state Waiting for table metadata lock, and new queries on the table queue up behind it. The sys schema names the session in the way and writes the KILL statement for you:

SELECT object_name, waiting_pid, waiting_query, blocking_pid, sql_kill_blocking_connection
FROM sys.schema_table_lock_waits;

Commit in the blocking session, or run the KILL 85 it suggests, and TRUNCATE finishes at once. Killing the session rolls back its open transaction. To fail fast instead of waiting a year (the default lock_wait_timeout is 31536000 seconds), set a short timeout first. The wait then ends with ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

SET SESSION lock_wait_timeout = 10;
TRUNCATE TABLE order_items;