Delete/truncate/drop difference _ MySQL

Deletetruncatedrop difference bitsCN.com

Differences between Delete/truncate/drop

1. delete/truncate: only delete data. The index structure is not deleted. Drop will delete the table structure and the index/constrain/trigger that depends on the table. the procedure/function will be retained, but will change to the invalid state;

2. delete is a dml statement. it can be rolled back after the transaction is committed. A one-time delete of a large volume of data may cause a sharp expansion of the rollback segment, which affects the database. use it with caution and trigger the trigger. Truncate/drop is ddl, which is submitted implicitly. it does not write rollback segment and cannot be rolled back. it is fast.

3. delete does not affect the extent used by the table. HWM keeps the original position unchanged, even if the data closest to HWM is deleted. Delete can also release space, but it does not reduce HWM. after delete, the free space of The block reaches pct_used, and it can be reused. Truncate releases space (tables and indexes) to the minextents extent by default unless reuse storage is used. Truncate will reset the high water line (back to the beginning ). Drop releases all the space occupied by the table, and the segment does not exist. The concept of HWM does not matter;

4. The truncate/drop object must be in this mode or be granted the drop any table permission. However, the drop any table permission cannot be set to the truncate/drop sys table. The delete object must be in this mode or be granted the delete on SCHEMA. table or delete any table permission. However, the delete any table permission cannot be used to delete sys tables;

5. you cannot truncate a table with an enable foreign key. no matter whether the table has any data, if you want to truncate, first, you must disable the foreign key or delete the foreign key (the table that drop the foreign key must have deleted the foreign key ). You cannot drop a table with an enable foreign key, no matter whether the table has data or not. if you want to drop the table, you must first delete the foreign key, or directly use drop table TABLE_NAME cascade constraints; cascade to delete the foreign key. Yes.

BitsCN.com