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

This article mainly introduces "what is the difference between drop, truncate and delete". In daily operation, I believe that many people have doubts about the difference between drop, truncate and delete. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what is the difference between drop, truncate and delete?" Next, please follow the editor to study!

Functions and characteristics of 1.drop, truncate and delete

Characteristics of 01.Drop

Drop table not only deletes tables at the database level, but also deletes files such as xxx.ibd,xxx.frm (InnoDB tables) or xxx.MYD,xxx.MYI,xxx.frm (MyISAM) at the system level.

Drop table if exists table_name can prevent errors that do not exist in the table, but there will be a warning

Drop table will also submit implicitly, except for temporary tables

Characteristics of 02.Truncate

Logically, truncate table is similar to delete from table_name;, but the process is drop table first, and then re-create table. If you want to empty all the data rows of a large table, truncate is more efficient than delete.

Truncate is a DDL operation, which will be implicitly committed once executed, which means that truncate cannot rollback, so be careful before execution.

If the watch is locked, truncate will report an error

If there is a foreign key constraint, truncate will also report an error

For the auto_increment column of the InnoDB/MyISAM table, the sequence value can be reused after truncate table

Truncate table cannot trigger delete-related triggers

Characteristics of 03.Delete

Delete is a DML operation. If it is not submitted, you can rollback it.

For auto_increment columns of the InnoDB/MyISAM table, sequence values can not be reused after delete table, but sequences can be reused after restart

Delete from table_name must be followed by a where condition, otherwise all rows of the table will be deleted

Summary:

01. Execution efficiency: drop > truncate > delete

02. It is important to be careful when using drop and truncate, although it can be recovered, but in order to reduce hassle.

03. If you want to delete part of the data with delete, be careful to take the where clause, and the rollback segment should be large enough; if you want to delete the table, of course, use drop; to delete all the data if you want to keep the table, or use truncate if it has nothing to do with transactions; if it is related to transactions, or you want to trigger trigger, or use delete; if you are defragmenting the table, you can use truncate to keep up with reuse stroage and then import / insert the data again.

The difference between 2.truncate and delete

Both delete and truncate can be used to delete data in mysql and oracle databases, but they are somewhat different.

Brother Feng introduces you to the following differences:

The first difference between 01.truncate and delete:

According to the sql language classification, delete belongs to DML, that is, data manipulation language, while truncate belongs to DDL language, that is, data definition language.

The difference between 02.truncate and delete:

Delete can delete not only the data in the table, but also the data in the view created based on the real table. Truncate can only delete the data in the table and cannot act on the view.

The third difference between 03.truncate and delete:

Because delete deleting data involves transaction processing and caching, when the data is very large, the cache may not be enough, so deleting data using delete may not achieve the purpose of deletion, and the speed is very slow. Truncate is a good fit when it comes to large amounts of data, because it doesn't involve caching, so it's particularly fast and efficient.

The difference between 04.truncate and delete 4:

When the database deletes data, delete can delete data as needed, that is, delete can delete a certain piece of data accurately combined with where conditions, while truncate cannot support the where clause. If it acts on the table, it deletes all the data in the table directly.

The difference between 05.truncate and delete five:

When delete deletes a table, it involves transaction processing, that is, it can support transaction commit and rollback. Transactions support data consistency, atomicity, persistence, and isolation. When you delete table data using delete, it does not actually delete it at first, but instead puts the deleted data into a cache, which can be recovered by rollback. However, when deleting data from a table using truncate, truncate does not involve transactions, so the data deleted by truncate is unrecoverable.

The difference between 06.truncate and delete 6:

For some associated tables, when using delete to delete data, the business logic order of sql can be used to achieve the purpose of deleting data. For example, there is an one-to-many relationship between table An and table B, where id is the primary key and id is the foreign key of table B. To delete data, table B cannot be deleted directly at this time, but table A should be deleted before table B is deleted, so that the goal can be achieved. While truncate cannot delete a table constrained by a foreign key, it does not achieve the purpose of deleting the table.

The difference between 07.truncate and delete 7:

Sometimes triggers may be involved in data manipulation, and triggers may be activated when data is deleted with delete. However, there is no trigger processing in truncate.

At this point, the study of "what is the difference between drop, truncate and delete" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.