MySQL Delete record delete and Truncate statement introduction

Ysql provides us with delete and TRUNCATE statements to delete data.

Definition of DELETE statement:

Children who often work with databases, delete the data most of the time with the DELETE statement. Now let's take a look at the definition of the DELETE statement.

The code is as follows

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

[WHERE Where_definition]

[Order BY ...]

[LIMIT Row_count]

Here to add that, perhaps someone will ask: "Keoko, you are too lazy, copy the grammar structure of the manual is not copied all." Why don't you write the grammatical structure of multiple table deletions? My answer is "Here is a blog station, can not be exhaustive, can only list the most commonly used grammar, as for some more advanced things, may be introduced later, but now not in this log discussion list", YY completed.

Example of a DELETE statement:

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. According to the syntax structure above, I want to delete all the user_name equals Simaopig records in the Firends table, and I can use the following SQL statement:

The code is as follows

Delete from friends where user_name = ' simaopig ';

Delete considerations:

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.

Simple description of the TRUNCATE statement:

I have not contacted this statement before, nor have I used it before. Because in general, delete data is used by everyone using the DELETE statement. In fact, this truncate command is very simple, it means: Delete all the records of the table. Equivalent to a DELETE statement that does not write a WHERE clause. Its grammatical structure is:

The code is as follows

TRUNCATE [TABLE] Tbl_name

Here is a simple example, I want to delete all the records in the Friends table, you can use the following statement:

The code is as follows

TRUNCATE TABLE friends;

Efficiency issues with TRUNCATE and delete:

If you want to delete all the data for a table, the TRUNCATE statement is faster than the DELETE statement. Because truncate deletes the table and then builds it based on the table structure, delete deletes the record and does not attempt to modify the table. This is why when inserting data into a table that is emptied with a delete, MySQL remembers the AutoIncrement sequence that was generated earlier and continues to use it to number the AutoIncrement field. After truncate deletes the table, the table is numbered from 1 to the AutoIncrement field.

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.

Summarize

Note that the delete here is the DELETE statement without the WHERE clause

Same point

Truncate and delete with no WHERE clause, and drop deletes data in the table

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); Stored procedures/functions that depend on the table 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 the corresponding trigger is present, the execution is 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 will reset the high waterline (back to the beginning).

4. Speed, in general: 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 is related to a transaction, or if you want to trigger trigger, or 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