13.2.2 DELETE Statement声明

DELETE is a DML statement that removes rows from a table.DELETE是从表中删除行的DML语句。

A DELETE statement can start with a WITH clause to define common table expressions accessible within the DELETE. DELETE语句可以以WITH子句开始，定义DELETE语句中可访问的公共表表达式。See Section 13.2.15, “WITH (Common Table Expressions)”.请参阅第13.2.15节，“WITH（公共表表达式）”。

Single-Table Syntax单表语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

The DELETE statement deletes rows from tbl_name and returns the number of deleted rows. DELETE语句从tbl_name中删除行并返回删除的行数。To check the number of deleted rows, call the ROW_COUNT() function described in Section 12.16, “Information Functions”.要检查删除的行数，请调用第12.16节，“信息函数”中描述的ROW_COUNT()函数。

Main Clauses主要子句

The conditions in the optional WHERE clause identify which rows to delete. With no WHERE clause, all rows are deleted.可选WHERE子句中的条件标识要删除的行。如果没有WHERE子句，则删除所有行。

where_condition is an expression that evaluates to true for each row to be deleted. where_condition是一个表达式，对于要删除的每一行，该表达式的计算结果都为true。It is specified as described in Section 13.2.10, “SELECT Statement”.如第13.2.10节，“SELECT语句”所述。

If the ORDER BY clause is specified, the rows are deleted in the order that is specified. 如果指定了ORDER BY子句，则按指定的顺序删除行。The LIMIT clause places a limit on the number of rows that can be deleted. LIMIT子句限制可以删除的行数。These clauses apply to single-table deletes, but not multi-table deletes.这些子句适用于单表删除，但不适用于多表删除。

Multiple-Table Syntax多表语法

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

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

Privileges权限

You need the DELETE privilege on a table to delete rows from it. 您需要表的DELETE权限才能从中删除行。You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause.对于任何只读取的列（如WHERE子句中指定的列），您只需要SELECT权限。

Performance性能

When you do not need to know the number of deleted rows, the TRUNCATE TABLE statement is a faster way to empty a table than a DELETE statement with no WHERE clause. 当您不需要知道已删除的行数时，TRUNCATE TABLE语句比不带WHERE子句的DELETE语句更快地清空表。Unlike DELETE, TRUNCATE TABLE cannot be used within a transaction or if you have a lock on the table. 与DELETE不同，TRUNCATE TABLE不能在事务中使用，也不能在表上有锁的情况下使用。See Section 13.1.37, “TRUNCATE TABLE Statement” and Section 13.3.6, “LOCK TABLES and UNLOCK TABLES Statements”.参见第13.1.37节，“TRUNCATE TABLE语句”和第13.3.6节，“LOCK TABLES和UNLOCK TABLES语句”。

The speed of delete operations may also be affected by factors discussed in Section 8.2.5.3, “Optimizing DELETE Statements”.删除操作的速度也可能受到第8.2.5.3节，“优化删除语句”中讨论的因素的影响。

To ensure that a given DELETE statement does not take too much time, the MySQL-specific LIMIT row_count clause for DELETE specifies the maximum number of rows to be deleted. 为了确保给定的DELETE语句不会花费太多时间，DELETE的MySQL专用的LIMIT row_count子句指定要删除的最大行数。If the number of rows to delete is larger than the limit, repeat the DELETE statement until the number of affected rows is less than the LIMIT value.如果要删除的行数大于限制，请重复DELETE语句，直到受影响的行数小于LIMIT值。

Subqueries子查询

You cannot delete from a table and select from the same table in a subquery.不能从表中删除，也不能从子查询中的同一表中选择。

Partitioned Table Support分区表支持

DELETE supports explicit partition selection using the PARTITION clause, which takes a list of the comma-separated names of one or more partitions or subpartitions (or both) from which to select rows to be dropped. DELETE支持使用PARTITION子句进行显式分区选择，该子句获取一个或多个分区或子分区（或两者）的逗号分隔名称列表，从中选择要删除的行。Partitions not included in the list are ignored. 不包括在列表中的分区将被忽略。Given a partitioned table t with a partition named p0, executing the statement DELETE FROM t PARTITION (p0) has the same effect on the table as executing ALTER TABLE t TRUNCATE PARTITION (p0); in both cases, all rows in partition p0 are dropped.给定一个分区为p0的分区表t，执行DELETE FROM t partition (p0)语句与执行ALTER table t TRUNCATE partition (p0)语句对表的影响相同；在这两种情况下，都会删除分区p0中的所有行。

