Three ways to delete a table in MySQL (summary)

drop table

Drop directly deletes table information, which is the fastest, but data cannot be retrieved

For example, to delete the user table:

drop table user;

truncate (table)

truncate deletes table data but not its structure. It ranks second in speed but cannot be used with where.

For example, to delete the user table:

truncate table user;

delete from

delete deletes the data in the table without deleting the table structure. It is the slowest, but can be used with where to delete the specified rows.

For example, delete all data in the user table

delete from user;

Delete the specified record in the user table

delete from user where user_id = 1;

The difference between the three methods

Similarities

• Truncate, delete without a where clause, and drop will delete the data in the table;
• Both drop and truncate are DDL statements (data definition language) and will be automatically committed after execution;

Differences

• Statement type: delete statement is database manipulation language (DML), truncate and drop are database definition language (DDL);
• Efficiency: Generally speaking, drop > truncate > delete;
• Whether to delete the table structure: truncate and delete only delete data but not the table structure. After deletion, truncate will rebuild the index (the id will start from 0 after the new data is inserted), while delete will not delete the index (the new inserted data will continue to increase after the index of the deleted data). The drop statement will delete the table structure including dependent constraints, triggers, indexes, etc.
• Security: Drop and truncate do not record MySQL logs and cannot be rolled back, while delete will record MySQL logs and can be rolled back;
• Return value: delete returns the number of deleted records after the operation, while truncate returns 0 or -1 (0 if successful, -1 if failed);

Quick Facts

Difference between delete and delete from

If you only delete 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 the table structure, use drop;
• If you want to keep the table structure but delete all records, use truncate;
• If you want to keep the table structure but delete some records, use delete.

This concludes this article about three ways to delete a table in MySQL (summary). For more information about deleting a table in MySQL, please search for previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!