MySQL Delete record Delete and Truncate statements

YSQL 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.

The Code is as follows:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_definition]

[Order by...]

[LIMIT row_count]

Some people may have to 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. You can only list the most commonly used syntaxes. For more advanced things, we may introduce them later, however, this log is not discussed here. "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:

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. 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 time, because you need to immediately acknowledge the error with the supervisor, and immediately find the MySQL Log, roll back the record. 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. In fact, this truncate command is very 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:

The Code is as follows:

TRUNCATE [TABLE] tbl_name

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

The Code is as follows:

Truncate table friends;

Efficiency of truncate and delete:

To delete all data in a 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

Note: The delete statement is a delete statement without the where clause.

Similarities

Truncate, delete without where clause, and drop both delete table data.

Differences:

1. truncate and delete: delete only data. Do not delete the table structure (Definition)

The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but it changes to the invalid status.

2. The delete statement is dml, which is put into the rollback segement and takes effect only after the transaction is committed. If a trigger exists, it is triggered during execution.

Truncate, drop is ddl, and the operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. trigger is not triggered.

3. The delete statement does not affect the extent used by the table, and the high watermark keeps the original position unchanged.

Obviously, the drop statement releases all the space occupied by the table.

The truncate statement is released to the minextents extent by default, unless reuse storage is used; The truncate will reset the high water line (back to the beginning ).

4. Speed, in general: drop>; truncate>; delete

5. Security: Be careful when using drop and truncate, especially when there is no backup. Otherwise, it will be too late to cry.

To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.

To delete a table, use drop

Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use delete.

If you want to organize fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data.