Home > MySQL > Difference between MySQL delete and truncate table

Difference between MySQL delete and truncate table

October 23rd, 2007 admin Leave a comment Go to comments

Both remove records from the table, so what is the difference. Very simple, read along.

For this article, I will use a ‘friends’ table.

delete from friends;

and

truncate table friends;




Both the above statements remove all records from the table, but the essentiall difference is as follows.

delete from friends – will delete all records from the friends table. That’s it. I.e. the auto_increment counter does not get reset.

truncate table friends – will delete all records from the table and also rebuild the table, thus resetting the auto_increment counter.

Try these steps to identify yourself:

Step 1:

create table friends (
id int not null primary key auto_increment,
name varchar(20)
);

Step 2:
insert into friends (name) values (‘Sunil’);
insert into friends (name) values (‘Vishal’);
insert into friends (name) values (‘Vikram’);

Step 3:
select * from friends;

Note the id of the last record. It will be 3.

Step 4:
delete from friends;

Step 5:
insert into friends (name) values (‘Mithil’);




Step 6:
select * from friends;

Note that the id of the only record will be 4. This means that delete from friends removed all records, but did not reset the auto_increment counter.

Step 7:
Repeat Step 2 – 3 and goto step 8.

Step 8:
truncate table friends;

Step 9:
Repeat step 5 – 6 and goto step 10.

Note that after you repeat step 6, you will see that the only record i.e. of Mithil has id 1. This means that truncate deletes all the records from the table and resets the auto_increment pointer to start from 1.

Leave a comment if you need more examples or have more questions

Categories: MySQL Tags:

Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (output started at /home/sunilb/www.sunilb.com/wp-content/themes/inove/templates/header.php:29) in /home/sunilb/www.sunilb.com/wp-content/plugins/mycaptcha/MyCaptcha.php on line 41

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/sunilb/www.sunilb.com/wp-content/themes/inove/templates/header.php:29) in /home/sunilb/www.sunilb.com/wp-content/plugins/mycaptcha/MyCaptcha.php on line 41
  1. October 23rd, 2007 at 02:19 | #1

    DELETE and TRUNCATE differ in some more important and subtle ways than the auto increment counter.

    The most important difference is DELETE operations are transaction-safe and logged, which means DELETEs can be rolled back. TRUNCATE cannot be done inside a transaction and can’t be rolled back. Because TRUNCATE is not logged recovering a mistakenly TRUNCATEd table is a much bigger problem than recovering from a DELETE.

    DELETE will fail if foreign key constraints are broken; TRUNCATE may not honor foreign key constraints (it does for InnoDB tables). DELETE will fire any ON DELETE triggers; TRUNCATE will not.

    TRUNCATE is probably better thought of as a shortcut for DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.

    These differences are not specific to mySQL — PostgreSQL, MS SQL Server, and Oracle behave more or less the same way.

    Greg Jorgensen
    typicalprogrammer.com

  2. admin
    October 23rd, 2007 at 02:26 | #2

    Hi Greg,

    I was of the same opinion earlier… but I realized that this was not the case when I wanted to truncate a table as large as 2 GB. I had to stop the mysqld thread and start it again to kill the truncate table process… even the kill command would not work…

    Therefore, I had to then manually type in DROP TABLE and CREATE TABLE which worked much faster.

    I waited for about 10 minutes for it to truncate… but DROP followed by CREATE did the trick in 1 minute.

    Thanks for your reply.

  3. Ritu
    August 27th, 2008 at 12:44 | #3

    There are five difference in Delete and Truncate:

    1. Delete use for delete row by row but truncate will delete the entire table.
    2. In Truncate rollback not possible.
    3. Value of Auto Increment will reset from starting after use of Truncate not in Delete.
    4. Truncate is a DDL command and Delete is a Dml command.
    5. When Delete the Particular row the Corresponding Delete Trigger(if exists) Fire.
    In Case of Truncate the Trigger is not fired.

  4. admin
    November 15th, 2008 at 19:41 | #4

    Hi Ritu,

    Thank you for providing more information on this topic.

    Appreciate it!

    Suniil

  5. Santosh
    January 27th, 2010 at 17:11 | #5

    Thank u Ritu.

  1. No trackbacks yet.
Enter this code to leave comment (Sorry, but bots get me crazy :) )