What is Difference between MySQL delete and truncate table

This very common question in interviews.  Both remove records from the table, so what is the difference.

  • Delete :
                         Syntax :  Delete from TableName [where condition];
    Deletes record  from table . But doesn’t reset the auto_increment
    DELETE operations are transaction-safe and logged, which means DELETE can be rolled back.
    DELETE will fire any ON DELETE triggers.DELETE will fail if foreign key constraints are broken.
  • Truncate :
                         Syntax :  truncate table  TableName;
    Deletes all record  from table. Reset the auto_increment.
    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. Truncate the Trigger is not fired.

    There are 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(Data Definition Language) command and Delete is a DML(Data Manipulation Language) command.
    5. When Delete the Particular row the Corresponding Delete Trigger(if exists) Fire.In Case of Truncate the      Trigger is not fired.

    But I found issue with both command, they will take time to execute when data is more than 2GB. So , I will prefer to Drop table & again create it.
    Before dropping table, export the structure of table using PHPMYADMIN or database administrative tools in sql file, so we can import the sql file, which will creates table structure.

Posted in Mysql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: