SoFunction
Updated on 2025-05-04

A brief discussion on the difference between drop, truncate and delete in MySQL

1. Preface

Although it is simple for drop, truncate and delete, it still needs to be summarized if you really want to use it or ask during the interview. Today I will briefly talk about their direct differences. Before this, let’s briefly understand what DDL and DML are.
DDL (Data Definition Language): DDL stands for the data definition language and is a SQL command that helps create database schemas. Commonly used commands in DDL are:create，drop，alter，truncateandrenameetc.
DML (Data Manipulation Language): DML stands for data operation language and is a SQL command that helps retrieve and manage data in relational databases. Commonly used commands in DML are:insert，update，deleteandselectetc.

DROPThe command is used to delete objects such as the entire table (structure and data), or database, and its features are as follows:

  • Delete completely：DROPThe command not only deletes all data in the table, but also deletes the structure definition of the table. This process is irreversible unless there is a backup to restore.
  • Free up resources:implementDROPAfter the command, the database resources related to the table will be released.
  • Non-transactional operations：DROPOperations cannot usually be rolled back, and executionDROPAfter the command, the relevant object is deleted immediately.
  • Self-increasing ID: If you create a new table, the counting of the self-increment ID will start again.

Code example:

DROP TABLE employees;

Note: The DROP statement takes effect immediately after execution and cannot be retrieved.

TRUNCATEThe command is used to delete all rows in a table, and its characteristics are as follows:

  • Quickly clear the table：TRUNCATECompared to useDELETEDeleting all rows in a table is much faster because it does not delete data row by row, but deletes data and reinitializes the table by freeing the data pages that store it.

  • Non-transactional operations: Although some database management systems may allowTRUNCATEOperations roll back in transactions, but in many cases,TRUNCATEDetailed logs are not recorded, so they cannot be likeDELETEOperation ensures transaction security.

  • No trigger trigger: Usually, executeTRUNCATEOperations will not trigger table triggers.

  • Automatically reset the self-increase ID: For tables with auto-increment primary keys,TRUNCATEThe self-increase counter will be reset.

truncate will delete all records in the table and will reset the high waterline and all indexes.

It means that truncate will delete all rows in the table, but the table structure, its columns, constraints, indexes, etc. remain unchanged. The count value used for the new row identification is reset to the seed of the column), and by default, the space is released to the extent of the minextents (that is, the area within the segment in the table structure), unless reuse storage is used (using this sentence, the extent space where the data is located will not be recycled, but the data is deleted. The freespace space after the data is deleted can only be used by this table, and others cannot be used). Logs cannot be recorded, so the execution speed is very fast, but it cannot be canceled through rollback (if a table is accidentally truncates, it can also be restored, but it cannot be restored through rollback.

Example:

TRUNCATE TABLE employees;

Note: The TRUNCATE statement takes effect immediately after execution and cannot be retrieved.

DELETEThe command is used to delete one or more rows of records in a table, with the following characteristics:

Selective deletion: Can be passedWHEREThe clause specifies which rows to delete. If not specifiedWHEREclause, delete all rows in the table.

Transactional operations：DELETEOperations are transaction-safe, which means you can roll back in a transactionDELETEoperate. This is very useful when you accidentally delete the wrong data.

trigger: If there is a trigger on the table, executeDELETEActions trigger them.

performance:becauseDELETEThe operation deletes data line by line and records the log, so it may be slower when deleting a large amount of data.

  • The delete statement does not affect the extent occupied by the table (that is, the area in the table structure), and the high watermark remains unchanged in its original position. (The high water level line exists in the segment, which is used to identify the intersection between used data blocks in the segment and unused data blocks. When scanning table data, all data blocks below the high water level line must be scanned.)

  • In InnoDB, delete does not actually delete the data. MySQL actually just marks the deleted data as deleted. Therefore, when delete deletes the data in the table, the space occupied by the table file on the disk will not decrease, and the storage space will not be released, but the deleted data rows are set to invisible. Although disk space is not freed, the next time you insert data, you can still reuse this part of the space (reuse → overwrite).

  • When delete is executed, the deleted data will be cached into the rollback segment first, and the transaction commit will take effect;delete from table_nameDelete all data in the table, MyISAM will immediately release disk space, and InnoDB will not release disk space;
    fordelete from table_name where xxxDeletion with conditionality will not free up disk space either innoDB or MyISAM;
    Use the delete operation lateroptimize table table_nameDisk space will be released immediately. Whether it is InnoDB or MyISAM. Therefore, in order to achieve the purpose of freeing disk space, delete and execute it lateroptimize tableoperate.

Example:

DELETE FROM employees WHERE department = 'Sales';

5. Summary

In terms of speed, generally speaking,drop> truncate > delete。

  • If you want to delete some data, use delete, please include the where clause, and the rollback segment should be large enough; if you want to delete the table, of course use drop;
  • If you want to keep the table and delete all data, if it has nothing to do with the transaction (cannot roll back), just use truncate;
  • If it is related to a transaction, or if you want to trigger a trigger, use delete;
  • If you are sorting out fragments inside the table, you can use truncate to keep up with the reuse stroage and then re-import/insert the data.
  • truncate and drop are DDL statements, which cannot be rolled back after execution; delete is DML statements, which can be rolled back.
  • truncate can only act on tables; delete and drop can act on tables, views, etc.
  • truncate will clear all rows in the table, but the table structure, its constraints, indexes, etc. remain unchanged; drop will delete the table structure and its dependencies, indexes, etc.
  • truncate will reset the table's self-added value; delete will not.
  • truncate will not activate table-related delete triggers; delete can.
  • After truncate, the space occupied by the table and index will be restored to the initial size; the delete operation will not reduce the space occupied by the table or index, and the drop statement will free up all the space occupied by the table.

This is the end of this article about briefly discussing the difference between drop, truncate and delete in MySQL. For more related content on MySQL drop truncate delete, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!