Explain the difference between DROP TRUNCATE and DELETE in MySQL to realize mysql starting from zero
- 2020-05-06 11:48:32
- OfStack
Differences:
1. truncate and delete only delete data but not the structure (definition) of the table The drop statement will remove the dependent constraints on the structure of the table (constrain), triggers (trigger), indexes (index); depends on
of the table The stored procedure/function remains, but becomes invalid.
2.delete statement is dml, this operation will be put into rollback segement, the transaction will not take effect until committed; If you have trigger,
This will be triggered during execution.
truncate,drop is ddl, operation immediately takes effect, the original data cannot be rolled back without being put into rollback segment. operation does not trigger
trigger.
3.delete statements do not affect the extent, high waterline (high watermark) occupied by the table, leaving
unchanged The statement obviously frees up
of all the space occupied by the table truncate statement by default sees the space freed to minextents extent, unless reuse storage is used; truncate
Will reset the high waterline (back to the beginning).
4. Speed, in general: drop> truncate > delete
5. Safety: be careful with drop and truncate, especially if you don't have a backup If you want to delete some rows of data using delete, be sure to include the where clause. rollback section should be large enough.
To delete a table, drop
, of course If is not associated with a transaction, use truncate. If is associated with a transaction, or if you want to trigger trigger,
delete.
If it is a fragment inside the cleaning table, you can use truncate to keep up with reuse stroage and then re-import/insert data/
Practical application:
delete from table; // delete all data
truncate table; // starts the auto_increatement modulation at 0, which is actually starting at 1
1. truncate and delete only delete data but not the structure (definition) of the table The drop statement will remove the dependent constraints on the structure of the table (constrain), triggers (trigger), indexes (index); depends on
of the table The stored procedure/function remains, but becomes invalid.
2.delete statement is dml, this operation will be put into rollback segement, the transaction will not take effect until committed; If you have trigger,
This will be triggered during execution.
truncate,drop is ddl, operation immediately takes effect, the original data cannot be rolled back without being put into rollback segment. operation does not trigger
trigger.
3.delete statements do not affect the extent, high waterline (high watermark) occupied by the table, leaving
unchanged The statement obviously frees up
of all the space occupied by the table truncate statement by default sees the space freed to minextents extent, unless reuse storage is used; truncate
Will reset the high waterline (back to the beginning).
4. Speed, in general: drop> truncate > delete
5. Safety: be careful with drop and truncate, especially if you don't have a backup If you want to delete some rows of data using delete, be sure to include the where clause. rollback section should be large enough.
To delete a table, drop
, of course If is not associated with a transaction, use truncate. If is associated with a transaction, or if you want to trigger trigger,
delete.
If it is a fragment inside the cleaning table, you can use truncate to keep up with reuse stroage and then re-import/insert data/
Practical application:
delete from table; // delete all data
truncate table; // starts the auto_increatement modulation at 0, which is actually starting at 1