TRUNCATE vs DELETE In MySQL

Most of the time you might be wondering the difference between TRUNCATE vs DELETE in MYSQL, where to use, and when to use it. In this article, I will cover as much as possible.

---

DELETE

Syntax

DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ] FROM table
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];

1. DELETE is a DML command.
2. After DELETE command, if table has PrimaryKey with AutoIncrement then AutoIncrement counter wont be reset and starts from next id.
3. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
4. We can specify filters in WHERE clause
5. It deletes specified data if where condition exists else it will delete all the data.
6. Delete activates a trigger because the operation are logged individually.
7. Transaction safe. Hence all the deleted data can be rolled back.
8. Slower than truncate because, it keeps logs.
9. Rollback is possible.

---

TRUNCATE

Syntax

TRUNCATE TABLE [database_name.]table_name;

1. TRUNCATE is a DDL command.
2. After TRUNCATE command, if there is PrimaryKey with AutoIncrement then AutoIncrement counter will be reset to 1.
3. TRUNCATE TABLE always locks the table and page but not each row.
4. Cannot use WHERE Condition.
5. It deletes all the data from the table.
6. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
7. Faster in performance wise, because it doesn't keep any logs.
8. Not Transaction safe. Hence we cannot rollback data.
9. Rollback is not possible only.

---

Common

1. DELETE and TRUNCATE both can be rolled back when used with TRANSACTION (TRUNCATE can be rolled back in SQL Server, but not in MySQL).
2. DELETE and TRUNCATE both keep table structure intact and only deletes data.

---

Articles For You

Move Uploaded Files From Local Computer Or Server To Amazon S3 Bucket In PHP

Install RabbitMQ in Ubuntu 22

Basic Server Security Setup For Ubuntu / Linux

Install Apache Web Server On Ubuntu 20.04 / Linux & Manage It

SummerNote WYSIWYG Text Editor

Testing Laravel Emails With MailHog

Prevent Multiple Form Submits

Responsive YouTube Videos

Sass or SCSS @mixin vs @extends vs Placeholder (%)

Generate SSH Key with ssh-keygen In Linux / Unix

Create A Composer Package? Test It Locally And Add To Packagist Repository

Google, Twitter, GitHub, Facebook & Many Other Social Generic Logins With PHP Laravel Socialite

Setup Docker for NodeJs, MongoDB, MongoDB Compass

Free SSL Certificate With Lets Encrypt/Certbot In Linux (Single / Multiple Domains)

Install Linux, Apache, MYSQL, PHP (LAMP Stack) on Ubuntu