MySQL Delete and truncate statements

If the wicked have their own wicked, if there is a problem with the data in the database, or someone is in trouble, or your old man is unhappy with this data, in addition, when you want to destroy evidence (in fact, there will always be traces), you need to understand the delete Statement of MySQL.MySQL provides the delete and truncate statements to delete data.

Definition of the delete statement:

Most of the children who often deal with databases use Delete statements to delete data. Now let's take a look at the definition of the delete statement.

Delete [low_priority] [quick] [ignore] From tbl_name

[Where where_definition]

[Order by...]

[Limit row_count]

Here is a further explanation. Someone may ask: "You are too lazy to copy the grammar structure above the manual. Why not write the syntax structure for deleting multiple tables? "My answer is" this is a blog website. It cannot cover all aspects. It can only list the most commonly used syntaxes. For more advanced things, we may introduce them later, but now it is not discussed in this log ", YY is complete.

Example of a delete statement:

These syntaxes of MySQL are similar to those of the spoken language. You need to specify the table from which you want to delete data and what data to delete.This is enough. Just like when writing a narrative, time, place, character, environment, and plot are essential.

The example is the most vivid and can best illustrate the problem. Therefore, it is useless to go to the topic directly. According to the preceding syntax structure, to delete all records whose user_name is simaopig in the firends table, you can use the following SQL statement:

Delete from friends where user_name = 'simaopig ';

Delete considerations:

From the syntax structure, we can see that, like the update syntax, we canWhere clause is omitted. However, this is a very dangerous behavior. If the WHERE clause is not specified, delete deletes all records in the table and deletes them immediately., Even if you want to cry, there is no place, there is no time, because you need to immediately admit the error with the supervisor, and immediately find the MySQL Log, roll back and forth records. However, once you have had such an experience, I believe it must be impressive.

A brief description of the truncate statement:

I have never touched or used this statement before. Generally, delete statements are used to delete data. Actually, thisThe truncate command is simple. It means to delete all records in the table. It is equivalent to a delete statement that does not write a where clause.Its syntax structure is:

Truncate [Table] tbl_name

Here is a simple example. to delete all records in the friends table, you can use the following statement:

Truncate table friends;

Efficiency of truncate and delete:

If you want to delete all the data in the table, the truncate statement is faster than the delete statement.. Because truncate deletes the table and creates it again based on the table structure, delete deletes records and does not try to modify the table. This is also why MySQL will remember the previously generated autoincrement sequence when inserting data to a table cleared by using Delete, and continue to use it to number the autoincrement field. After truncate deletes a table, the table starts from 1 and is the autoincrement field number.

However, the truncate command is fast and fast, but it is not as secure as the DELETE command for transaction processing. Therefore, if the table to be deleted by truncate is being processed in a transaction, this command will generate an exit and generate an error message.

Summary and nagging:

There is a saying in my hometown that "the chicks do not urinate, and each has its own way." Whether you are using the delete statement or the truncate statement, after understanding their features, this is not a very difficult task.