MySQL Delete Syntax uses detailed parsing _mysql

The following article mainly describes the MySQL delete syntax of the detailed parsing, first of all, we are from the single table syntax and the example of multiple table syntax, if you are interested in the MySQL delete syntax related content, you can browse the following article to have a better understanding.

Single table Syntax:

Copy Code code as follows:

DELETE [low_priority] [QUICK] [IGNORE] from Tbl_name
[WHERE Where_definition]
[Order BY ...]
[LIMIT Row_count]

Multiple table syntax:

Copy Code code as follows:

DELETE [low_priority] [QUICK] [IGNORE]
TBL_NAME[.*] [, tbl_name[.*] ...]
From Table_references
[WHERE Where_definition]

Or:

Copy Code code as follows:

DELETE [low_priority] [QUICK] [IGNORE]
From tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[WHERE Where_definition]

Some rows in the Tbl_name meet the conditions given by where_definition. MySQL Delete Deletes these rows and returns the number of records that were deleted.

If you write a DELETE statement that does not have a WHERE clause, all rows are deleted. When you don't want to know the number of rows being deleted, there is a quicker way to use the truncate TABLE.

If you delete a row that contains the maximum value for the Auto_increment column, the value is used again in the BDB table, but it is not used for MyISAM tables or InnoDB tables. If you delete all rows in a table using the delete from Tbl_name (without a WHERE clause) in autocommit mode, the sequence is rearranged for all table types (except for InnoDB and MyISAM). For InnoDB tables, there are some exceptions to this operation.

For MyISAM and BDB tables, you can assign auto_increment subordinate columns to a multiple-column keyword. In this case, the value removed from the top of the sequence is reused, even for MyISAM tables. The DELETE statement supports the following modifiers:

If you specify low_priority, the execution of delete is deferred until no other client reads this table.

For MyISAM tables, if you use quick keywords, the storage engine will not merge index-end nodes during the deletion process, which can speed up some kinds of deletions.

In the process of deleting a row, the Ignore keyword will cause MySQL to ignore all errors. (Errors encountered during the analysis phase are handled in a regular manner.) Errors that are ignored due to the use of this option are returned as warnings.

The speed of the delete operation is affected by a number of factors, which are discussed in section 7.2.18, "Speed of the MySQL DELETE statement."

In the MyISAM table, the deleted record is kept in a linked list, and subsequent inserts reuse the old record location. To reuse unused space and reduce the size of the file, use the Optimize table statement or the MYISAMCHK application to rearrange the tables. OPTIMIZE table is simpler, but myisamchk faster. See section 13.5.2.5, "OPTIMIZE table Syntax" and chapter 7th: Optimization.

The quick modifier affects whether the index end node is merged in the delete operation. Delete Quick is most useful when the index value for the deleted row is replaced by a similar index value from the row that was inserted later. In this case, the void left by the deleted value is reused.

A new insertion occurs again when an index block that is not full spans a range of index values. Delete Quick does not work when the deleted value causes the not-filled index block to appear. In this case, using quick will result in discarded space in the unused index. The following are examples of such cases:

1. Create a table that contains the indexed auto_increment columns.

2. Insert a lot of records in a table. Each insertion produces an index value that is added to the high end of the index.

3. Use Delete quick to delete a set of records from the lower end of the column.

In this case, the index blocks associated with the deleted index values become not filled, but, because quick is used, they are not merged with other index blocks. When new values are inserted, the blocks are still not filled, because the new records do not contain index values that are in the range being deleted. Also, even if you later use the MySQL delete without quick, the index blocks are still not filled, unless a portion of the index value that is deleted happens to be in or adjacent to the pieces that are not filled. In these cases, if you want to reuse unused index space, you use optimize TABLE.

If you plan to remove many rows from a table, use delete quick plus optimize table to speed up. Doing so allows you to re-establish the index, rather than doing a large number of block merge operations.

The MySQL unique limit row_count option for delete tells the server the maximum value of the row that was deleted before the control command was returned to the client. This option is used to ensure that a DELETE statement does not consume too much time. You can repeat the DELETE statement only until the number of related rows is less than the limit value.

If the DELETE statement includes an ORDER BY clause, the rows are deleted in the sequence specified in the clause. This clause works only if it is associated with limit. For example, the following clause is used to find the row corresponding to the WHERE clause, to classify using timestamp_column, and to delete the first (oldest) row:

Copy Code code as follows:

DELETE from Somelog
WHERE user = ' Jcole '
ORDER BY Timestamp_column
LIMIT 1;

You can specify multiple tables in a DELETE statement to delete rows from one table or multiple tables based on specific criteria in multiple tables. However, you cannot use order by or limit in a multiple table DELETE statement.

The table_references section lists the tables that are included in the Union. This syntax is described in section 13.2.7.1, "join syntax."

For the first syntax, only the corresponding rows in the table that are listed before the FROM clause are deleted. For the second syntax, only the corresponding rows in the table listed in the FROM clause (before the using clause) are deleted. The effect is that you can delete rows from many tables at the same time and use a different table for searching:

Copy Code code as follows:

DELETE T1, t2 from T1, T2, T3 WHERE t1.id=t2.id and t2.id=t3.id;

Or:

Copy Code code as follows:

DELETE from T1, T2 USING T1, t2, T3 WHERE t1.id=t2.id and t2.id=t3.id;

When searching for rows to be deleted, the statements use all three tables, but only the corresponding rows are deleted from the table T1 and table T2.

The above example shows the inner union using the comma operator, but the multiple table MySQL DELETE statement can use all the types of unions allowed in the SELECT statement, such as a left JOIN.

This syntax allows you to add. * After the name to be compatible with access.

If you use a multiple table MySQL DELETE statement that includes a InnoDB table, and those tables are restricted by foreign keys, the MySQL optimizer processes the table to change the original dependencies. In this case, the statement appears with an error and returns to the previous step. To avoid this error, you should remove from a single table and rely on the on Delete feature provided by InnoDB to modify the other tables accordingly.

Note: When referencing a table name, you must use an alias (if given):

Copy Code code as follows:

DELETE t1 from test as T1, test2 where ...

Cross-database deletions are supported when multiple table deletions are made, but in this case, you cannot use aliases when referencing tables. An example is provided:

Copy Code code as follows:

DELETE test1.tmp1, test2.tmp2 from Test1.tmp1, TEST2.TMP2 where ...

Currently, you cannot delete from a table and select from the same table in a subquery.