Shulou(Shulou.com)05/31 Report--

This article is to share with you about the differences in the implementation of table deletion in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

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

Brief introduction

Delete

1. Delete the data of the entire table:

Delete from table_name

2. Delete part of the data and add the where clause:

Delete from table_name where...

3. Description

1), belongs to the DML language, each time a row is deleted, an entry is recorded for each deleted row in the transaction log. When a rollback is generated, the transaction commits and takes effect; if there is a corresponding trigger, it will be triggered during execution, and it will be slow to delete tables with large amounts of data.

Delete the data in the table without deleting the structure (definition) of the table, and do not free up space.

Truncate

1. You can only manipulate the table and delete all the data in the table, which is the same as the delete statement without where clause:

Truncate table table_name

2. Description

1) by default, truncate deletes the data by releasing the page used to store the table data, and only records the release of the page in the transaction log. So using less system and transaction log resources, you can use reuse storage; truncate to reset the high waterline (back to the beginning).

2) truncate is a DDL language, the operation takes effect immediately, it is automatically submitted, and the original data is not put into rollback segment and cannot be rolled back. The operation does not trigger trigger.

3), delete the content, free up space without deleting the structure (definition) of the table.

Drop

1. The drop statement deletes the structure of the table, as well as dependent constraints (constrain), triggers (trigger), and indexes (index).

Drop table table_name

2. Description

1) after deletion, stored procedures / functions that depend on the table will be retained, but become invalid state.

2) drop also belongs to DDL language, which can be executed immediately and with the fastest execution speed.

3), delete content and definition, free up space.

Difference

1. Space occupied by tables and indexes:

When the table is TRUNCATE, the space occupied by the table and index is restored to its original size

The DELETE operation does not reduce the space occupied by the table or index

The DROP statement frees up all the space occupied by the table.

2. Scope of application:

TRUNCATE can only work with table

DELETE can be table and view.

3. Execution speed: drop > truncate > delete

4. After delete from deletes the table, it will keep an empty page, and truncate will not leave any pages in the table.

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

On the other hand, TRUNCATE TABLE deletes all the data from the table at once and does not log the individual delete operation records, and the delete row cannot be recovered. And table-related delete triggers are not activated during deletion. The execution speed is fast.

6. When the DELETE statement is executed with a row lock, the rows in the table are locked for deletion. Truncate always locks tables and pages, not rows.

7. If there is a self-increasing id column generated by identity, the number will still increase after delete from, that is, the seed will remain unchanged.

After you delete it using truncate, the seed returns to its original value.

Summary

1. Delete statement can use where clause to achieve partial deletion, but truncate cannot, it will delete all the data in the table. When you use it, you can choose according to your needs.

2. If you want to delete all data from the table, do not use delete, you can use the truncate statement, because it is faster to execute. The truncate statement actually deletes the original table and then creates a new one.

3. Use drop and truncate carefully when there is no backup. To delete a table structure, use drop

4. Instead of using TRUNCATE TABLE for tables referenced by FOREIGN KEY constraints, you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate the trigger.

Thank you for reading! About "what are the differences in the operation of deleting tables in MySQL?" this article ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.