mysql——delete syntax

Single table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*] ...]
    FROM table_references
    [WHERE where_definition]

or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]

Some rows in tbl_name satisfy the conditions given by where_definition. DELETE is used to delete these rows and returns the number of deleted records.

If you write a DELETE statement without a WHERE clause, all rows will be deleted. When you don't want to know the number of deleted rows, there is a faster way, which is to use TRUNCATE TABLE.

If the row you delete includes the maximum value for the AUTO_INCREMENT column, that value is reused in the BDB table, but not in the MyISAM table or the InnoDB table. If you use DELETE FROM tbl_name (without a WHERE clause) in AUTOCOMMIT mode to delete all rows in a table, the sequence is reordered for all table types (except InnoDB and MyISAM).

For MyISAM and BDB tables, you can specify the AUTO_INCREMENT secondary column into a multi-column keyword. In this case, the value removed from the top of the sequence is used again, even for MyISAM tables.

The DELETE statement supports the following modifiers:

· If you specify LOW_PRIORITY, the execution of DELETE is delayed until no other client reads this table.

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

· During the process of deleting rows, the IGNORE keyword causes MySQL to ignore all errors. (Errors encountered during the analysis phase are handled in the normal manner.) Errors that are ignored due to the use of this option are returned as warnings.

In MyISAM tables, deleted records are kept in a linked list, and subsequent INSERT operations will reuse the old record location. To reuse unused space and reduce the file size, use the OPTIMIZE TABLE statement or the myisamchk application to reorganize the table. OPTIMIZE TABLE is simpler, but myisamchk is faster.

QUICK modifier will affect whether the index end nodes are merged during the deletion operation. DELETE QUICK is most useful when the index value for the deleted row is replaced by a similar index value from a later inserted row. In this case, the holes left by the deleted values ​​are reused.

If the index block that is not full spans a certain range of index values, a new insertion will occur. DELETE QUICK has no effect when the deleted value results in an underfilled index block. In this case, using QUICK can result in waste 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 insertion produces 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 low end of the column.

In this case, the index blocks related to the deleted index values ​​become underfilled, but due to the use of QUICK, these index blocks will not be merged with other index blocks. When new values ​​are inserted, these index blocks remain underfilled because the new records do not contain index values ​​within the deleted range. In addition, even if you later use DELETE without including QUICK, these index blocks will still be unfilled, unless some of the deleted index values ​​happen to be in or adjacent to these unfilled blocks. In these cases, if you want to reuse unused index space, use OPTIMIZE TABLE.

If you plan to delete many rows from a table, using DELETE QUICK coupled with OPTIMIZE TABLE can speed things up. Doing so re-indexes rather than doing a large number of index block merge operations.

MySQL’s only LIMIT row_count option for DELETE is used to tell the server the maximum number of rows to be deleted before the control command is returned to the client. This option is used to ensure that a DELETE statement does not take up too much time. You can just repeat the DELETE statement until the number of relevant rows is less than the LIMIT value.

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

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp_column
LIMIT 1;

You can specify multiple tables in one DELETE statement, based on multiple Deletes rows from a table or multiple tables based on specific conditions in the table. However, you cannot use ORDER BY or LIMIT in a multi-table DELETE statement.

table_references section lists the tables included in the union.

For the first syntax, only delete the corresponding rows in the table listed before the FROM clause. For the second syntax, only the corresponding rows in the table listed in the FROM clause (before the USING clause) are deleted. What this does is, you can delete rows from many tables at the same time and search using the other tables:

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

or:

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, these statements use all three tables, but only from the table Delete the corresponding rows in t1 and table t2.

The above example shows an inner join using the comma operator, but the multi-table DELETE statement can use all types of joins allowed in the SELECT statement, such as LEFT JOIN.

本语法允许在名称后面加.*，以便与Access相容。

如果您使用的多表DELETE语句包括InnoDB表，并且这些表受外键的限制，则MySQL优化程序会对表进行处理，改变原来的从属关系。在这种情况下，该语句出现错误并返回到前面的步骤。要避免此错误，您应该从单一表中删除，并依靠InnoDB提供的ON DELETE功能，对其它表进行相应的修改。

注释：当引用表名称时，您必须使用别名（如果已给定）：

DELETE t1 FROM test AS t1, test2 WHERE ...

进行多表删除时支持跨数据库删除，但是在此情况下，您在引用表时不能使用别名。举例说明：

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...

目前，您不能从一个表中删除，同时又在子查询中从同一个表中选择。