Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about how to delete tables in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Drop table

Drop deletes table information directly, which is the fastest, but cannot retrieve data.

For example, delete the user table:

Drop table user

Truncate (table)

Truncate deletes table data, does not delete table structure, and ranks second in speed, but cannot be used with where

For example, delete the user table:

Truncate table user

Delete from

Delete deletes data from a table and does not delete the table structure. It is the slowest, but it can be used with where to delete specified rows.

For example, delete all data in the user table

Delete from user

Delete the specified record of the user table

Delete from user where user_id = 1

The difference between the three ways

Identical point

Both truncate and delete,drop without a where clause delete the data in the table

Drop,truncate are all DDL statements (data definition language), which are automatically submitted after execution.

Differences

Statement type: delete statement is the database operation language (DML), and truncate,drop is the database definition language (DDL)

Efficiency: generally speaking, drop > truncate > delete

Whether to delete the table structure: truncate and delete delete only the data but not the table structure. The index will be rebuilt after the truncate is deleted (id starts at 0 after the newly inserted data is inserted), while the delete will not delete the index (the newly inserted data will continue to increase after deleting the index of the data). The drop statement will delete the structure of the table, including dependent constraints, triggers, indexes, etc.

Security: drop and truncate delete do not record MySQL logs and cannot be rolled back. Delete deletion records MySQL logs, which can be rolled back

Return value: the number of deleted records is returned after the delete operation, while truncate returns 0 or-1 (0 for success and-1 for failure)

A little knowledge

The difference between delete and delete from

If you delete only one table, the effect is the same; if you need to join other tables, you need to use from

Delete tb1 from tb1 m where id in (select id from tb2)

Usage summary

When you want to delete a table structure, use drop

You want to keep the table structure, but when you want to delete all records, use truncate

You want to keep the table structure, but use delete when you want to delete some records.

The above is how to delete the table in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.