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

This article will explain in detail what is the difference between drop, truncate and delete. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Note: delete refers to the same point of delete statements without where clause truncate and delete without where clause, and drop will delete the data differences in the table: 1. Truncate and delete will only delete the structure of the table without deleting the table structure (definition) drop statement will delete the table structure dependent constraints (constrain), trigger (trigger), index (index); stored procedures / functions dependent on the table will be retained, but become invalid state. The 2.delete statement is dml, and this operation will not take effect until the transaction is committed; if there is a corresponding trigger, it will be triggered when it is executed. Truncate,drop is ddl, and the operation takes effect immediately. The original data is not put into rollback segment and cannot be rolled back. The operation does not trigger trigger. The 3.delete statement does not affect the extent occupied by the table, and the high waterline (high watermark) maintains the original position. Obviously, the drop statement frees up all the space occupied by the table. The truncate statement releases the space to minextents extent by default, unless the use of reuse storage; truncate resets the high waterline (back to the beginning). 4. Speed, generally speaking: drop >; truncate >; delete 5. Security: be careful with drop and truncate, especially when there is no backup. Otherwise, it is too late to cry. If you want to delete some data lines with delete, take the where clause with you. The rollback section should be large enough. If you want to delete the table, of course use drop to keep the table and delete all the data. If it has nothing to do with the transaction, just use truncate. If it is related to a transaction, or if you want to trigger trigger, use delete. If you are defragmenting the table, you can use truncate to keep up with reuse stroage, and then re-import / insert the data: in practical application, the difference between the three is clear. When you no longer need the table, use drop; when you still want to keep the table, but to delete all records, use truncate; when you want to delete partial records (always with a WHERE clause), use delete.

About drop, truncate and delete what is the difference between sharing here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it 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.