MySQL 8.0-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. See Section 13.2.15, “WITH (Common Table Expressions)”.

DELETE语句可以以with子句开始，定义在DELETE中可访问的公共表表达式。参见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. To check the number of deleted rows, call the ROW_COUNT() function described in Section 12.16, “Information Functions”.

DELETE语句从tbl_name中删除行，并返回删除的行数。要检查已删除的行数，请调用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. It is specified as described in Section 13.2.10, “SELECT Statement”.

Where_condition是一个表达式，对于要删除的每一行计算结果都为true。如13.2.10节“SELECT语句”中所述。

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.

如果指定了ORDER BY子句，则按指定的顺序删除行。LIMIT子句限制可以删除的行数。这些子句适用于单表删除，但不适用于多表删除。

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 clause.

要从表中删除行，需要具有表上的DELETE权限。对于只被读取的任何列，例如在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. Unlike DELETE, TRUNCATE TABLE cannot be used within a transaction or if you have a lock on the table. See Section 13.1.37, “TRUNCATE TABLE Statement” and Section 13.3.6, “LOCK TABLES and UNLOCK TABLES Statements”.

当您不需要知道已删除的行数时，使用TRUNCATE TABLE语句清空表比使用不带WHERE子句的DELETE语句更快。与DELETE不同，TRUNCATE TABLE不能在事务中使用，或者在表上有锁的情况下使用。

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节“优化delete语句”中讨论的因素的影响。

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.

为了确保给定的DELETE语句不会花费太多时间，用于DELETE的mysql特定的LIMIT row_count子句指定要删除的最大行数。如果要删除的行数大于限制，则重复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. 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.

DELETE支持使用partition子句进行显式分区选择，该子句接受一个或多个分区或子分区(或两者)的逗号分隔的名称列表，从中选择要删除的行。未包含在列表中的分区将被忽略。给定一个分区表t，其分区名为p0，执行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. 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可以与WHERE条件一起使用，在这种情况下，只对列出的分区中的行测试条件。例如，DELETE FROM t PARTITION (p0) WHERE c < 5只删除条件c < 5为真分区p0中的行;任何其他分区中的行都不会被检查，因此不受DELETE的影响。

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.

PARTITION子句也可以在多表DELETE语句中使用。在FROM选项中，每个表最多可以使用一个这样的选项。

For more information and examples, see Section 24.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 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 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

如果您删除包含AUTO_INCREMENT列最大值的行，则该值不会在MyISAM或InnoDB表中重用。如果在自动提交模式下使用delete FROM tbl_name(不带WHERE子句)删除表中的所有行，那么除了InnoDB和MyISAM以外的所有存储引擎都会重新开始。

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 3.6.9, “Using AUTO_INCREMENT”.

对于MyISAM表，可以在多列键中指定AUTO_INCREMENT辅助列。在这种情况下，即使对于MyISAM表，也会重用从序列顶部删除的值。

Modifiers

The DELETE statement supports the following modifiers:

DELETE语句支持以下修饰符:

• If you specify the LOW_PRIORITY modifier, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).

• 如果指定了LOW_PRIORITY修饰符，服务器将延迟DELETE的执行，直到没有其他客户端从表中读取数据。这只影响只使用表级锁的存储引擎(如MyISAM、MEMORY和MERGE)。

• For MyISAM tables, if you use the QUICK modifier, the storage engine does not merge index leaves during delete, which may speed up some kinds of delete operations.

• 对于MyISAM表，如果使用QUICK修饰符，存储引擎在删除时不会合并索引叶子，这可能会加快某些类型的删除操作。

• The IGNORE modifier causes MySQL to ignore ignorable errors during the process of deleting rows. (Errors encountered during the parsing stage are processed in the usual manner.) Errors that are ignored due to the use of IGNORE are returned as warnings. For more information, see The Effect of IGNORE on Statement Execution.

• IGNORE修饰符导致MySQL在删除行过程中忽略可忽略的错误。(解析阶段遇到的错误按照通常的方式处理。)由于使用IGNORE而被忽略的错误将作为警告返回。

Order of Deletion

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语句包含ORDER BY子句，则按子句指定的顺序删除行。这主要与LIMIT结合使用。例如，下面的语句查找匹配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. 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:

如果你要从一个大的表中删除很多行，你可能会超过InnoDB表的锁表大小。为了避免这个问题，或者仅仅是为了减少表保持锁定的时间，下面的策略(根本不使用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. See Section 13.1.36, “RENAME TABLE Statement”.

在执行RENAME TABLE时，其他会话不能访问所涉及的表，因此重命名操作不会遇到并发问题。参见13.1.36节“RENAME TABLE Statement”。

MyISAM Tables

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 13.7.3.4, “OPTIMIZE TABLE Statement”, and Section 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.

在MyISAM表中，删除的行被维护在一个链表中，后续的INSERT操作将重用旧的行位置。要回收未使用的空间并减少文件大小，可以使用OPTIMIZE TABLE语句或myisamchk实用程序来重组表。OPTIMIZE TABLE更容易使用，但myisamchk更快。参见13.7.3.4节“OPTIMIZE TABLE Statement”和4.6.4节“myisamchk - MyISAM TABLE - maintenance Utility”。

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.

QUICK修饰符影响是否为删除操作合并索引叶子。DELETE 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:

当删除的值导致索引块填充不足时，DELETE QUICK是没有用的。在这种情况下，使用QUICK会导致索引中仍然没有被回收的空间被浪费。下面是这样一个场景的例子:

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. 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.

在此场景中，由于使用QUICK，与已删除索引值关联的索引块将变为未填充的索引块，但不会与其他索引块合并。当发生新插入时，它们仍然是未填充的，因为新行在已删除的范围内没有索引值。而且，即使稍后使用没有使用QUICK的DELETE，它们仍然是未填满的，除非一些删除的索引值碰巧位于未填满的块内或相邻的索引块中。在这种情况下，要回收未使用的索引空间，可以使用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.

如果要从表中删除很多行，使用delete QUICK和OPTIMIZE table可能会更快。这将重新构建索引，而不是执行许多索引块合并操作。

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. 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 13.2.10.2, “JOIN Clause”.

您可以在DELETE语句中指定多个表，以根据WHERE子句中的条件从一个或多个表中删除行。不能在多表DELETE中使用ORDER BY或LIMIT。table_references子句列出了连接所涉及的表，如13.2.10.2节“join子句”所述。

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:

对于第一个多表语法，只删除从from子句之前列出的表中匹配的行。对于第二个多表语法，只删除from子句(在USING子句之前)中列出的表中的匹配行。这样做的效果是，你可以同时从许多表中删除行，并有额外的表只用于搜索:

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. For example, to delete rows that exist in t1 that have no match in t2, use a LEFT JOIN:

前面的示例使用INNER JOIN，但是多表DELETE语句可以使用SELECT语句中允许的其他类型的连接，例如LEFT JOIN。例如，要删除t1中存在但t2中不匹配的行，可以使用左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. 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语句，涉及到有外键约束的InnoDB表，MySQL优化器处理表的顺序可能与它们的父/子关系不同。在这种情况下，语句失败并回滚。相反，您应该从单个表中删除数据，并依赖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. Elsewhere, alias references are permitted but not alias declarations.

多表DELETE中的表别名只能在语句的table_references部分声明。在其他地方，允许使用别名引用，但不允许使用别名声明

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. (Bug #89410,Bug #27455809)