Gregory Broccoli - difference between TRUNCATE and DELETE

March 3, 2018 141 views

Hi, I am Gregory Broccoli
I want to know, What is the difference between TRUNCATE and DELETE in MySQL.
Thanks, Gregory Broccoli

1 Answer
uidezignlab March 3, 2018
Accepted Answer


  1. DELETE is a DML Command.
  2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
  3. We can specify filters in where clause
  4. It deletes specified data if where condition exists.
  5. Delete activates a trigger because the operation are logged individually.
  6. Slower than truncate because, it keeps logs.
  7. Rollback is possible.


  1. TRUNCATE is a DDL command.
  2. TRUNCATE TABLE always locks the table and page but not each row.
  3. Cannot use Where Condition.
  4. It Removes all the data.
  5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
  6. Faster in performance wise, because it doesn't keep any logs.
  7. Rollback is possible.

The most important difference is DELETE operations are transaction-safe and logged, which means DELETE 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.

DELETE will fail if foreign key constraints are broken; TRUNCATE may not honor foreign key constraints (it does for InnoDB tables). DELETE will fire any ON DELETE triggers; TRUNCATE will not.

Have another answer? Share your knowledge.