Mysql Delete Data Delete and Truncate statement use comparison _mysql

Source: Internet
Author: User
Tags mysql delete
The contents of an empty MYSQLL table are common in two ways: a delete and a truncate. Delete statements without a where parameter can delete all content in the MySQL table, and you can also empty the MySQL table with TRUNCATE table. Efficiency truncate faster than delete, but truncate deleted after the MySQL log does not record, can not recover data.

Its grammatical structure is:
Copy Code code as follows:

TRUNCATE [TABLE] Tbl_name

Here's a simple example,

I want to delete all the records in the Friends table, you can use the following statement:
Copy Code code as follows:

TRUNCATE TABLE friends;


The effect of the delete is a bit like deleting all the records in the MySQL table to the end, and truncate is equivalent to preserving the MySQL table structure, recreating the table, and all States are equivalent to the new table.

Grammar:

The definition of the DELETE statement.
Copy Code code as follows:

DELETE [low_priority] [QUICK] [IGNORE] from Tbl_name

[WHERE Where_definition]

[Order BY ...]

[LIMIT Row_count]


MySQL's syntax is similar to spoken language, you have to point out which table you want to delete data, and delete which data, this is enough. Just like when writing a narrative, time, place, character, environment and plot elements are essential.
The example is the most image and the most descriptive question. So, not the whole useless, directly into the subject. Following the syntax structure above, I want to delete all the user_name equals Simaopig records in the Firends table,
You can use the following SQL statement:

Copy Code code as follows:

Delete from friends where user_name = ' Simaopig ';d elete


Precautions:
From the syntax structure, we can see that, like the update syntax, we can omit the WHERE clause. But this is a very dangerous behavior. Because if you do not specify a WHERE clause, delete deletes all the records in the table and deletes them immediately, even if you have no place to cry, and no time, because you need to immediately acknowledge the error with the supervisor, and immediately find the MySQL log and roll back the record. But once you have had such an experience, I believe it must be impressive.

The strange thing is that I am on the InnoDB and MyISAM on the large amount of data, the index file under great circumstances, it is almost impossible to delete the success! Left to settle.

Summary

However, the truncate command is fast, but it is not as secure as the delete command for transaction processing. Therefore, if we want to perform a transaction on a table that truncate deletes, this command generates an exit and generates an error message.
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.