MySQL :: MySQL 8.3 Reference Manual :: 15.2.2 DELETE Statement

Documentation Home

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / DELETE Statement

15.2.2 删除语句

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 是一个表达式，对于要删除的每一行都评估为 true。它按照第 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 权限，以便从表中删除行。您只需要对只读的列拥有 SELECT 权限，例如在 WHERE 子句中命名的列。

性能

当您不需要知道删除的行数时，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 中满足条件 c < 5 的行；其他分区中的行不受影响。

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

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

自动递增列

如果您删除包含最大值的行的 AUTO_INCREMENT 列，MyISAM 或 InnoDB 表中的值不会被重用。如果您使用 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 表维护实用程序”。

快速修饰符影响删除操作中的索引叶子合并。DELETE QUICK 对于应用程序非常有用，其中删除的索引值被后续插入的行所替换。在这种情况下，删除的值留下的空隙被重复使用。

DELETE QUICK 不适用于删除的值导致索引块跨越一系列索引值的范围，而新的插入操作再次发生。在这种情况下，使用 QUICK 可能会导致索引中的浪费空间无法被回收。以下是一个这样的场景：

创建一个包含自动递增列的表。
将许多行插入表中。每个插入操作都会将索引值添加到索引的高端。
使用 DELETE QUICK 删除表的低端列范围内的一块行。

在这种情况下，与删除的索引值相关的索引块变得不充分，但由于使用 QUICK，它们不会与其他索引块合并。当新的插入操作发生时，这些索引块仍然不充分，除非一些删除的索引值恰好位于索引块中或相邻的索引块中。在这种情况下，使用 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 兼容。

如果您使用多表 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