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

What is the difference between delete, truncate and drop? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

What are the ways in which MySQL deletes data?

There are three common ways to delete data: delete through the delete, truncate, and drop keywords; all three can be used to delete data, but in different scenarios.

First, in terms of execution speed

Drop > truncate > > DELETE

Second, in principle

1 、 DELETE

DELETE from TABLE_NAME where xxx

1. DELETE belongs to the database DML operation language. It only deletes the data and does not delete the table structure. It will walk through the transaction and trigger trigger when it is executed.

2. In InnoDB, DELETE does not really delete the data, but mysql actually marks the deleted data as deleted, so when delete deletes the data in the table, the table file will not take up less space on disk and the storage space will not be released, just set the deleted data rows to be invisible. Although disk space is not freed, it can still be reused the next time you insert data (reuse → overrides).

3. When DELETE executes, the deleted data will be cached in rollback segement first, and the transaction commit will take effect later.

4. Delete from table_name deletes all data from the table. For MyISAM, disk space will be released immediately, and InnoDB will not free disk space.

5. For conditional deletion of delete from table_name where xxx, neither InnoDB nor MyISAM will free disk space.

6. Using optimize table table_name after delete operation will release disk space immediately. Whether it's InnoDB or MyISAM. So to achieve the goal of freeing up disk space, delete performs optimize table operations later. Summary of previous interview questions: 001 ~ 150

Example: the SQL statement to view the amount of hard disk space occupied by the table is as follows: (display unit in M, database name: csjdemo, table name: demo2)

Select concat (round (sum (DATA_LENGTH/1024/1024), 2),'M') as table_size from information_schema.tables where table_schema='csjdemo' AND table_name='demo2'

Then execute the space optimization statement, and the table Size changes after execution:

Optimize table demo2

If you look at the size of this table, all that's left is the table structure size.

7. The delete operation is deleted by one row, and the delete operation of the row is recorded in the redo and undo table spaces at the same time for rollback and redo operations, and a large number of logs generated will also take up disk space. Summary of previous interview questions: 001 ~ 150

2 、 truncate

Truncate table TABLE_NAME

1. Truncate: belongs to the database DDL definition language. It does not take transactions, the original data is not put into rollback segment, and the operation does not trigger trigger.

Effective immediately after execution and cannot be recovered

Effective immediately after execution and cannot be recovered

Effective immediately after execution and cannot be recovered

2. Truncate table table_name immediately frees up disk space, whether it's InnoDB or MyISAM. Truncate table is actually a bit like drop table and then creat, except that the create table process is optimized, such as table structure files, and so on. So the speed should be close to the speed of drop table.

3. Truncate can quickly empty a table. And reset the value of auto_increment.

But what you need to pay attention to for different types of storage engines are:

For MyISAM,truncate, the value of auto_increment (self-increasing sequence) is reset to 1. However, the table still keeps auto_increment after delete.

For InnoDB,truncate, the value of auto_increment is reset to 1. The table remains auto_increment after delete. However, if you restart MySQL after delete the entire table, the rebooted auto_increment will be set to 1.

In other words, the InnoDB table itself cannot persist the auto_increment. The auto_increment is still saved in memory after the delete table, but it is lost after reboot and can only start at 1. Essentially, the rebooted auto_increment starts with SELECT 1+MAX (ai_col) FROM t.

4. Use truncate carefully, especially when there is no backup. If you delete the online form by mistake, remember to contact CAAC in time. Booking telephone number: 400806-9553

3 、 drop

Drop table Tablename

1. Drop: belongs to database DDL definition language, same as Truncate

Effective immediately after execution and cannot be recovered

Effective immediately after execution and cannot be recovered

Effective immediately after execution and cannot be recovered

2. Drop table table_name immediately frees disk space, whether InnoDB and MyISAM; drop statements will delete the dependent constraints (constrain), trigger (trigger), and index (index) of the table structure; stored procedures / functions that depend on the table will be retained, but will become invalid state.

The answer to the question about what is the difference between delete, truncate and drop is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.