MySQL DELETE 用法与实例

在 MySQL 中，DELETE 语句用于从表中删除满足条件的记录行。

`DELETE` 单表删除语法

如果要从数据库表中删除记录行，请使用 DELETE 语句。 DELETE 语句单表删除语法如下：

DELETE FROM table_name
[WHERE clause]
[ORDER BY ...]
[LIMIT row_count]

说明：

DELETE FROM 后跟的是要从中删除数据的表。
WHERE 子句用来过滤需要删除的行。满足条件的行会被删除。
WHERE 子句是可选的。没有 WHERE 子句时，DELETE 语句将删除表中的所有行。
ORDER BY 子句用来指定删除行的顺序。它是可选的。
LIMIT 子句用来指定删除的最大行数。它是可选的。
DELETE 语句返回删除的行数。

DELETE 语句中的 WHERE 子句非常重要。除非您特意，否则不要省略 WHERE 子句。它会导致删除表中的所有数据。

单表删除实例

在以下实例中，我们使用 Sakila 示例数据库中的 actor 表进行演示。

请注意，一旦使用 DELETE 语句删除了数据，数据就会消失。请谨慎操作。

为了防止数据丢失，我们通过以下 SQL 创建一个表 actor_copy 做为 actor 表的拷贝。如下：

CREATE TABLE actor_copy AS (SELECT * FROM actor);

Query OK, 201 rows affected (0.01 sec)
Records: 201  Duplicates: 0  Warnings: 0

下面开始我们的实例。

根据条件删除数据

删除 actor_id 等于 1 的行

DELETE FROM actor_copy WHERE actor_id = 1;

Query OK, 1 row affected (0.00 sec)

删除 last_name 等于 KILMER 的行

DELETE FROM actor_copy WHERE last_name = 'KILMER';

Query OK, 5 rows affected (0.01 sec)

限制删除的最大行数

考虑以下这些需求：

删除排名最靠后的 5 个成绩
删除最新注册的 10 名用户

这时，我们可以结合使用 ORDER BY 和 LIMIT 子句。

以下语句用来删除 actor_copy 中 actor_id 最大的 10 行：

DELETE FROM actor_copy
ORDER BY actor_id DESC
LIMIT 10;

Query OK, 10 rows affected (0.01 sec)

如果单独使用 LIMIT 子句，删除的顺序是不明确的。大多数情况下， DELETE 语句中的 LIMIT 子句都应该和 ORDER BY 子句一起使用。

删除表中所有的行

如果我们不在 DELETE 语句中使用 WHERE 或者 LIMIT 子句，则会删除表中的所有行。

DELETE FROM actor_copy;

Query OK, 185 rows affected (0.00 sec)

我们通过以下语句检查表中是否还有数据行：

SELECT COUNT(*) FROM actor_copy;

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

此时， actor_copy 表已经空了。

如果你只是想清空表，可以使用 TRUNCATE TABLE 语句以获得更好的性能。如下：

TRUNCATE actor_copy;

DELETE 表别名

在早期的 MySQL 版本中，单表删除 DELETE 语句不支持为表设置别名。比如：

DELETE FROM main_table m
WHERE NOT EXISTS (
    SELECT *
    FROM another_table a
    WHERE a.main_id = m.id
  );

将会产生错误：Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't' at line 1 0.016 sec。

可以改成如下语句：

DELETE m FROM main_table m
WHERE NOT EXISTS (
    SELECT *
    FROM another_table a
    WHERE a.main_id = m.id
  );

或者不使用别名，而是使用表名：

DELETE FROM main_table
WHERE NOT EXISTS (
    SELECT *
    FROM another_table a
    WHERE a.main_id = main_table.id
  );

`DELETE` 多表删除

我们也可以在一个 DELETE 语句中指定多个表，以便在一个或多个表中删除符合 WHERE 子句中的条件的行。

以下语句删除 t1 和 t2 表中满足条件的行：

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

以下语句删除 t1 表中满足条件的行：

DELETE t1
FROM t1 INNER JOIN t2
WHERE t1.id = t2.id;

以下语句在删除时使用 LEFT JOIN：

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

只要是 SELECT 语句中允许使用的 JOIN 类型，多表删除语句都可以使用。

多表删除语句中不能使用 LIMIT 子句和 ORDER BY 子句。

DELETE 修饰符

在 MySQL 中， DELETE 语句支持 3 个修饰符:

LOW_PRIORITY: 如果你指定了 LOW_PRIORITY 修饰符，MySQL 服务器将延迟执行 DELETE 操作直到没有客户端对表进行读操作。这个修饰符影响那些只支持表级锁的存储引擎，比如： MyISAM, MEMORY, 和 MERGE。
QUICK: 如果你指定了 QUICK 修饰符，MyISAM 存储引擎不会在 DELETE 操作期间合并索引。这在某种程度上会加快 DELETE 操作。
IGNORE: 如果你指定了 IGNORE 修饰符，MySQL 服务器会在执行 DELETE 操作期间忽略那些可忽略的错误。这些错误最终会作为 WARNING 返回。

修饰符的用法如下：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name

结论

在本文中，我们通过实例介绍了在 MySQL 中使用 DELETE 语句从表中删除满足条件的记录行。以下是 DELETE 语句的要点：

DELETE 语句可用于从一个表或多个表中删除数据行。
WHERE 子句用来过滤需要删除的行。满足条件的行会被删除。
WHERE 子句是可选的。没有 WHERE 子句时，DELETE 语句将删除表中的所有行。
ORDER BY 子句用来指定删除行的顺序。它是可选的。它不能用在多表删除。
LIMIT 子句用来指定删除的最大行数。它是可选的。它不能用在多表删除。
一旦执行了 DELETE 语句，数据就会消失。所以请谨慎使用 DELETE 语句。
多表删除需要使用 JOIN 连接多个表。
如果想忽略删除过程中一些错误行，可使用 IGNORE 标识符。

DELETE 单表删除语法