Delete, Truncate, drop analysis for MySQL

Delete, Truncate, drop analysis for MySQL

Same point:

Truncate and delete without a WHERE clause, and drop deletes the data in the table

Different points

1. Truncate and delete only delete data without deleting the structure of the table (definition)

The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index, and the stored procedure/function that depends on the table will remain, but become invalid state.

2. The DELETE statement is a database manipulation language (DML), which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.

Truncate, drop is the database definition language (DDL), the operation takes effect immediately, the original data is not placed in the rollback segment, cannot be rolled back, the operation does not trigger trigger.

The 3.delete statement does not affect the extent occupied by the table, and the high waterline (watermark) remains in its original position.

It is clear that the drop statement frees all the space occupied by the table.

The TRUNCATE statement defaults to the space release to minextents extent unless using reuse storage;truncate resets the high watermark (back to the beginning).

4. Speed, in general: drop> truncate > Delete

5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.

For use, to delete some data rows with delete, note the WHERE clause. The rollback segment should be large enough.

Want to delete the table, of course with drop

To keep the table and delete all the data, if it is not related to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.

If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

Delete from Tablename Where condition

Truncate Table Tablename

Drop Table Tablename

The difference between Mysql "truncate" and "delete"

TRUNCATE TABLE table_name and DELETE from TABLE_NAME are all records in the delete table.

Difference:

Truncate can quickly empty a table. and resets the value of the auto_increment. Delete can only be deleted in one row.

However, for different types of storage engines, it is important to note that:

A for MyISAM

Truncate resets the value of Auto_increment to 1. The table remains auto_increment after delete.

B for InnoDB

Truncate resets the value of Auto_increment to 1. The table remains auto_increment after the delete. However, if you restart MySQL after you delete the entire table, the auto_increment will be reset to 1.

In other words, the InnoDB table itself is unable to persist auto_increment. After the delete table, the auto_increment is still in memory, but it is lost after a reboot, only starting from 1. The auto_increment that is essentially restarted will start with the SELECT 1+max (Ai_col) from T.

Delete, Truncate, drop analysis for MySQL