At times we want to simply empty the contents of a table in a MySQL database instead of deleting the table entirely.
That is, we want to keep the layout and structure of the table, but just empty the data. MySQL has a handy command for emptying a table known as TRUNCATE.
The following MySQL tutorial will help to get you up to speed on using the truncate command.
The syntax for this command is straightforward:
TRUNCATE TABLE table_name;
So if the wanted to empty the contents of a table called "employees" which looks like this:
| id | first_name | last_name |
|---|---|---|
| 1 | Paul | Pitterson |
| 2 | Francine | Beecham |
| 3 | Raul | DiNozzo |
| 4 | Anthony | DiNozzo |
We would use the following SQL:
TRUNCATE TABLE employees;
This would empty the data in the table but preserve the table and its structure. So our table would look something like this:
| id | first_name | last_name |
|---|
That is, the fields, field types and other table characteristics will remain intact. Only the data will be destroyed. It is up to you as the database administrator to decide when to use a TRUNCATE command as opposed to a DROP TABLE command.
At all times, it is always a good idea to make a backup of your database before running queries which will alter it.
We hope this MySQL tutorial was useful.