Detailed explanation of Truncate usage in MySQL

Preface:

When we want to clear a table, we often use the truncate statement. Most of the time we only care about whether the needs can be met, without thinking about the usage scenarios and precautions of such statements. This article mainly introduces the usage and precautions of the truncate statement.

1.Truncate usage syntax

The function of truncate is to clear the table or truncate the table, and it can only be used on the table. The syntax of truncate is very simple, just follow it with the table name, for example: truncate table tbl_name or truncate tbl_name .

Executing the truncate statement requires the drop permission for the table. Logically, truncate table is similar to a delete statement that deletes all rows or a combination of a drop table and then a create table statement. To achieve high performance, it bypasses the DML method of deleting data, so it cannot be rolled back. Although truncate table is similar to delete, it is classified as a DDL statement rather than a DML statement.

2. Comparison between truncate, drop, and delete

As mentioned above, truncate is very similar to delete and drop. In fact, there are still significant differences between these three. The following is a brief comparison of the similarities and differences between the three.

• truncate and drop are DDL statements and cannot be rolled back after execution; delete is a DML statement and can be rolled back.
• truncate can only be applied to tables; delete and drop can be applied to tables, views, etc.
• Truncate will clear all rows in the table, but the table structure and its constraints, indexes, etc. remain unchanged; drop will delete the table structure and the constraints, indexes, etc. on which it depends.
• Truncate resets the table's auto-increment value; delete does not.
• Truncate does not activate delete triggers associated with the table; delete does.
• After truncate, the space occupied by the table and index will be restored to the initial size; the delete operation will not reduce the space occupied by the table or index, and the drop statement will release all the space occupied by the table.

3. Truncate usage scenarios and precautions

Through the previous introduction, we can easily derive the usage scenario of the truncate statement, that is, truncate can be used when the table data is completely unnecessary. If you want to delete some data, use delete, and remember to include a where clause; if you want to delete a table, of course use drop; if you want to keep the table and delete all data and it has nothing to do with transactions, use truncate; if it is related to transactions, or you want to trigger a trigger, still use delete; if you want to sort out the fragments inside the table, you can use truncate and then reinsert the data.

In any case, truncate table is a high-risk operation, especially in production environment, you need to be more careful. Here are a few points to note, I hope you can use them as a reference when using.

• Truncate cannot be rolled back through binlog.
• Truncate will clear all data and execute very quickly.
• Truncate cannot be used on tables referenced by foreign key constraints.
• The drop permission is required to execute truncate. It is not recommended to give the account the drop permission.
• Be sure to check and confirm again before executing truncate. It is best to back up the following table data in advance.

The above is a detailed explanation of the usage of Truncate in MySQL introduced by the editor. I hope it will be helpful to everyone. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!