Detailed explanation of MySQL Truncate usage

There are two ways to delete data in a table: delete and truncate. TRUNCATE TABLE is used to delete all rows in a table without recording individual row deletion operations. TRUNCATE TABLE is similar to a DELETE statement without a WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources. The following describes the usage of Truncate in SQL

When you no longer need the table, use drop; when you still want to keep the table but delete all records, use truncate; when you want to delete some records (always with a WHERE clause), use delete.

Truncate is a SQL syntax that can quickly clear all data in a table. It can also reset the count to zero and recalculate fields with automatically incremented values.

MySQL Truncate usage

1. Truncate syntax

[ { database_name.[ schema_name ]. | schema_name . } ]
table_name
[ ; ]

parameter

database_name
The name of the database.

schema_name
The name of the schema to which the table belongs.

table_name
The name of the table to be truncated, or the name of the table whose rows are to be deleted.

2. Precautions for using Truncate

1. TRUNCATE TABLE is functionally identical to a DELETE statement without a WHERE clause: both delete all rows in a table. However, TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources.

2. The DELETE statement deletes one row at a time and records an entry in the transaction log for each row deleted. TRUNCATE TABLE removes data by freeing the data pages used to store table data, and only records the freeing of pages in the transaction log.

3. TRUNCATE TABLE deletes all rows in the table, but the table structure and its columns, constraints, indexes, etc. remain unchanged. The count value used to identify new rows is reset to the seed for the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete a table definition and its data, use the DROP TABLE statement.

4. For tables referenced by FOREIGN KEY constraints, TRUNCATE TABLE cannot be used. Instead, a DELETE statement without a WHERE clause should be used. Because TRUNCATE TABLE is not logged, it cannot activate triggers.

5. TRUNCATE TABLE cannot be used for tables participating in indexed views.

6. When adding data to a table that has been deleted using TRUNCATE TABLE, use UPDATE STATISTICS to maintain index information.

7. If there is a ROLLBACK statement, the DELETE operation will be undone, but the TRUNCATE will not be undone.

3. TRUNCATE TABLE cannot be used on the following tables

1. Tables referenced by FOREIGN KEY constraints. (You can truncate a table that has a foreign key that references itself.)

2. Tables participating in the index view.

3. Tables published by using transactional replication or merge replication.

4. For tables with one or more of the above characteristics, use the DELETE statement.

5. TRUNCATE TABLE cannot activate triggers because the operation does not record individual row deletions.

IV. Differences between TRUNCATE, Drop, and Delete

1. Drop and delete only delete the data (definition) of the table. The drop statement will delete the table structure, dependent constraints, triggers, and indexes; the stored procedures/functions that depend on the table will be retained but become invalid.
2. The delete statement is a DML language. This operation will be placed in the rollback segment and will take effect only after the transaction is committed. If there is a corresponding trigger, it will be triggered when it is executed. Truncate and drop are DDL languages. They take effect immediately after the operation. The original data will not be put into the rollback and cannot be rolled back. The operation will not trigger the trigger.

3. The delete statement does not affect the extent occupied by the table, and the high watermark remains in its original position. The drop statement releases all the space occupied by the table. By default, the truncate statement releases space to the extent of minextents unless reuse storage is used. Truncate will reset the high water mark (back to the beginning).

4. Efficiency: drop > truncate > delete

5. Security: Use drop and truncate with caution, especially when there is no backup. If you want to delete some data, you can use delete with a where clause and the rollback segment must be large enough. If you want to delete a table, you can use drop. If you want to keep the table but just want to delete all the data in the table, you can use truncate if it is not related to the transaction. If it is related to the transaction 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 followed by reuse stroage, and then re-import and insert the data.

6.delete is a DML statement and will not be automatically submitted. Drop/truncate are both DDL statements and will be automatically committed after execution.

7. Drop is generally used to delete overall data such as tables, schemas, indexes, views, integrity constraints, etc.; delete is used to delete local data such as a tuple in a table.

8. DROP deletes the table structure; DELETE only clears the data

9. When you no longer need the table, use drop; when you still want to keep the table but delete all records, use truncate; when you want to delete some records (always with a WHERE clause), use delete.

Difference between TRUNCATE and DELETE in MYSQL

In MYSQL, both TRUNCATE and DELETE can clean up the data in the table, but what is the difference between them? We analyze from the following points:

1. Conditional deletion

This is easier to understand because DELETE can have a WHERE clause, so it supports conditional deletion; whereas TRUNCATE can only delete the entire table.

# delete - conditional deletion DELETE FROM student WHERE id = 1;
# delete - delete the data of the entire table DELETE FROM student;
# truncate - delete the data of the entire table TRUNCATE TABLE student;

2. Transaction rollback

Since DELETE is a data manipulation language (DML), the original data will be placed in the rollback segment during the operation and can be rolled back; while TRUNCATE is a data definition language (DDL), the original data will not be stored during the operation and cannot be rolled back.

You can see that DELETE can be rolled back successfully.

It can be seen that TRUNCATE cannot be rolled back successfully.

3. Cleaning speed

When the amount of data is relatively small, the difference in cleanup speed between DELETE and TRUNCATE is not very large. But when the amount of data is large, the difference can be seen. As mentioned in the second item, TRUNCATE does not need to support rollback, so it uses fewer system and transaction log resources. The DELETE statement deletes one row at a time and records an entry in the transaction log for each row deleted. Although it is slow, it is relatively safe.

4. High Water Mark Reset

As DML operations on table records are continuously performed, the high water mark (HWM) of the table will continue to increase. Although the data in the table is deleted after the DELETE operation, the high water mark of the table is not lowered. As DML operations occur, the database capacity will only increase, not decrease. Therefore, if DELETE is used, even if the data in the table is reduced a lot, the query speed is still the same as before the DELETE operation.
The TRUNCATE operation will reset the high water mark, and the database capacity will also be reset, and the speed of subsequent DML operations will also be improved.

MySQL Truncate usage supplement

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 the detailed content of the detailed usage of MySQL Truncate. For more information about MySQL Truncate, please pay attention to other related articles on 123WORDPRESS.COM!