PARTITION can be used along with a WHERE condition, in which case the condition is tested only on rows in the listed partitions. PARTITION可以与WHERE条件一起使用，在这种情况下，只对列出的分区中的行测试条件。For example, DELETE FROM t PARTITION (p0) WHERE c < 5 deletes rows only from partition p0 for which the condition c < 5 is true; rows in any other partitions are not checked and thus not affected by the DELETE.例如，DELETE FROM t PARTITION (p0) WHERE c < 5只从分区p0中删除行，并要满足条件c < 5，不检查任何其他分区中的行，因此不受DELETE的影响。

The PARTITION clause can also be used in multiple-table DELETE statements. PARTITION子句也可以用在多表DELETE语句中。You can use up to one such option per table named in the FROM option.对于在FROM选项中命名的每个表，最多可以使用一个这样的选项。

For more information and examples, see Section 24.5, “Partition Selection”.有关更多信息和示例，请参阅第24.5节“分区选择”。

Auto-Increment Columns自动递增列

If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB table. 如果删除包含AUTO_INCREMENT列的最大值的行，则MyISAM或InnoDB表不会重用该值。If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in autocommit mode, the sequence starts over for all storage engines except InnoDB and MyISAM. 如果你在autocommit模式下使用DELETE FROM tbl_name删除了表中的所有行（不用WHERE子句），对于除了InnoDB和MyISAM之外的所有存储引擎，序列会重新开始。There are some exceptions to this behavior for InnoDB tables, as discussed in Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.InnoDB表的这种行为有一些例外，如第15.6.1.6节，“InnoDB中的自动增量处理”所述。

For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. 对于MyISAM表，可以在多列键中指定AUTO_INCREMENT的第二列。In this case, reuse of values deleted from the top of the sequence occurs even for MyISAM tables. 在这种情况下，即使对于MyISAM表，也会重用从序列顶部删除的值。See Section 3.6.9, “Using AUTO_INCREMENT”.请参阅第3.6.9节，“使用自动增量”。

Modifiers修饰符

The DELETE statement supports the following modifiers:DELETE语句支持以下修饰符：

Order of Deletion删除顺序

If the DELETE statement includes an ORDER BY clause, rows are deleted in the order specified by the clause. 如果DELETE语句包含ORDER BY子句，则按该子句指定的顺序删除行。This is useful primarily in conjunction with LIMIT. 这主要与LIMIT一起使用。For example, the following statement finds rows matching the WHERE clause, sorts them by timestamp_column, and deletes the first (oldest) one:例如，以下语句查找与WHERE子句匹配的行，按timestamp_column对它们进行排序，并删除第一个（最早的）行：

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

ORDER BY also helps to delete rows in an order required to avoid referential integrity violations.ORDER BY还有助于按避免引用完整性冲突所需的顺序删除行。

InnoDB Tables表

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. 如果要从一个大表中删除许多行，则可能会超过InnoDB表的锁表大小。To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:为了避免此问题，或者简单地减少表保持锁定的时间，以下策略（完全不使用DELETE）可能会有所帮助：

  1. Select the rows not to be deleted into an empty table that has the same structure as the original table:选择不删除到与原始表具有相同结构的空表中的行：

    INSERT INTO t_copy SELECT * FROM t WHERE ... ;

  2. Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:使用RENAME TABLE以原子方式将原始表移开，并将副本重命名为原始名称：

    RENAME TABLE t TO t_old, t_copy TO t;

  3. Drop the original table:删除原始表：

    DROP TABLE t_old;

No other sessions can access the tables involved while RENAME TABLE executes, so the rename operation is not subject to concurrency problems. 当RENAME TABLE执行时，没有其他会话可以访问所涉及的表，因此重命名操作不受并发问题的影响。See Section 13.1.36, “RENAME TABLE Statement”.请参阅第13.1.36节，“RENAME TABLE语句”。

MyISAM Tables表

In MyISAM tables, deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. 在MyISAM表中，删除的行保存在链表中，随后的INSERT操作重用旧的行位置。To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. 要回收未使用的空间并减少文件大小，请使用OPTIMIZE TABLE语句或myisamchk实用程序重新组织表。OPTIMIZE TABLE is easier to use, but myisamchk is faster. OPTIMIZE TABLE更容易使用，但myisamchk更快。See Section 13.7.3.4, “OPTIMIZE TABLE Statement”, and Section 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.请参阅第13.7.3.4节，“优化表语句”和第4.6.4节，“myisamchk-MyISAM表维护实用程序”。

The QUICK modifier affects whether index leaves are merged for delete operations. QUICK修饰符会影响是否为删除操作合并索引叶。DELETE QUICK is most useful for applications where index values for deleted rows are replaced by similar index values from rows inserted later. 对于那些删除行的索引值被稍后插入行的类似索引值替换的应用程序，DELETE QUICK最有用。In this case, the holes left by deleted values are reused.在这种情况下，将重用删除的值留下的孔。

