Detailed explanation of Truncate usage in MYSQL

This article guide: 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 the deletion of a single row. 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.

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.

This is the end of this article about the detailed usage of Truncate in MYSQL. For more relevant MYSQL Truncate content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!