The difference between delete, truncate, and drop and how to choose

Preface

Last week, a colleague asked me: "Brother, I found a bug in MySQL. I deleted 1 million MySQL data in the afternoon to clean up the disk, but the disk not only did not get smaller, but it became even fuller?"

So how did you delete it?

"delete from table"

"No wonder. Actually, there are several ways to delete MySQL data. DELETE should not be used in some scenarios, such as your situation. Okay, let me explain it to you."

What are the ways to delete data in MySQL?

We commonly use three deletion methods: deletion through delete, truncate, and drop keywords; all three can be used to delete data, but in different scenarios.

1. In terms of execution speed

drop > truncate >> DELETE

2. In principle

1. DELETE

DELETE from TABLE_NAME where xxx

1. DELETE belongs to the database DML operation language . It only deletes data but not the table structure. It will go through transactions and trigger a trigger when executed.

2. In InnoDB, DELETE does not actually delete the data. MySQL actually just marks the deleted data as deleted. Therefore, when DELETE deletes the data in the table, the space occupied by the table file on the disk will not be reduced, and the storage space will not be released. It just sets the deleted data rows to be invisible. Although the disk space is not released, it can still be reused the next time data is inserted (Reuse → Overwrite).

3. When DELETE is executed, the deleted data will be cached in the rollback segment first, and will take effect after the transaction is committed;

4. delete from table_name deletes all data in the table. For MyISAM, disk space will be released immediately, but InnoDB will not release disk space.

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

6. Use optimize table table_name after the delete operation to release disk space immediately. Whether it is InnoDB or MyISAM. Therefore, in order to free up disk space, execute the optimize table operation after deleting.

Example: The SQL statement to check the disk space occupied by a table is as follows: (Use M as the display unit, 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 

Looking at the size of this table, only the table structure size remains.

7. The delete operation is performed row by row, and the deletion operation log of the row is recorded in the redo and undo tablespaces for rollback and redo operations. The large amount of logs generated will also occupy disk space .

2. truncate

Truncate table TABLE_NAME

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

It takes effect immediately after execution and cannot be retrieved. It takes effect immediately after execution and cannot be retrieved.

2. truncate table table_name immediately frees up disk space, regardless of InnoDB or MyISAM . Truncate table is actually a bit similar to drop table and then create, except that the create table process has been optimized, for example, the table structure file already exists. So the speed should be close to that of drop table;

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

But for different types of storage engines, you need to pay attention to the following:

• For MyISAM, truncate resets the auto_increment value to 1. After deletion, the table still maintains auto_increment.
• For InnoDB, truncate resets the auto_increment value to 1. After delete, the table still maintains auto_increment. However, if you restart MySQL after deleting the entire table, the auto_increment will be set to 1 after the restart.

In other words, the InnoDB table itself cannot persist auto_increment. After deleting the table, auto_increment is still saved in memory, but it is lost after restart and can only start from 1. In fact, the auto_increment after restart will start from SELECT 1+MAX(ai_col) FROM t.

4. Be careful when using truncate, especially when there is no backup. If you delete an online table by mistake, remember to contact China Civil Aviation in time. Ticket booking phone number: 400-806-9553

3. drop

Drop table Tablename

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

It takes effect immediately after execution and cannot be retrieved. It takes effect immediately after execution and cannot be retrieved.

2. drop table table_name will immediately release disk space, regardless of whether it is InnoDB or MyISAM; the drop statement will delete the constraints, triggers, and indexes that the table structure depends on; the stored procedures/functions that depend on the table will be retained, but will become invalid.

3. Be careful when using drop. If you want to delete the form and run away, please execute the operation after successfully booking the ticket! Ticket booking hotline: 400-806-9553

You can understand it this way: for a book, delete is to tear off the table of contents, truncate is to tear off the contents of the book and burn them, and drop is to burn the book.

Summarize

This is the end of this article about the differences between delete, truncate, and drop and how to choose. For more information about the differences and selections between delete, truncate, and drop, please search previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!