DELETE QUICK is not useful when deleted values lead to underfilled index blocks spanning a range of index values for which new inserts occur again. 当删除的值导致跨一系列索引值的索引块填充不足，而新的插入又会出现时，DELETE QUICK将不起作用。In this case, use of QUICK can lead to wasted space in the index that remains unreclaimed. 在这种情况下，使用QUICK可能会导致索引中未声明的空间被浪费。Here is an example of such a scenario:下面是这样一个场景的示例：

  1. Create a table that contains an indexed AUTO_INCREMENT column.创建一个包含索引AUTO_INCREMENT列的表。

  2. Insert many rows into the table. Each insert results in an index value that is added to the high end of the index.在表中插入许多行。每次插入都会产生一个索引值，该值将添加到索引的高端。

  3. Delete a block of rows at the low end of the column range using DELETE QUICK.使用Delete QUICK删除列范围低端的行块。

In this scenario, the index blocks associated with the deleted index values become underfilled but are not merged with other index blocks due to the use of QUICK. 在这种情况下，与已删除的索引值关联的索引块将变得填充不足，但由于使用了QUICK，因此不会与其他索引块合并。They remain underfilled when new inserts occur, because new rows do not have index values in the deleted range. 由于新行的索引值不在已删除的范围内，因此在发生新插入时，它们仍处于未填满状态。Furthermore, they remain underfilled even if you later use DELETE without QUICK, unless some of the deleted index values happen to lie in index blocks within or adjacent to the underfilled blocks. 此外，即使以后不使用QUICK而使用DELETE，它们也会保持未填满状态，除非某些已删除的索引值恰好位于未填满的块内或附近的索引块中。To reclaim unused index space under these circumstances, use OPTIMIZE TABLE.要在这些情况下回收未使用的索引空间，请使用OPTIMIZE TABLE。

If you are going to delete many rows from a table, it might be faster to use DELETE QUICK followed by OPTIMIZE TABLE. 如果要从表中删除许多行，那么使用delete QUICK和OPTIMIZE table可能会更快。This rebuilds the index rather than performing many index block merge operations.这将重建索引，而不是执行许多索引块合并操作。

Multi-Table Deletes多表删除

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the condition in the WHERE clause. 可以在DELETE语句中指定多个表，以根据WHERE子句中的条件从一个或多个表中删除行。You cannot use ORDER BY or LIMIT in a multiple-table DELETE. 不能在多表DELETE中使用ORDER BY或LIMIT。The table_references clause lists the tables involved in the join, as described in Section 13.2.10.2, “JOIN Clause”.table_references子句列出了联接中涉及的表，如第13.2.10.2节，“联接子句”所述。

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. 对于第一种多表语法，只删除FROM子句之前列出的表中的匹配行。For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. 对于第二种多表语法，只删除FROM子句（在USING子句之前）中列出的表中的匹配行。The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:这样做的效果是，您可以同时从多个表中删除行，并具有仅用于搜索的其他表：

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

Or:或：

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

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.这些语句在搜索要删除的行时使用所有三个表，但仅从表t1和t2中删除匹配的行。

The preceding examples use INNER JOIN, but multiple-table DELETE statements can use other types of join permitted in SELECT statements, such as LEFT JOIN. 前面的示例使用内部联接，但是多多DELETE语句可以使用SELECT语句中允许的其他联接类型，例如LEFT JOIN。For example, to delete rows that exist in t1 that have no match in t2, use a LEFT JOIN:例如，要删除t1中存在的与t2中不匹配的行，请使用LEFT JOIN：

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

The syntax permits .* after each tbl_name for compatibility with Access.语法允许在每个tbl_name后面加.*以与Access兼容。

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. 如果您使用一个涉及InnoDB表的多表DELETE语句，其中有外键约束，MySQL优化器可能会按照不同于表的父/子关系的顺序处理表。In this case, the statement fails and rolls back. 在这种情况下，语句失败并回滚。Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.相反，您应该从单个表中删除，并依赖InnoDB提供的ON DELETE功能来相应地修改其他表。

Note注意

If you declare an alias for a table, you must use the alias when referring to the table:如果为表声明别名，则在引用表时必须使用别名：

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

Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement. 多表DELETE中的表别名应仅在语句的table_references部分声明。Elsewhere, alias references are permitted but not alias declarations.在其他地方，允许使用别名引用，但不允许使用别名声明。

Correct:对的：

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

Incorrect:不正确：

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;

DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

Table aliases are also supported for single-table DELETE statements beginning with MySQL 8.0.16. 从MySQL 8.0.16开始的单表DELETE语句也支持表别名。(Bug #89410,Bug #27455809)