The difference between drop, truncate, and delete in MySQL

The difference between drop, truncate, and delete in MySQL

(1) The DELETE statement performs the deletion by deleting one row from the table at a time and saving the row's delete operation as a transaction record in the log for the rollback operation.

TRUNCATE table deletes all the data from the table at once and does not record the individual deletion records in the log, and deleting rows is not recoverable. Delete triggers related to the table are not activated during the removal process. Execution speed is fast.

(2) The space occupied by the table and index.

When the table is truncate, the space occupied by the table and index is restored to its original size.

The delete operation does not reduce the space occupied by the table or index.

The drop statement frees all the space occupied by the table.

(3) Generally, drop > truncate > Delete

(4) Scope of application.

TRUNCATE can only be used on table; Delete could be table and view

(5) TRUNCATE and delete Delete only the data, and drop deletes the entire table (structure and data).

(6) Truncate and without where Delete: delete data only, 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), the trigger (trigger) index (index), and the stored procedure that depends on the table/ The function will be preserved, but its state will change to: invalid.

(7) The DELETE statement is DML (data maintain Language), which is placed in rollback segment and is not valid until the transaction is committed. If there is a corresponding Tigger, the execution time will be triggered.

(8) Truncate, drop is a DLL (data define language), the operation takes effect immediately, the original data is not placed in the rollback segment, can not be rolled back

(9) In the absence of backup, use the drop and truncate sparingly. To delete some data rows, use Delete and pay attention to where to constrain the extent of the impact. The rollback segment should be large enough. To delete a table with drop, if you want to preserve the table and delete the data in the table, you can do it with truncate if the transaction is irrelevant. If it is related to a transaction, or if the teacher wants to trigger trigger, use Delete.

Truncate table name is fast and efficient because:
TRUNCATE TABLE is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources. The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.

TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.

(12) For a table referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.

First, delete

1, delete is DML, when you perform a delete operation, each time a row is deleted from the table, and the delete operation of the row is recorded in the Redo and undo table spaces for rollback (rollback) and redo operations, but be aware that the tablespace is large enough A manual commit (commit) action is required to take effect, and you can undo the operation by rollback.

2. Delete can delete the data in the table that satisfies the criteria, or delete all records in the table if the WHERE clause is not specified.

3. The DELETE statement does not affect the extent occupied by the table, and the high waterline (higher watermark) keeps the original position unchanged.

Second, truncate

1, Truncate is DDL, will be implicitly committed, so, cannot rollback, does not trigger trigger.

2. Truncate will delete all records in the table and will reset the high watermark and all indexes, freeing the space to minextents extent by default, unless reuse storage is used. Logs are not logged, so the execution is fast, but cannot be undone by rollback (if a table is accidentally truncate off, it can be restored, but cannot be recovered by rollback).

3. For a table referenced by a foreign KEY (ForeignKey) constraint, you cannot use TRUNCATE TABLE, and you should use a DELETE statement without a WHERE clause.

4. Truncatetable cannot be used for tables that participate in an indexed view.

Three, drop

1. Drop is DDL and is implicitly committed, so it cannot be rolled back and triggers will not be triggered.

2. Drop statement deletes the table structure and all data, and frees all the space occupied by the table.

3. The drop statement will delete the constraints that the table's structure relies on, triggers, indexes, stored procedures/functions that depend on the table will be preserved, but become invalid states.

Summarize:

1, in the speed, generally,drop> truncate > delete.

2, in the use of drop and truncate must pay attention, although can be restored, but in order to reduce the trouble, still need to be cautious.

3, if you want to delete some of the data with delete, pay attention to the WHERE clause, the rollback segment is large enough;

If you want to delete the table, of course use drop;

If you want 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.

The difference between drop, truncate, and delete in MySQL