What is the difference between truncate and delete in MySQL?

In MySQL, both truncate and delete are used to remove data from a table, but there are some differences between them.

1. Speed: Truncate is faster than delete because it removes data by releasing the data pages of the table instead of deleting row by row, allowing for faster deletion of large amounts of data.
2. Rollback: Truncate cannot be rolled back, once executed, the data cannot be recovered. However, delete can restore deleted data during a transaction rollback.
3. Preserve table structure: Truncate only removes data from the table, without deleting the table’s structure such as columns, primary keys, and indexes. Delete, on the other hand, only removes data from the table while keeping the table’s structure intact.
4. Auto-increment primary key: When data is deleted using truncate, the auto-increment primary key will be reset to the initial value. However, when data is deleted using delete, the auto-increment primary key will not be reset.
5. Permission: Truncate requires DROP permission for the table, while delete only requires DELETE permission for the table.

Summary: Truncate is suitable for quickly deleting all data in a table and resetting the auto-increment primary key, but it cannot be rolled back or preserve the table structure. Delete is suitable for deleting data row by row, can be rolled back, and preserves the table structure.