Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "what is the difference between drop, truncate and delete in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Drop: delete the database

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

Drop > truncate > delete

Method 1: if the database you want to delete exists, the deletion is successful. If it does not exist, an error is reported.

DROP DATABASE mytest1

Method 2: recommend. If the database you want to delete exists, the deletion is successful. If it does not exist, it ends silently and will not report an error.

DROP DATABASE IF EXISTS mytest1; 2. Compare TRUNCATE TABLE and DELETE FROM

Similarities: it is possible to delete all data in the table while preserving the table structure.

Differences:

TRUNCATE TABLE: once you do this, all the table data is cleared. At the same time, the data cannot be rolled back.

DELETE FROM: once you do this, all the table data can be cleared (without WHERE). At the same time, data can be rolled back.

3. Description of DDL and DML

Once the operation of ① DDL (CREATE, DROP, ALTER, TRUNCATE, etc.) is performed, it cannot be rolled back. The instruction SET autocommit = FALSE is invalid for DDL operations. (because COMMIT must be executed once after the DDL operation is performed. This COMMIT operation is not affected by SETautocommit = FALSE.)

The operation of ② DML (INSERT, DELETE, UPDATE, SELECT, and so on) is by default and cannot be rolled back once performed. However, if SET autocommit = FALSE is executed before DML is executed, the DML operation performed can be rolled back.

Demonstrate that after 1:DELETE FROM deletes the data, restore the data

SET autocommit = FALSE

DELETE FROM myemp3

The data is empty after query

Roll back data

Query again and you can see the recovered data.

Demo 2:TRUNCATE TABLE, data operations cannot be rolled back

SET autocommit = FALSE

TRUNCATE TABLE myemp3

Emptied the data

Roll back things

Rollback

The data was not recovered after the query.

4. Efficiency comparison

In terms of speed, generally speaking, drop > truncate > delete.

If you want to delete part of the data, use delete, pay attention to the where clause, and the rollback segment should be large enough; if you want to delete the table, of course use drop

If you want to keep the table and delete all data, if it has nothing to do with the transaction (cannot be rolled back), you can use truncate.

If it is related to a transaction, or if you want to trigger trigger, use delete.

If you are defragmenting the table, you can use truncate to keep up with reuse stroage, and then re-import / insert the data.

Truncate and drop are DDL statements that cannot be rolled back after execution; delete is a DML statement that can be rolled back.

Truncate can only work on tables; delete,drop can act on tables, views, and so on.

Truncate clears all rows in the table, but the table structure and its constraints, indexes, etc., remain the same; drop deletes the table structure and its dependent constraints, indexes, and so on.

Truncate resets the self-increment of the table; delete does not.

Truncate does not activate table-related delete triggers; delete can.

After 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, and the drop statement frees up all the space occupied by the table.

This is the end of the content of "what is the difference between drop, truncate and delete in mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.