TRUNCATE TABLE
In this page:
TRUNCATE TABLE table_name;Clearing Table Data
TRUNCATE TABLE removes every row while leaving the table's column structure intact, and it's typically much faster than DELETE because it skips row-by-row logging and trigger execution entirely.
Example: Clearing Table Data
TRUNCATE TABLE users;Resetting Auto Increment Values
Unlike DELETE, which leaves the AUTO_INCREMENT counter wherever it stopped, TRUNCATE resets it back to its starting value — meaning the next inserted row gets ID 1 again, not a continuation of the old sequence.
Example: Resetting Auto Increment Values
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
TRUNCATE TABLE users; -- next inserted row gets id 1 againSafe Data Cleanups
Because TRUNCATE can't be selectively filtered with a WHERE clause, it always empties the entire table — double-check you're connected to the right database before running it, since the operation can't be undone.
Example: Safe Data Cleanups
-- TRUNCATE always empties the entire table; there is no WHERE clause
TRUNCATE TABLE users;Comparing Truncate Operations
TRUNCATE is technically a DDL (data definition language) statement, not DML — internally it drops and recreates the table structure rather than deleting rows one at a time, which explains its speed.
Example: Comparing Truncate Operations
-- TRUNCATE is DDL: it drops and recreates the table structure, unlike DELETE
TRUNCATE TABLE users;Post-Truncate Verification
After truncating, a quick SELECT COUNT(*) confirms the table is empty and ready for fresh data, giving you confidence the reset actually completed as expected.
Example: Post-Truncate Verification
TRUNCATE TABLE users;
SELECT COUNT(*) FROM users;- Expecting a
WHEREclause withTRUNCATE TABLE, which does not allow it and always removes every row. - Assuming
TRUNCATEcan be undone withROLLBACK, when it is DDL and commits immediately. - Running it on a table referenced by a foreign key from another table, which fails.
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: