Difference between MySql drop table, truncate table, delete from table commands

Apr 07, 2014, by admin

DROP command is used for deleting the table and its structure from the data base.Use this command when you don’t need that table any more.

Other Spec:

1. Removes a table from the database.Table structures, indexes, privileges, constraints will also be removed.
2. Cannot be rolled back.
3. No Triggers can be fired.

DELETE FROM tablename;

DELETE command is used for deleting the records from the table, and it removing the table space which is allocated by the database, and returns number of rows deleted.Delete allows you to use a WHERE clause so you can delete specific records according to your requirement.

Other Spec:

1. Can be rolled back, as during DELETE operation, all the data get copied into Rollback Table space first, and then delete operation perform.
2. Does not reset identity of the table.
3. Triggers can be fired.

TRUNCATE TABLE tablename;

TRUNCATE operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
After this, the table is empty, and auto-incrementing keys are reset to 1.It faster than DELETE because delete scan the table to generate a count of rows that were affected.

Other Spec:

1. Removes all rows from a table, but the table structures and its columns, constraints, indexes remains.
2. Cannot be rolled back.
3. Resets the identity of the table.
4. Cannot use TRUNCATE on a table referenced by a FOREIGN KEY constraint.
5. No Triggers can be fired.
6. Cannot use WHERE conditions.

Hope the post will be useful for more posts like the page Bugtreat Technologies