MySQL :: MySQL 8.4 Reference Manual :: 15.2.2 DELETE Statement

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / DELETE Statement

15.2.2 删除语句

DELETE 是一个 DML 语句，用于从表中删除行。

一个 DELETE 语句可以以第15.2.20节，“通用表达式”开始，定义在DELETE 中的通用表达式。见第15.2.20节，“通用表达式”。

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

删除语句删除了 tbl_name 中的行，并返回删除的行数。要检查删除的行数，调用ROW_COUNT() 函数，见第14.15节，“信息函数”。

可选的 WHERE 子句中的条件确定要删除的行。没有 WHERE 子句，所有行都将被删除。

where_condition 是要删除的每一行的表达式，它按照第15.2.13节，“SELECT 语句” 中描述指定的。

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

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]

要从表中删除行，您需要对该表拥有DELETE权限。对于只读的列，如WHERE子句中指定的列，只需拥有SELECT权限。

如果不需要知道删除了多少行，使用TRUNCATE TABLE语句比无WHERE子句的DELETE语句更快。与DELETE不同，TRUNCATE TABLE不能在事务中使用或锁定表。请参阅第15.1.37节，“TRUNCATE TABLE 语句”和第15.3.6节，“LOCK TABLES 和 UNLOCK TABLES 语句”。

删除操作的速度也可能受到第10.2.5.3节，“优化DELETE语句”中讨论的因素影响。

为了确保某个DELETE语句不花太多时间，MySQL特定的LIMIT row_count子句对DELETE指定要删除的最大行数。如果要删除的行数超过限制，重复执行DELETE语句直到影响的行数小于LIMIT值。

子查询

不能从同一个表中删除和选择数据。

DELETE支持使用PARTITION子句来明确指定要删除的分区，指定的是一个或多个分区名（包括子分区），不包含在列表中的分区将被忽略。给定一个分区表t，其中有一个分区名为p0，执行语句DELETE FROM t PARTITION (p0)对该表的效果与执行ALTER TABLE t TRUNCATE PARTITION (p0)相同；在这两个情况下，所有分区p0中的行都被删除。

PARTITION 可以与 WHERE 条件一起使用，在这种情况下，条件只在列举的分区中测试。例如，DELETE FROM t PARTITION (p0) WHERE c < 5 只删除分区 p0 中的行；其他分区中的行不被检查因此不受 DELETE 的影响。

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

更多信息和示例，请参见第26.5节，“分区选择”。

如果您删除包含 AUTO_INCREMENT 列的最大值所在行，MyISAM 或 InnoDB 表中的值不会被重用。如果您在autocommit 模式下删除表中的所有行（没有 WHERE 子句）tbl_name，除了 InnoDB 和 MyISAM 外，对于所有存储引擎重新开始序列。对于 InnoDB 表，有些例外情况，如第17.6.1.6节，“InnoDB 自动递增处理”中讨论了。

对于 MyISAM 表，您可以在多列键中指定一个AUTO_INCREMENT次要列。在这种情况下，即使从序列顶部删除值，MyISAM 表也会重新使用该值。见第5.6.9节，“使用 AUTO_INCREMENT”。

DELETE 语句支持以下修饰符：

如果指定了LOW_PRIORITY 修饰符，服务器将延迟执行DELETE，直到没有其他客户端正在读取该表。这只影响使用表级锁定的存储引擎（例如 MyISAM、MEMORY 和 MERGE）。
对于 MyISAM 表，如果使用了QUICK 修饰符，存储引擎不会在删除过程中合并索引叶子结点，这可能会加速某些删除操作。
IGNORE 修饰符导致 MySQL 忽略删除过程中的可忽略错误（解析阶段的错误按照通常方式处理）。由于使用 IGNORE，忽略的错误将作为警告返回。更多信息，请见修饰符 IGNORE 对语句执行的影响。

如果DELETE 语句包含一个ORDER BY 子句，删除的行将按照子句指定的顺序删除。这主要是与LIMIT 一起使用。例如，以下语句找到匹配WHERE 子句的行，按timestamp_column 排序，然后删除最早的一条：

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

ORDER BY 也可以用来避免引用完整性违规。

InnoDB 表

如果您从一个大表中删除许多行，可能会超出DELETE 的锁定表大小。为了避免这个问题，或者只是为了最小化表锁定的时间，以下策略（不使用DELETE）可能是有帮助的：

将要删除的行选择到一个空表中，该表结构与原始表相同：
```
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
```
使用RENAME TABLE 原子地将原始表移到一边，并将副本重命名为原始名称：
```
RENAME TABLE t TO t_old, t_copy TO t;
```
删除原始表：
```
DROP TABLE t_old;
```

在RENAME TABLE 执行时，其他会话不能访问涉及的表，所以重命名操作不受并发问题影响。见第15.1.36节，“RENAME TABLE 语句”。

MyISAM 表

在 MyISAM 表中，删除的行记录在链表中维护，后续INSERT 操作重用老的行记录位置。要回收未使用的空间并减少文件大小，请使用OPTIMIZE TABLE 语句或myisamchk 工具重新组织表。 OPTIMIZE TABLE 更加方便，但myisamchk 更快。请参阅第15.7.3.4节，“OPTIMIZE TABLE 语句”，和第6.6.4节，“myisamchk — MyISAM 表维护工具”。

QUICK 修饰符影响索引叶子节点的合并操作。DELETE QUICK 对于应用程序来说，删除行记录的索引值被替换为后插入行记录的索引值最有用。在这种情况下，删除值留下的空洞会被重用。

DELETE QUICK 在删除值导致索引块不够填充的情况下无效。在这种情况下，使用QUICK 可能导致索引中留下的空洞无法回收。以下是一个这样的场景：

创建一个包含索引AUTO_INCREMENT列的表。
将许多行插入到表中。每个插入操作都将索引值添加到索引的高端。
使用DELETE QUICK删除位于索引范围低端的行。

在这种情况下，关联于被删除索引值的索引块变为不满，但由于使用了QUICK因此它们不会与其他索引块合并。它们仍然不满当新插入行时，因为新行的索引值不在被删除范围内。此外，即使您后来使用DELETE而不使用QUICK，除非被删除的索引值恰好位于或邻近于不满块中。要在这种情况下重用未用的索引空间，请使用OPTIMIZE TABLE。

如果您计划从表中删除许多行，可能更快地使用DELETE QUICK后跟OPTIMIZE TABLE。这样重建索引，而不是执行多个索引块合并操作。

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

对于第一种多表语法，只删除前面列出的表中的匹配行。对于第二种多表语法，只删除FROM子句中列出的表中的匹配行（在USING子句之前）。结果是，您可以同时从多个表中删除行，并且有额外的表用于搜索：

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

或者：

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

这些语句在搜索要删除的行时使用所有三个表，但只删除匹配行来自t1 和 t2 表。

前面的示例使用了DELETE 语句，但多表DELETE 语句可以使用SELECT语句允许的其他连接类型，例如SELECT 语句中的LEFT JOIN。例如，要删除t1 表中没有匹配的行，使用LEFT JOIN：

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

语法允许在每个tbl_name后添加.*，以保持与Access的兼容性。

如果您使用多表DELETE语句，涉及InnoDB表，其中存在外键约束，MySQL 优化器可能按照不一样的顺序处理表，这种情况下语句失败并回滚。相反，您应该从单个表中删除，并依靠InnoDB提供的ON DELETE功能来修改其他表。

Note

如果您为表声明了别名，必须在引用该表时使用别名：

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

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

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;

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语句也支持表别名。

PREV HOME UP NEXT