MySQL

Difference between truncate and delete in mysql

31 Aug , 2009  

Well this is very common but much confused discussion. This question is often asked from freshers in interviews as well. The difference is pretty simple but yet people confuse it because they see the result to be the same of both the queries.

Truncate and Delete are both SQL commands which result in removing the table records. So lets list the differences one by one :

Type of Command – Truncate is a DDL command and Delete is a Dml command

RollBack - As mentioned above Truncate is DDL command, so the changes made by it are committed automatically hence there is nothing called rollback when you use truncate, while Delete commands can be rolled back

Table Structure – When you use Truncate command, all the rows in the table are delete and the structure of the table is recreated and so does the indexes. On the contrary if you use Delete command only the desired rows or all the rows are deleted and the structure remains unchanged.

Syntax - The syntax for both the commands is :

 Truncate table <tablename> #command to truncate a table.
 Delete from <tablename> #command to delete all the records from table.

Practical example -

#creates a table with 2 columns, 1st column is auto incremented
Create table mysqlDemo (id integer not null auto_increment,name varchar(100),PRIMARY KEY(id)); 
 
#now insert two records in the table 
insert into mysqlDemo(name)values ('sachin');
insert into mysqlDemo(name)values ('digimantra');
 
#check the records and note their auto_increment values
select * from mysqlDemo;
 
#Let us try delete it using Delete command
delete from  mysqlDemo;
 
#Now the table is empty, lets insert values from the first row.
insert into mysqlDemo(name)values ('new_sachin');
insert into mysqlDemo(name)values ('new_digimantra');
 
#check the records and note their auto_increment values
select * from mysqlDemo; #the aut_increment values will continue from the last records, as the table structure is preserved. 
 
#Now let us Truncate the table and re-insert the values.
Truncate table mysqlDemo;
insert into mysqlDemo(name)values ('sachin');
insert into mysqlDemo(name)values ('digimantra');
 
#check the records and note their auto_increment values
 select * from mysqlDemo;
 
#this time the auto_increment value will start from one, as the table structure is recreated because we used Truncate instead of Delete.

So this is what the difference is, in short always remember Truncate command recreates the structure of the table and deletes all the records of the table. However Delete command does not recreates the structure and deletes the complete or partial records (as desired) from the table.

You can find more of the PHP interview questions in my old post PHP interview for Beginners.

Stay Digified !!
Sachin Khosla

, ,


4 Responses to “Difference between truncate and delete in mysql”

  1. abhinav says:

    woah! i got this question in my DBMS exam just 3 days back!!!

    what do we mean by “structure is recreated”? I mean, what is the difference in recreating the structure and leaving it as-it-is? this is the only point that i missed out on in the paper. :P

  2. Well try to execute the queries in the sequence above. Structure is recreated means all the indexes are dropped, the auto_increment counter is reset to 0 as well. You will be able to see this when you execute the queries above(in sequence) read the comments alongside.

  3. One effect of re-creating the table structure is that your auto_increment fields will “start over” at 1.

  4. Exactly and that is what the above example explains :)

More in MySQL (10 of 12 articles)


Hey Guys, Let me first explain you the scenario in which more likely you can use this type of query. ...