SoFunction
Updated on 2025-04-13

The difference between MySQL delete table three operations and delete, truncate, and drop statements

This article mainly talks about the three operations of deleting tables in mysql, the differences between delete statements, truncate statements and drop statements.

1. Relationship and difference

1. Difference

delete, truncate, and drop are SQL statements used to delete data or objects in a database, but they have different functions and usages.

  • delete statement: The delete statement is used to delete a specific row or row that meets a specific condition from a table. The delete statement is a conditional delete operation. It can specify a specific condition through the WHERE clause to select the row to delete. The delete statement can only delete rows of data, but will not delete the table itself or other objects. Deletion operations can usually be rolled back, that is, they can withdraw the deletion operations and restore deleted data.
  • truncate statement: The truncate statement is used to delete all rows from the table, but retains the table's structure. Compared to delete, truncate is a very fast and efficient operation because it does not require deleting data row by row, but directly clears the data pages in the table. The truncate operation will not trigger the transaction log and cannot be rolled back. Therefore, once the truncate operation is performed, the deleted data will not be restored.
  • drop statement: The drop statement is used to delete objects in the database, including tables, views, indexes, etc. The drop statement is a physical deletion that completely deletes the specified object and all data and definitions related to the object. Drop operations are unrecoverable, so special care is required before performing drop operations to prevent data loss due to misoperation.

2. Execution speed

drop> truncate > delete

3. Usage scenarios

When deleting data in a database, there are often several situations:

  • It has been confirmed that the entire table and data is deleted, and there is no need to recover the data, use drop;
  • Delete data without deleting the table, but you may regret it after deleting it, so use delete;
  • I just want to delete the data, and don’t need to delete the table. I won’t regret it after deleting the data, so I use truncate;

4. Space occupied by tables and indexes:

  • When the table is truncate, the space occupied by the table and index will be restored to its initial size;
  • The delete operation will not reduce the space occupied by tables or indexes;
  • The drop statement releases all the space occupied by the table.

5. Summary

  • The delete statement can be partially deleted using the where clause, while truncate cannot. It will delete all the data in the table. When using it, you can choose according to your needs;
  • If you want to delete all data from the table, don't use delete. You can use the truncate statement because it will execute faster. The truncate statement actually deletes the original table and then rebuilds a new table;
  • Use drop and truncate with caution without backup. To delete a table structure, use drop;. For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE TABLE, but use a DELETE statement without the WHERE clause. Since TRUNCATE TABLE is not logged in the log, it cannot activate the trigger.
  • After deleting the empty table, an empty page will be retained, and truncate will not leave any pages in the table.
  • The DELETE statement performs the deletion process, which is to delete one row from the table each time, and at the same time save the delete operation of the row as a transaction record in the log for rollback operation.
  • TRUNCATE TABLE deletes all data from the table at once and does not record the separate deletion operation records in the log to save, and deletion rows cannot be restored. And the table-related delete trigger will not be activated during the deletion process. Fast execution speed.
  • When a DELETE statement is executed using a row lock, rows in the table are locked for deletion. truncate always locks tables and pages, not rows.
  • If there is an auto-increment id column generated by identity, the delete from still increases from the last number, that is, the seed remains unchanged; after using truncate to delete, the seed will be restored to the initial value.
  • delete delete content without deleting the table structure, you can delete all data or delete part of the data, delete one row by one, which is inefficient and can be rolled back.
  • truncate deletes content, does not delete the table structure, and deletes all data in the table. After the operation takes effect, it will be automatically submitted and cannot be rolled back.
  • drop not only deletes table contents and table structures, and executes the fastest.

2. Usage:

1.delete

1. Delete the data of the entire table:

delete from table_name;

2. Delete some data and add where clause:

delete  from table_name where...;

3. Explanation

  • delete can be used to delete the specified line in conjunction with the where clause.
  • It belongs to the DML language. Each time one line is deleted, one item is recorded in the transaction log for each line deleted. A rollback is generated, which takes effect only after the transaction is submitted; if there is a corresponding trigger, it will be triggered during execution, and if the table with large data volumes is deleted, it will be very slow.
  • Delete the data in the table without deleting the structure (definition) of the table, and do not free up space.
  • delete will not delete the index (newly inserted data will continue to be added after deleting the index of the data);

2.truncate

1. Only operate tables and delete all data in the table. Functionally the same as delete statements without the where clause:

truncate table table_name;

2. Explanation

  • By default, truncate deletes data by freeing the data pages used to store table data, and records only the release of pages in the transaction log. Therefore, there are few system and transaction log resources used, so you can use reuse storage; truncate will reset the high waterline (return to the beginning).
  • truncate is a DDL language. The operation takes effect immediately and is automatically submitted. The original data is not placed in the rollback segment and cannot be rolled back. The operation does not trigger trigger.
  • Structure (definition) that deletes content, frees up space but does not delete tables.

Three.drop

The statement will delete the table structure, as well as the dependencies, triggers, and indexes;

drop table table_name;

2. Explanation

  • After deletion, stored procedures/functions that depend on the table will be retained, but become invalid.
  • drop also belongs to the DDL language, executes immediately and has the fastest execution speed
  • Delete content and definitions to free up space.

This is the end of this article about the three operations of MySQL deletion tables (the difference between delete, truncate, and drop). For more related contents of mysql delete truncate drop, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!