Introduction
DELETE and TRUNCATE commands are used to remove data from a table, but there are differences between them. A web developer must understand the difference between DELETE and TRUNCATE in MySQL for efficient data management. While the DELETE command removes rows based on a condition, the TRUNCATE TABLE command removes all rows instantly and resets the auto-increment. This tutorial explains DELETE vs TRUNCATE in MySQL with examples to help you choose the right command for your database operations.
What does DELETE do?
DELETE is a DML (Data Manipulation Language) statement. It can delete a single row, more than one row, or all rows from a table. You can use WHERE condition with DELETE statement to choose which row or rows to be deleted. Examples of DELETE statements:
To delete all rows from the table Employees, use:
DELETE FROM EMPLOYEES;
To delete a row from the Employees table for the employee with ID 200, use:
DELETE FROM EMPLOYEES WHERE EMP_ID = 200;
The database keeps a log of the changes in the database in the transaction log for the DELETE statement. So, when you delete data using the DELETE statement, you can restore the deleted data.
What does TRUNCATE do?
TRUNCATE is a DDL (Data Definition Language) statement. TRUNCATE also deletes rows from a table. However, you cannot use a filter to delete a specific row or some of the rows. TRUNCATE deletes all data from the table. So, you cannot use the WHERE condition in the TRUNCATE statement. Example:
TRUNCATE TABLE EMPLOYEES;
In this case, all the rows from the Employees table will be deleted. Also, if data is deleted using the TRUNCATE statement, you cannot restore it back. Because the database does not write the transaction logs for the deleted data using TRUNCATE. So, we should use the TRUNCATE statement carefully.
Differences between DELETE and TRUNCATE
We can summarize the differences between the DELETE and TRUNCATE commands in the below table.
| Feature | DELETE Command | TRUNCATE Command |
|---|---|---|
| Definition | Used to delete specific rows from a table based on a condition. | Used to remove all rows from a table instantly. |
| Command Type | DELETE is a DML command. | TRUNCATE is a DDL command. |
| Syntax | DELETE FROM table_name WHERE condition; | TRUNCATE TABLE table_name; |
| Condition Support | Can include a WHERE clause to delete selected records. | Does not support a WHERE clause — removes all rows. |
| Speed | DELETE command deletes row-by-row data and it is slower. | TRUNCATE command deletes data by deallocating data pages in the database and it is faster than DELETE. |
| Transaction Log | Each deleted row is logged individually | Minimal logging — only the table deallocation is logged. |
| Rollback Support | Can be rolled back if used within a transaction. | Cannot be rolled back once executed (in most database engines). |
| Trigger Execution | DELETE statement can activate a trigger because it works row-by-row basis. | TRUNCATE cannot raise a trigger as it does not delete row-by-row data. |
| Auto-Increment Counter | DELETE retains the auto-increment values in the table. If you have 100 rows in a table and you deleted all rows using DELETE statement, then for the next insert, auto-increment will start from 101. | TRUNCATE resets the auto-increment as it drops and recreates the table. In this case, after using TRUNCATE, auto-increment will start from 1. |
| Usage Recommendation | Use when you need to remove specific data. | Use when you want to clear all data quickly. |
Conclusion
In this tutorial, we learned the difference between DELETE and TRUNCATE in MySQL. The DELETE command is best for selectively removing rows, while TRUNCATE is ideal for quickly removing all data from a table. Both commands serve unique purposes depending on your project's requirements. By knowing when to use DELETE vs TRUNCATE in MySQL, you can improve performance, maintain clean data and avoid unintended data loss.
Post a Comment