Detailed parsing of MySQL DELETE syntax

The following articles mainly describe the detailed parsing of MySQL DELETE syntax. First, we start with the example of single table syntax and multi-Table syntax, if you are very interested in the MySQL DELETE syntax, you can read the following articles to better understand it.

Single Table Syntax:
Copy codeThe Code is as follows:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_definition]
[Order by...]
[LIMIT row_count]

Multi-Table Syntax:
Copy codeThe Code is as follows:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
Tbl_name [. *] [, tbl_name [. *]...]
FROM table_references
[WHERE where_definition]

Or:

Copy codeThe Code is as follows:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name [. *] [, tbl_name [. *]...]
USING table_references
[WHERE where_definition]

Some rows in tbl_name meet the conditions specified by where_definition. MySQL DELETE is used to DELETE these rows and return the number of deleted records.

If the DELETE statement you have written does not contain the WHERE clause, all rows are deleted. If you do not want to know the number of rows to be deleted, you can use the truncate table method faster.

If the row you delete contains the maximum value used for the AUTO_INCREMENT column, the value is reused in the BDB table, but not in the MyISAM table or InnoDB table. If you use delete from tbl_name (excluding the WHERE clause) in AUTOCOMMIT mode to DELETE all rows in a table, the sequence is re-organized for all table types (except InnoDB and MyISAM. For InnoDB tables, this operation has some exceptions.

For MyISAM and BDB tables, you can specify the AUTO_INCREMENT subcolumn to a multi-column keyword. In this case, the deleted value from the top of the sequence is used again, even for the MyISAM table. The DELETE Statement supports the following modifiers:

If LOW_PRIORITY is specified, the execution of the DELETE statement is delayed until no other client reads the table.

For MyISAM tables, if you use the QUICK keyword, the storage engine does not merge the index end nodes during the deletion process, which can speed up the deletion of some types.

The IGNORE keyword causes MySQL to IGNORE all errors during row deletion. (Errors encountered during the analysis phase will be handled in a regular manner .) Errors ignored due to the use of this option will be returned as a warning.

The speed of the DELETE operation may be affected by some factors. These factors are discussed in section 7.2.18, "MySQL DELETE statement speed.

In the MyISAM table, deleted records are retained in a linked list, and subsequent INSERT operations will 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 re-orchestrate the TABLE. Optimize table is easier, but myisamchk is faster. See section 13.5.2.5, "optimize table Syntax" and Chapter 7th: optimization.

The QUICK modifier affects whether the index end nodes are merged during the delete operation. When the index value of the row to be deleted is replaced by a similar index value of the row inserted later, delete quick is the most suitable. In this case, the holes left by the Deleted Values are reused.

If the index value of an unfilled index block spans a certain range, a new insert will occur again. Delete quick does not work when the deleted Value causes unfilled index blocks. In this case, using QUICK may lead to obsolete space in unused indexes. The following is an example of this situation:

1. Create a table that contains the indexed AUTO_INCREMENT column.

2. Insert many records into the table. Each insert operation generates an index value, which is added to the high-end of the index.

3. Use delete quick to DELETE a group of records from the lower end of the column.

In this case, the index blocks related to the deleted index values are not full. However, due to the use of QUICK, these index blocks are not merged with other index blocks. When new values are inserted, these index blocks are still not full because the new records do not include the index values in the deleted range. In addition, even if you use MySQL DELETE later without QUICK, these index blocks are still not full, unless some of the deleted index values happen to be in these unfilled blocks, or adjacent to these blocks. In these cases, you need to use optimize table to reuse unused index space.

If you want to DELETE many rows from a TABLE, use delete quick with optimize table to speed up. In this way, indexes can be re-created, rather than a large number of index block merge operations.

The unique LIMIT row_count option of MySQL for DELETE is used to notify the server of the maximum value of the row deleted before the control command is returned to the client. This option ensures that a DELETE statement does not take too much time. You can repeat the DELETE statement until the number of related rows is less than the LIMIT value.

If the DELETE statement contains an order by clause, all rows are deleted in the ORDER specified in the clause. This clause takes effect only when used with LIMIT. For example, the following clause is used to find the row corresponding to the WHERE clause, use timestamp_column for classification, and delete the first (oldest) row:

Copy codeThe Code is as follows:
Delete from somelog
WHERE user = 'jcol'
Order by timestamp_column
LIMIT 1;

You can specify multiple tables in a DELETE statement and DELETE rows from one or more tables based on specific conditions in multiple tables. However, you cannot use order by or LIMIT in a multi-table DELETE statement.

The table_references section lists the tables contained 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 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. You can delete rows from multiple tables at the same time and search for other tables:
Copy codeThe Code is as follows:
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;

Or:
Copy codeThe Code is as follows:
Delete from t1, t2 USING t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;

When you search for rows to be deleted, these statements use all three tables, but only delete the corresponding rows from Table t1 and table t2.

The preceding example shows the internal Union using the comma operator, but the multi-Table MySQL DELETE statement can use all types of Union allowed in the SELECT statement, such as left join.

This syntax allows you to add. * after the name to make it compatible with Access.

If you use multiple MySQL DELETE statements including InnoDB tables, and these tables are restricted by foreign keys, the MySQL optimizer processes the tables and changes the original subordination. In this case, the statement is incorrect and returned to the previous step. To avoid this error, you should DELETE it from a single table and modify other tables based ON the on delete function provided by InnoDB.

Note: When referencing a table name, you must use an alias (if given ):
Copy codeThe Code is as follows:
DELETE t1 FROM test AS t1, test2 WHERE...

You can delete multiple tables across databases. However, you cannot use aliases when referencing tables. Example:
Copy codeThe Code is as follows:
DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE...

Currently, You cannot delete a table or select from the same table in the subquery.