1111 Votes

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

Tutorial by Stefan Trost | Last update on 2024-04-14 | Created on 2011-11-10

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

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 > 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:


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:


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 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.


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.


About the Author

AvatarYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile


Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.


Ask your own question or write your own article on askingbox.com. That’s how it’s done.