MySQL table deletion operation implementation (differences between delete, truncate, and drop)

This article mainly discusses the differences between three operations of deleting tables in MySQL, the delete statement, the truncate statement, and the drop statement:

Introduction

delete

1. Delete the data of the entire table:

delete from table_name;

2. Delete some data and add a where clause:

delete from table_name where...;

3. Description

1) It belongs to DML language. Every time a row is deleted, an entry is recorded in the transaction log for each row deleted. A rollback is generated and takes effect only after the transaction is committed; if there is a corresponding trigger, it will be triggered during execution. If a table with a large amount of data is deleted, the speed will be very slow.
2) Delete the data in the table without deleting the table structure (definition) and releasing the space.

truncate

1. You can only operate on the table and delete all the data in the table. Its function is the same as the delete statement without the where clause:

truncate table table_name;

2. Description

1) By default, truncate deletes data by releasing the data pages used to store table data, and only records the release of pages in the transaction log. Therefore, less system and transaction log resources are used, and reuse storage can be used; truncate will reset the high watermark (back to the beginning).
2) Truncate is a DDL language. The operation takes effect immediately and is automatically committed. The original data is not placed in the rollback segment and cannot be rolled back. The operation does not trigger a trigger.
3) Delete the content and free up space but do not delete the table structure (definition).

drop

1. The drop statement will delete the table structure, as well as the dependent constraints, triggers, and indexes;

drop table table_name;

2. Note 1) After deletion, the stored procedures/functions that depend on the table will be retained, but will become invalid.
2) Drop also belongs to DDL language, which is executed immediately and has the fastest execution speed. 3) Delete content and definition to free up space.

the difference

1. Space occupied by tables and indexes:
When a table is TRUNCATEd, the space occupied by the table and indexes will be restored to their original size;
DELETE operations do not reduce the space occupied by tables or indexes;
The DROP statement releases all the space occupied by the table.

2. Application scope:
TRUNCATE can only be performed on table;
DELETE can be a table or a view.

3. Execution speed: drop > truncate > delete

4. After deleting an empty table, delete from will retain an empty page, and truncate will not leave any pages in the table.

5. The process of executing the DELETE statement is to delete one row from the table each time, and at the same time save the deletion operation of the row as a transaction record in the log for rollback operation.

TRUNCATE TABLE deletes all data from the table at once without logging individual deletion operations. Deleted rows cannot be recovered. And the delete triggers related to the table will not be activated during the deletion process. Fast execution speed.

6. When a DELETE statement is executed using a row lock, each row in the table will be locked for deletion. truncate always locks tables and pages, not individual rows.

7. If there is an auto-increment id column generated by identity, it will still increase from the last number after delete from, that is, the seed remains unchanged;

After deleting using truncate, the seed will be restored to the initial value.

Summarize

1. The delete statement can use the where clause to achieve partial deletion, but truncate cannot, which will delete all the data in the table. When using it, you can choose according to your needs;
2. If you want to delete all the data from the table, do not use delete, you can use truncate statement, because it executes faster. The truncate statement actually deletes the original table and then creates a new table;
3. When there is no backup, use drop and truncate with caution. To delete the table structure use drop;
4. For tables referenced by FOREIGN KEY constraints, TRUNCATE TABLE cannot be used. Instead, a DELETE statement without a WHERE clause should be used. Because TRUNCATE TABLE is not logged, it cannot activate triggers.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.