MySql Delete, Truncate, and Drop analysis

Similarities:

Truncate, delete without where clause, and drop both delete table data.

Differences

1. truncate and delete: delete only data. Do not delete the table structure (Definition)

The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but the status changes to invalid.

2. The delete statement is the database operation language (dml). This operation will be placed in the rollback segement and take effect after the transaction is committed. If a trigger exists, it will be triggered during execution.

Truncate and drop are database Definition Language (ddl). The operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. trigger is not triggered.

3. The delete statement does not affect the extent used by the table, and the high watermark keeps the original position unchanged.

Obviously, the drop statement releases all the space occupied by the table.

The truncate statement is released to the minextents extent by default, unless reuse storage is used; The truncate will reset the high water line (back to the beginning ).

4. Speed. Generally, drop> truncate> delete

5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.

To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.

To delete a table, use drop

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

If you want to organize fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data.

Delete from Tablename where Condition

Truncate table Tablename

Drop table Tablename