A brief analysis of MySQL deletion methods: the difference between delete, truncate, and drop

In MySQL, there are three deletion methods: delete, truncate, and drop. The usage and usage scenarios of the three are completely different. Let’s take a look at them in detail.

1.delete

detele can be used to delete some or all data in a table. Its usage syntax is as follows:

delete from table_name [where...] [order by...] [limit...]

PS: Commands in [] are optional and can be omitted.

If we want to delete the top 3 students with the highest math scores in the student table, we can use the following SQL:

delete from student order by math desc limit 3;

1.1 delete implementation principle

In the InnoDB engine, the delete operation does not actually delete the data, but marks the data for deletion and marks it as deleted. We can test this by setting MySQL to non-automatic submission mode. Verify it. The setting SQL for non-automatic commit mode is as follows:

set autocommit=0;

After that, delete a data first, then use the rollback operation, and finally verify whether the data we deleted before still exists. If the data still exists, Note that delete does not actually delete the data, it just marks the data as deleted. The verification SQL and execution results are as shown in the figure below:

1.2 About Auto-increment column

In the InnoDB engine, after using delete to delete all data, the auto-increment column will not be reset to its initial value. We can verify it with the following command:

2.truncate

The execution effect of truncate is similar to delete. It is also used to delete all rows of data in the table. Its usage syntax is as follows:

truncate [table] table_name

The biggest difference between truncate and delete in use is that delete can use conditional expressions to delete part of the data, while truncate cannot add conditional expressions, so it can only delete all row data, such as the following After adding the where command to truncate, an error will be reported:

2.1 Implementation principle of truncate

truncate seems to only delete row data. But it is a DDL statement, which is Data Definition Language. It is used to maintain the structural instructions of stored data, so this is different from the delete command. The delete statement belongs to DML, Data Manipulation Language. Used to operate on data. Why does truncate only delete row data but not column data (fields, indexes, etc.) but it is DDL language? This is because truncate essentially creates a new table structure and then deletes the original table, so it belongs to the DDL language, not the DML language.

2.2 Reset the auto-increment column

truncate will reset the auto-increment column in the InnoDB engine, as shown in the following command:

3.drop

drop is different from the first two commands that only delete the row data of the table. drop will delete the row data of the entire table and the table structure together. , its syntax is as follows:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name]

where TEMPORARY means temporary table. Under normal circumstances, this command will be ignored.

drop Usage examples are as follows:

The difference between the three

• Data recovery: delete can recover deleted data, but truncate and drop cannot recover deleted data.

• Execution speed: drop > truncate > delete.

• In terms of deleting data: drop deletes the entire table, including row data, fields, indexes and other data, while truncate and drop only delete row data.

• In terms of adding conditions: delete can use where expressions to add query conditions, while truncate and drop cannot add where query conditions.

• Resetting auto-increment columns: In the InnoDB engine, truncate can reset auto-increment columns, but delete cannot reset auto-increment columns.

Summary

delete and truncate can be used to delete row data in the table, while drop deletes the entire table. The deleted data includes all Row data, fields, indexes and other data. Data deleted by delete can be recovered, while truncate and drop are irrecoverable. However, in terms of execution efficiency, the latter two deletion methods have great advantages, so they should be based on the actual scenario. To select the corresponding deletion command, of course, you should also be careful when using truncate and drop, which are unrecoverable data deletion methods.

[Related recommendations: mysql video tutorial]

The above is the detailed content of A brief analysis of MySQL deletion methods: the difference between delete, truncate, and drop. For more information, please follow other related articles on the PHP Chinese website!