DELETE 是 DML 语句，用于从表中移除行。DELETE is a DML statement that removes rows from a table.

DELETE 语句可以以 WITH 子句来定义在 DELETE。参见 第 15.2.20 节，“WITH （Common Table Expressions）”。A DELETE statement can start with a WITH clause to define common table expressions accessible within the DELETE. See Section 15.2.20, “WITH (Common Table Expressions)”.

单表语法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]

该DELETE statement deletes rows from tbl_name and returns the number of deleted rows. To check the number of deleted rows, call the ROW_COUNT() function described in Section 14.15, “Information Functions”.The DELETE statement deletes rows from tbl_name and returns the number of deleted rows. To check the number of deleted rows, call the ROW_COUNT() function described in Section 14.15, “Information Functions”.

主要子句Main Clauses

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

where_condition是一个对每一行被删除时求值为真的表达式。它的指定方法如第 15.2.13 节，“SELECT 语句”.where_condition is an expression that evaluates to true for each row to be deleted. It is specified as described in Section 15.2.13, “SELECT Statement”.

如果指定了 ORDER BY 子句，则按指定顺序删除行。LIMIT 子句限制可删除的行数。这些子句适用于单表删除，但不适用于多表删除。If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted. 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. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE子句创建的表空间。You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause.

性能Performance

当你不需要知道被删除的行数时，TRUNCATE TABLE 语句比 DELETE 语句且无 WHERE 子句“更快清空表。与 DELETE不同，TRUNCATE TABLE 不能在事务中使用，或者当你对表有锁时。参见 第 15.1.37 节“TRUNCATE TABLE Statement”，“TRUNCATE TABLE Statement” 和 第 15.3.6 节，“LOCK TABLES 和 UNLOCK TABLES 语句”。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. Unlike DELETE, TRUNCATE TABLE cannot be used within a transaction or if you have a lock on the table. See Section 15.1.37, “TRUNCATE TABLE Statement” and Section 15.3.6, “LOCK TABLES and UNLOCK TABLES Statements”.

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

To ensure that a given DELETE statement does not take too much time, the MySQL-specific LIMIT row_count条款用于DELETE specifies the maximum number of rows to be deleted. 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.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. 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.

子查询Subqueries

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

分区表支持Partitioned Table Support

DELETE 支持使用PARTITION，该子句列出一个或多个分区或子分区（或两个）的逗号分隔名称，从中选择要丢弃的行。未包含在列表中的分区被忽略。给定一个分区表 t，且分区名为 p0，执行语句 DELETE FROM t PARTITION (p0) 对表的影响与执行 ALTER TABLE t TRUNCATE PARTITION (p0);在这两种情况下，分区 p0都会被丢弃。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. 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.

PARTITION可以与WHERE条件一起使用，在这种情况下，条件仅在列出的分区中的行上进行测试。例如，DELETE FROM t PARTITION (p0) WHERE c < 5仅删除分区中的行p0在该条件c < 5为真时；其他分区中的行不会被检查，因此不会受到DELETE.PARTITION can be used along with a WHERE condition, in which case the condition is tested only on rows in the listed partitions. 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.

该PARTITION clause can also be used in multiple-table DELETE statements. You can use up to one such option per table named in the FROM同步调试检查The PARTITION clause can also be used in multiple-table DELETE statements. You can use up to one such option per table named in the FROM option.

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

自动递增列如果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或者InnoDB table. 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以及MyISAM. There are some exceptions to this behavior for InnoDB tables, as discussed in Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.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. 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. There are some exceptions to this behavior for InnoDB tables, as discussed in Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

对于 MyISAM 表，你可以在多列键中指定 AUTO_INCREMENT 次列。在这种情况下，即使是 MyISAM。参见 第 5.6.9 节，“Using AUTO_INCREMENT”。For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. In this case, reuse of values deleted from the top of the sequence occurs even for MyISAM tables. See Section 5.6.9, “Using AUTO_INCREMENT”.

修饰符Modifiers

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

删除Order of Deletion

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

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

ORDER BY 还帮助按必要顺序删除行，以避免引用完整性违规。ORDER BY also helps to delete rows in an order required to avoid referential integrity violations.

InnoDB 表InnoDB Tables

如果你删除了大表中的多行，可能会超过InnoDB。为避免此问题，或仅仅为了缩短表被锁定的时间，以下策略（不使用 DELETE 完全不使用）可能有帮助：If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. 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:

在 RENAME TABLE执行时，重命名操作不会引起并发问题。参见 第 15.1.36 节，“RENAME TABLE Statement”。No other sessions can access the tables involved while RENAME TABLE executes, so the rename operation is not subject to concurrency problems. See Section 15.1.36, “RENAME TABLE Statement”.

MyISAM 表在MyISAM Tables

MyISAM 表中，删除的行维护在链表中，后续 INSERT 操作重用旧行位置。为了回收未使用的空间并减少文件大小，可以使用 OPTIMIZE TABLE句，或 myisamchk 工具来重新组织表格。OPTIMIZE TABLE易用，但 myisamchk 更快。参见 第 15.7.3.4 节，“OPTIMIZE TABLE Statement”，以及 第 6.6.4 节，“myisamchk — MyISAM 表维护工具”。In MyISAM tables, deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier to use, but myisamchk is faster. See Section 15.7.3.4, “OPTIMIZE TABLE Statement”, and Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.

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

DELETE QUICK在删除值导致索引块不足、涵盖多个索引值范围内且又会再次插入时，DELETE QUICK 就没用了。在这种情况下，使用QUICK可能导致索引中浪费空间并未被回收。这里有一个这样的例子：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. In this case, use of QUICK can lead to wasted space in the index that remains unreclaimed. Here is an example of such a scenario:

在这种情况下，与已删除索引值关联的索引块虽然变得不足，但由于使用了QUICK，它们不会与其他索引块合并。当发生新插入时，它们仍然不足，因为新行没有在已删除范围内的索引值。此外，即使你之后使用DELETE而不使用QUICK，这些块仍会保持不足状态，除非某些已删除的索引值恰好位于不足块内或其相邻的索引块中。要在这种情况下回收未使用的索引空间，请使用OPTIMIZE TABLE。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. 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. To reclaim unused index space under these circumstances, use OPTIMIZE TABLE.

如果你要从表中删除大量行，使用DELETE QUICK然后再使用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. This rebuilds the index rather than performing many index block merge operations.

多表删除Multi-Table Deletes

你可以在DELETE语句中指定多个表，以根据WHERE子句中的条件从一个或多个表中删除行。多表DELETE中不能使用ORDER BY或LIMIT。table_references子句列出了参与连接的表，如第 15.2.13.2 节，“JOIN 子句”中所述。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. You cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join, as described in Section 15.2.13.2, “JOIN Clause”.

对于第一种多表语法，只删除FROM子句之前列出的表中匹配的行。对于第二种多表语法，只删除FROM子句（在USING子句之前）中列出的表中匹配的行。效果是你可以同时从许多表中删除行，同时还有其他表仅用于搜索：For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables 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 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;

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

前面的示例使用了INNER JOIN，但多表DELETE语句可以使用SELECT语句允许的其他类型连接，例如LEFT JOIN。例如，要删除在t1中存在但在t2中没有匹配的行，可以使用LEFT JOIN：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. For example, to delete rows that exist in t1 that have no match in t2, use a LEFT JOIN:

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

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

影响。如果您使用涉及DELETE具有外键约束的表的多表InnoDB语句，MySQL 优化器可能会以不同于其父子关系的顺序处理表。在这种情况下，语句会失败并回滚。相反，您应该从单个表中删除，并依赖ON DELETE提供的功能InnoDB来导致其他表相应地被修改。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. 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.

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

时必须使用别名：多表中的表别名 DELETE 应仅在语句的 table_references部分声明中声明。其他地方允许别名引用，但不允许别名声明。Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement. 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;

表别名也支持单表 DELETE 语句。Table aliases are also supported for single-table DELETE statements.