MySQL | TRUNCATE
Start your free 7-days trial now!
TRUNCATE statement drops a table and all its records and then recreates it as an empty table. It is similar to a
DELETE statement that deletes all rows of a table (i.e.
DELETE statement without a
TRUNCATE a table you cannot undo it so perform it with utmost caution!
Some points to note:
You cannot perform
TRUNCATEon InnoDB tables that are reference by foreign key constraints.
AUTO_INCREMENTvalues are reset once you
TRUNCATE [TABLE] table_name;
Consider the following table about whether students hold a drivers license:
The above sample table can be created using the code here.
To remove all records from the
TRUNCATE drivers_license;Query OK, 0 rows affected (0.02 sec)
Note that no matter how many rows are removed the returned response will say
"0 rows affected”.
To check the contents of the
drivers_license table now:
SELECT * FROM drivers_license;Empty set (0.00 sec)
We can see that all records have now been removed.