A detailed explanation of the difference between drop,truncate and delete in MySQL realize MySQL from scratch _mysql

Source: Internet
Author: User
Tags rollback
Different points:
1. Truncate and delete Delete only the data do not delete the structure of the table (definition)
The DROP statement deletes the table's structure-dependent constraints (constrain), triggers (trigger), indexes (index); That depends on the table.
Stored procedures/functions are preserved, but become invalid states.

The 2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and if there is a corresponding trigger,
Execution will be triggered.
Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment and cannot be rolled back. Operation does not trigger
Trigger.

The 3.delete statement does not affect the extent that the table occupies, and the high watermark (watermark) keeps the original position.
Obviously the drop statement frees up all the space that the table occupies
Truncate statement by default see space released to minextents extent unless the reuse storage is used; Truncate
The high watermark will be reset (back to the beginning).

4. Speed, generally: drop> truncate > Delete

5. Security: Be careful with drop and truncate, especially when there is no backup. Otherwise, it's too late to cry.
Use, to delete some data rows with delete, note the WHERE clause is taken. The rollback segment should be large enough.
Want to delete the table, of course with drop
You want to keep the table and delete all the data. If it has nothing to do with the transaction, use truncate. If it's related to the transaction, or if you want to trigger trigger,
is with the delete.
If you are defragmenting the inside of the 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; The auto_increatement modulation starts at 0, which is actually starting at 1.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.