MySQL: Delete Data from Table - Difference between TRUNCATE, DELETE and DROP

In this little tutorial, we want to have a closer look at the difference between the MySQL commands TRUNCATE, DELETE and DROP. All three commands have to do with the deletion of data from tables or the deletion of entire tables.

The tutorial is divided into the following sections:

• DELETE: Deletion of individual records
• Deletion of all records from a table
• Difference between TRUNCATE and DELETE
• Deletion of a complete table with DROP

DELETE: Deletion of individual records

The best known of the three commands is DELETE. With DELETE, it is possible to delete individual records from a table. Here is an example of how to use this command:

DELETE FROM tab WHERE id = 2;
DELETE FROM tab WHERE id > 7 AND id < 30;
DELETE FROM tab WHERE name = 'Anna';
DELETE FROM tab WHERE name LIKE '%abc%';

These are four different SQL statements that each delete other records from the table named "tab" according to certain criteria. The first command deletes the record with the ID 2, the second command all records that have an ID greater than 7 and less than 30 (IDs 8 to 29), and the third command deletes the record or the records in which the column "name" is equal to "Anna" respectively only contains "Anna". The last command, in turn, deletes all records in which the column "name" contains the string "abc".

As you can see, at this point, we can define any criteria for deleting data records, just the same way as we can define the selection in the WHERE part of a SELECT query.

Deletion of all records from a table

In the examples for the DELETE command we looked at in the last section, we have described in detail behind WHERE which records should be deleted. But we can also omit the WHERE statement:

DELETE FROM tab;

This statement has no limitations according to which criteria records should be deleted. Therefore, all records from the table will be lost irrepealable. The entire table is empty after this query.

Also with the command TRUNCATE, all records from a table are deleted. You can use this command in the following way:

TRUNCATE TABLE tab;

This command also deletes all data permanently from the table "tab" and therefore, the effect of this instruction on the data records is identical to "DELETE FROM tab". However, the technical implementation is different:

Difference between TRUNCATE and DELETE

During the deletion with DELETE, each record from the table will be deleted individually, while TRUNCATE has a different approach: TRUNCATE first deletes the entire table including the column definitions and then creates a new identical blank table with no data.

As a result, TRUNCATE is a lot faster than the DELETE statement. However, a side effect of this different approach is how the two delete commands are dealing with AUTO_INCREMENT columns. TRUNCATE resets the AUTO_INCREMENT value to 1, because the whole table is rebuilt, while DELETE makes no reset and the column will be go on counting where it was stopped. Therefore, if you would like to keep your AUTO_INCREMENT value, you should rely - despite the rather poor performance - on DELETE or you have to save the value before the deletion process in order to set it manually afterwards.

Deletion of a complete table with DROP

The last command we will look at in this tutorial is called DROP:

DROP TABLE tab;
DROP TABLE tab1, tab2;
DROP TABLE IF EXISTS tab1, tab2;

DROP deletes the entire table, including all data and the definitions of the table columns. So, after you have carried out a DROP command, the whole table will be gone and can not be recovered. In contrast, DELETE and TRUNCATE are only deleting the data from the table, while the table definitions are remaining.

You can use DROP as shown in the example. With the first line, the entire table "tab" is deleted, with the second line you can see how to delete multiple tables simultaneously within only one statement. In the third line, "IF EXISTS" is added. This enhancement ensures that no error message comes up when trying to delete a table that does not exist.

Warning

You should deal with the commands DELETE, TRUNCATE and DROP very carefully, because the records or the whole table can not be restored after deleting. Please be sure whether you really no longer need the data and always make as a precaution a backup of your database before using.

Otherwise the only thing you can do is to create a new column again.