33 Votes

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

Tutorial by Stefan Trost | Last update on 2021-04-30 | 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: Deleting individual records

The best known command, is DELETE. With DELETE, it is possible to delete individual records from a table. Here we have an example:

DELETE FROM tab WHERE id=2;
DELETE FROM tab WHERE id<7;
DELETE FROM tab WHERE name='Anton';
DELETE FROM tab WHERE name LIKE '%abc%';

These are four MySQL statements that respectively delete records from the table "tab" according to certain criteria. The first command deletes the record with the id 2, the second command all records that have an id less than 7, and the third command deletes the record or the records in which the column "name" is equal to "Anton" respectively contains only "Anton". The last command, however, deletes all records in which the column "name" contains the string "abc".

In all of these examples, 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.

Difference between TRUNCATE and DELETE

Also with the command TRUNCATE, all records from a table can be 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 is identical to "DELETE FROM tab". But the technical implementation is different. During 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 definition and then puts on a new identical blank table with no data.

As a result, TRUNCATE is a lot faster than the DELETE statement. Furthermore, in the nature of things lies 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.

Delete complete table with DROP

The last command we will look at is called DROP:

DROP TABLE tabe;
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 described in the example. The first line, the entire table "tab" is deleted, in the second line you can see how to delete multiple tables simultaneously. 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.

ReplyPositiveNegative

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.

Participate

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