MySQL Basics Tutorial 15 — SQL Syntax Data Operation Statement DML—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]

tbl_name Some rows satisfy the requirements of where_definitionThe given conditions. 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, the value is reused in the BDB table, but will not be used 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 usual way.) Errors that are ignored due to the use of this option are returned as warnings.

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

The QUICK modifier will affect whether the index end nodes are merged during the delete 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 indexed AUTO_INCREMENT columns.

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 plus OPTIMIZE TABLE can speed up the process. Doing so re-indexes rather than doing a large number of index block merge operations.

MySQL's only LIMIT for DELETE row_count option is used to tell the server the maximum number of rows that can 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, 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 the 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 , delete rows from one table or multiple 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 included in the union.

For the first syntax, only the corresponding rows in the table 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 in many tables at the same time and use other tables to search:

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 delete corresponding rows from table t1 and table t2.

以上例子显示了使用逗号操作符的内部联合，但是多表DELETE语句可以使用SELECT语句中允许的所有类型的联合，比如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 ...

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

 以上就是MySQL基础教程15 —— SQL语法之数据操作语句DML——DELETE语法的内容，更多相关内容请关注PHP中文网（www.php.cn）！