MySQL :: MySQL 9.0 参考手册 :: 15.2.2 DELETE 语句

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / DELETE 语句

15.2.2 DELETE 语句

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

一个 DELETE 语句可以以一个 WITH 子句开头，以定义在 DELETE 内可访问的公用表表达式。参见第 15.2.20 节，“WITH（公用表表达式）”.

单表语法

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 语句从 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 子句显式选择分区，该子句采用一个或多个分区或子分区（或两者）的逗号分隔名称列表，从中选择要删除的行。列表中未包含的分区将被忽略。给定一个名为 p0 的分区的已分区表 t，执行语句 DELETE FROM t PARTITION (p0) 对表的执行效果与执行 ALTER TABLE t TRUNCATE PARTITION (p0) 相同；在这两种情况下，分区 p0 中的所有行都将被删除。

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

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

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

自动递增列

如果您删除包含 AUTO_INCREMENT 列的最大值的行，该值不会在 MyISAM 或 InnoDB 表中重新使用。如果您在 autocommit 模式下使用 DELETE FROM tbl_name（不带 WHERE 子句）删除表中的所有行，则除了 InnoDB 和 MyISAM 之外，所有存储引擎的序列都会重新开始。 InnoDB 表的行为有一些例外，如第 17.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理” 中所述。

对于 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 表

如果您要从一个大型表中删除许多行，您可能会超出 InnoDB 表的锁表大小。为避免此问题，或者只是为了最大程度地减少表保持锁定状态的时间，以下策略（根本不使用 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，它们不会与其他索引块合并。当发生新的插入时，它们仍然未填满，因为新行在已删除范围内没有索引值。此外，即使您随后使用不带 QUICK 的 DELETE，它们仍然未填满，除非某些已删除索引值碰巧位于未填满块内或相邻的索引块中。若要在此情况下回收未使用的索引空间，请使用 OPTIMIZE TABLE。

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

多表删除

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

对于第一个多表语法，只删除在 FROM 子句之前列出的表的匹配行。对于第二个多表语法，只删除在 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 中删除匹配的行。

前面的示例使用 INNER JOIN，但多表 DELETE 语句可以使用 SELECT 语句中允许的其他连接类型，例如 LEFT JOIN。例如，要删除 t1 中不存在于 t2 中的匹配项的行，请使用 LEFT JOIN

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

该语法允许在每个 tbl_name 后使用 .*，以与 Access 兼容。

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

注意

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

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 语句。