MySQL | TRUNCATE
Start your free 7-days trial now!
MySQL's 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 WHERE clause).
Once you 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.Any
AUTO_INCREMENTvalues are reset once youTRUNCATEa table.
Syntax
TRUNCATE [TABLE] table_name;
Examples
Consider the following table about whether students hold a drivers license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
The above sample table can be created using the code here.
To remove all records from the drivers_license table:
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.
