MySQL DELETE 用法与实例

在本文中,我们通过实例介绍了在 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 BYLIMIT 子句。

以下语句用来删除 actor_copyactor_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 子句中的条件的行。

  • 以下语句删除 t1t2 表中满足条件的行:

    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 标识符。