How InnoDB handles incremented ids when deleting rows in batches is a potential pit.

The whole experiment procedure is shown in the figure above:

The first step: build a table, set the autoincrement column;

Insert id=1, anchor first line id=1;

Step 3: Do not specify id, rely on the auto-increment mechanism, insert 3 lines;

Voice-over: should id now be 2,3,4?

Step 4: delete Deletes all records;

Voiceover: The pit is easy to come out of here.

Step 5: Specify id=0 insert;

Step 6: Specify id=1 insert;

Step 7: Do not specify id, rely on auto-increment mechanism, insert 1 row;

What are the ids of the three rows in the table?

Does it meet everyone’s expectations?

In today’s 1 minute lecture, I will talk about the differences between using truncate and DELETE to batch delete data.

There are three common methods for deleting data in batches

drop table

You can use this method when the table is not needed.

truncate table

Deleting all data while keeping tables is fast.

Voiceover: Drop table then create table.

delete from table

You can delete all data and keep tables, but the performance is poor.

You can also delete some data with where conditions, which is flexible.

Although truncate and DELETE can delete all data and retain the table, there are significant differences between them.

A,

Truncate is a DDL statement, which does not have the so-called “transaction rollback”;

Delete is a DML statement that can rollback after execution.

Second,

Truncate table returns 0;

Delete from table returns the number of rows that were deleted.

Three,

InnoDB supports one table per file.

Truncate will kill the table at one time and will not activate the trigger, which is very fast.

Delete from table deletes a row by row, which activates the trigger and is slow.

Voiceover: Delete data is to be logged. The TRUNCate table does not need to be logged.

Four,

When a column in a table is used as a foreign key by another table:

Truncate is failure;

Delete succeeds.

Voice-over: This type of data deletion failure is easy to locate because the error message is easy to understand.

Five,

When there are autoincrement columns in a table:

Truncate restores the increment column count;

After deleting all data, increment column counting does not start from scratch.

Voiceover: Therefore, the act of incrementing the column count after deleting all data is often undesirable and therefore a potential pitfall.

In this minute, did you get anything?

Please select the deletion mode according to your own business scenario