Difference between DELETE and TRUNCATE — MySql
1. For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE TABLE by deleting rows one by one. As of MySQL 5.0.3, row by row deletion is used only if there are any FOREIGN KEY constraints that reference the table. If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.
2. 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.
3. TRUNCATE is probably better thought of as a shortcut for DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.
4. Delete use for delete row by row but truncate will delete the entire table.
5. Truncate is a DDL command and Delete is a Dml command.
6. Value of Auto Increment will reset from starting after use of Truncate not in Delete.
7. When Delete the Particular row the Corresponding Delete Trigger(if exists) Fire. In Case of Truncate the Trigger is not fired.
8. As of MySQL 5.0.8, truncate operations cause an implicit commit. Before 5.0.8, truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction.
9. Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment