Implementation of MySQL Delete Table Operation (delete truncate drop Difference)

This article mainly talks about three kinds of table deletion operations in mysql, and the differences between delete statement, truncate statement and drop statement:

Brief introduction

delete

1. Delete the data of the whole table:

delete from table_name;

2. Delete some data and add where clause:

delete from table_name where...;

3. Description

1), belongs to the DML language, every time delete 1 row, in the transaction log for each deleted row record 1. rollback, transaction commits only after the effective; If there is a corresponding trigger, the execution will be triggered, if delete large data table speed will be very slow.
2) Delete the data in the table without deleting the structure (definition) of the table and without freeing up space.

truncate

1. You can only operate the table and delete all the data in the table, which is the same as delete statement without where clause in function:

truncate table table_name;

2. Description

1) By default, truncate deletes data by releasing the data pages used to store the table data, and only records the release of the pages in the transaction log. Therefore, the system and transaction log resources used are less, and reuse storage; can be used; truncate resets the high waterline (back to the beginning).
2), truncate is DDL language, operation immediately effective, automatic submission, the original data does not put rollback segment, can not be rolled back. Operation does not trigger trigger.
3) Delete content, free space without deleting the structure (definition) of the table.

drop

1. The drop statement will delete the structure of the table, as well as the dependent constraints (constrain), triggers (trigger) and indexes (index);

drop table table_name;

2. Description
1) After deletion, stored procedures/functions that depend on the table remain, but become invalid.
2), drop also belongs to DDL language, which executes immediately and has the fastest execution speed
3) Delete contents and definitions and free up space.

Difference

1. Table and index space:
When the table is TRUNCATE, the space occupied by this table and index will be restored to the original size;
DELETE operations do not reduce the space occupied by tables or indexes;
The DROP statement frees up all the space occupied by the table.

2. Application scope:
TRUNCATE can only be applied to table;
DELETE can be table and view.

3. Execution speed: drop > truncate > delete

4. After delete from deletes an empty table, one empty page will be left, while truncate will not leave any pages in the table.

5. The DELETE statement deletes one row from the table at a time, and at the same time saves the deletion operation of the row as a transaction record in the log for rollback operation.

TRUNCATE TABLE deletes all the data from the table once and does not record the separate deletion operation record, and the deleted rows cannot be recovered. And the table-related delete triggers are not activated during the delete process. Fast execution speed.

6. When an DELETE statement is executed using row locks, rows in the table are locked for deletion. truncate always locks tables and pages, not rows.

7. If there is a self-increasing id column generated by identity, the number of delete and from will still increase from the last time, that is, the seeds will remain unchanged;

After being deleted using truncate, the seed will be restored to its original value.

Summarize

1. delete statement can use where clause to realize partial deletion, but truncate can't, which will delete all the data in the table. When using it, you can choose according to your needs;
2. If you want to delete all the data from the table, instead of using delete, you can use truncate statement because it is faster to execute. The truncate statement actually deletes the original table and re-establishes a new table;
3. Use drop and truncate carefully without backup. To delete the table structure use drop;
4. For tables referenced by an FOREIGN KEY constraint, you cannot use TRUNCATE TABLE, but you should use an DELETE statement without an WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate triggers.