10 个基本的 MariaDB / MySQL DELETE 命令示例

当您使用 MySQL 数据库时，在多种情况下您可能希望从一个或多个表中删除现有记录。

在本教程中，我们将通过一些有用的示例来解释如何使用 MySQL Delete。

本教程涵盖以下内容：

• 删除特定行
• 删除多行（使用数字列匹配）
• 删除多行（使用字符串列匹配）
• 使用 LIMIT 选项删除记录
• 删除与截断（用于删除所有行）
• 使用 IGNORE 选项删除记录
• 何时将 order by 与 Delete 操作结合使用？
• 使用 QUICK 选项删除记录
• 使用 LOW_PRIORITY 选项删除记录
• 使用 INNER join 从多个表中删除记录

要从表中删除记录，您应该对要从中删除行的特定表具有 DELETE 权限。

在本教程中，我们将使用以下员工表作为示例。

MariaDB [tgs]> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+

1. 删除特定行

要删除特定行，您应该使用主列值或唯一列值之一指定 WHERE 条件。

下面的例子将从employee表中删除一条id为100的记录。

MariaDB [tgs]> DELETE FROM employee WHERE id = 100;
Query OK, 1 row affected (0.01 sec)

在上面的输出中：

• delete 命令的输出将显示它删除了多少条记录。正如您从上面的输出中看到的，它说“1 行受影响”，这意味着它已删除了一行。
• 如果查询已执行，输出还将显示“查询确定”。如果有语法错误，它会在这里显示。即使它没有删除任何记录，只要没有语法错误并且语句是干净的，这一行仍然会说“查询OK”。最后，这还将显示 MySQL 执行查询所需的时间（例如：0.01 秒）。

现在从员工表中删除了 id 为 100 的第一条记录。

MariaDB [tgs]> SELECT * FROM employee;
+-----+-------+------------+--------+
| id  | name  | dept       | salary |
+-----+-------+------------+--------+
| 200 | Jason | Technology |   5500 |
| 300 | Mayla | Technology |   7000 |
| 400 | Nisha | Marketing  |   9500 |
| 500 | Randy | Technology |   6000 |
+-----+-------+------------+--------+

2. 使用字符串列匹配删除多行

您可以通过匹配具有特定条件的字符串列来从表中删除记录。

对于字符串，您可以使用关键字“like”进行部分匹配。对于部分匹配，在值列中使用 %。

下面的例子将从employee 表中删除dept 列中值以“Tech”开头的记录。因此，这将删除部门为“技术”的所有记录。

MariaDB [tgs]> DELETE FROM employee WHERE dept LIKE 'Tech%';
Query OK, 3 rows affected (0.00 sec)

上面的输出表明它已经删除了 3 条记录。正如我们从以下输出中看到的，我们不再看到带有“Technology”部门的记录。

MariaDB [tgs]> SELECT * FROM employee;
+-----+--------+-----------+--------+
| id  | name   | dept      | salary |
+-----+--------+-----------+--------+
| 100 | Thomas | Sales     |   5000 |
| 400 | Nisha  | Marketing |   9500 |
+-----+--------+-----------+--------+

3.使用数字列匹配删除多行

与上面的数字列示例类似，您也可以通过匹配具有特定条件的字符串列来从表中删除记录。

下面将从employee表中删除工资小于等于700的记录。

MariaDB [tgs]> DELETE FROM employee WHERE salary <=7000;
Query OK, 4 rows affected (0.00 sec)

上面的命令已经从我们的员工表中删除了 4 条记录。我们目前只剩下一个记录。

MariaDB [tgs]> SELECT * FROM employee;
+-----+-------+-----------+--------+
| id  | name  | dept      | salary |
+-----+-------+-----------+--------+
| 400 | Nisha | Marketing |   9500 |
+-----+-------+-----------+--------+

4. 使用 LIMIT 选项删除行

即使 where 子句匹配许多记录，您仍然可以使用 LIMIT 选项限制应删除的记录数。

当删除命令需要很长时间，并且您想将删除操作分解为多个块时，这非常有用。例如，如果您要删除超过 100,000 条记录，则可以使用 LIMIT 10000 一次仅删除 10,000 条记录。

例如，以下不带任何 LIMIT 选项的删除命令将删除所有匹配的记录。即这已经删除了 5 条记录。

MariaDB [tgs]> DELETE FROM employee WHERE id > 1 ;
Query OK, 5 rows affected (0.00 sec)

但是当您使用 LIMIT 选项时，即使匹配 5 条记录，以下内容也只会删除 2 条记录。

MariaDB [tgs]> DELETE FROM employee WHERE id > 1 LIMIT 2;
Query OK, 2 rows affected (0.01 sec)

再次执行相同的删除命令，将再删除 2 条记录。

MariaDB [tgs]> DELETE FROM employee WHERE id > 1 LIMIT 2;
Query OK, 2 rows affected (0.00 sec)

再次执行时，这次它只删除了一条记录，因为这是最后匹配的记录。

MariaDB [tgs]> DELETE FROM employee WHERE id > 1 LIMIT 2;
Query OK, 1 row affected (0.00 sec)

现在我们没有更多匹配的记录要删除。

MariaDB [tgs]> DELETE FROM employee WHERE id > 1 LIMIT 2;
Query OK, 0 rows affected (0.00 sec)

5. Delete Vs Truncate（用于删除所有行）

当您想删除表中的所有行时，您可以使用 DELETE 或 TRUNCATE 命令，如下所示。两者都会做同样的事情。但是，有区别。

MariaDB [tgs]> DELETE FROM employee;
Query OK, 5 rows affected (0.00 sec)

(or)

MariaDB [tgs]> TRUNCATE TABLE employee;
Query OK, 0 rows affected (0.00 sec)

以下是您应该了解的有关 DELETE 与 TRUNCATE 的一些事项：

• 截断比删除快得多。如果您只想快速清空表，请使用 truncate。
• Delete 是一个 DML 语句。截断是一个 DDL 语句。
• 当您执行 Truncate 命令时，它确实会删除表并重新创建空表。这就是 truncate 非常快的原因，因为它不会像 DELETE 命令那样一一删除记录。
• 当特定表上有锁时，不能使用 truncate 命令。
• 从上面的 truncate 命令输出中可以看出，它没有显示删除了多少行。即使它截断了一个有几行的表，它也只会说“0 行受影响”。
• 因此，如果您需要知道在擦除表期间删除了多少条记录，您应该使用 delete。
• 如果您在表上有一个 on-delete 触发器，当您执行 truncate 命令时，这些触发器将不会被执行。
• 最后，一个重要的区别是：当你执行 truncate 命令时，你不能回滚。但是当您执行删除命令时，您可以回滚。截断执行隐式提交。

6. 使用 IGNORE 选项删除行

当您想忽略您知道特定 DELETE 命令将抛出的真正有效错误消息时，您可以将 IGNORE 关键字与删除命令（即 DELETE IGNORE）一起使用。

所以，当你使用 DELETE IGNORE 时，如果有任何有效的错误信息，它只会被视为警告。使用 DELETE IGNORE 时必须非常小心，尤其是在有外键的表上，因为 delete ignore 只会删除记录而无需担心任何外部外键依赖项。

MariaDB [tgs]> DELETE IGNORE FROM contractorbenefits WHERE id > 200;
Query OK, 3 rows affected, 1 warning (0.01 sec)

从上面的输出中可以看出，即使存在外键依赖关系，delete ignore 也会从表中删除记录。它只是将那些真正的错误消息视为警告。

7. 为什么要使用 Order by 和 Delete？

您可以将 Order by 子句与 Delete 命令一起使用。

当您希望以特定顺序删除行时，这会非常有用。

在删除命令中，当你结合“order by”和“limit”选项时，你可以做一些聪明的把戏。

例如，当您执行以下不带“order by”子句的删除命令时，这将删除 ID 为 100 的员工记录（即“技术”部门中最老的员工）。

MariaDB [tgs]> DELETE FROM employee WHERE dept = 'Technology' LIMIT 1;
Query OK, 1 row affected (0.00 sec)

上述命令删除了员工 ID 200（最年长的技术员工）。

MariaDB [tgs]> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+

但是，如果您将 ORDER by 和 LIMIT 组合在一起，您也可以删除“技术”部门的最新员工，如下所示。

MariaDB [tgs]> DELETE FROM employee WHERE dept = 'Technology' ORDER BY id DESC LIMIT 1;
Query OK, 1 row affected (0.00 sec)

上述命令删除了员工 ID 500（最新的技术员工）。

MariaDB [tgs]> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Mayla  | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
+-----+--------+------------+--------+

此外，请记住，当您想要删除特定序列中的记录以避免任何参照完整性约束时，您可以使用 ORDER BY 子句。

8. 使用 QUICK 选项删除行

对于有一些索引的大表，您可以使用 QUICK 选项来加速删除操作，如下所示。

MariaDB [tgs]> DELETE QUICK FROM employee WHERE id > 200;
Query OK, 3 rows affected (0.00 sec)

在删除中，当您使用 QUICK 选项时，它不会合并索引叶，这可能会根据您在表上拥有的索引类型加快删除操作。

9. 使用 LOW_PRIORITY 选项删除行

当您尝试从频繁使用的表中删除记录时，低优先级关键字非常有用。

LOW_PRIORITY 关键字仅对以下存储引擎有效：MyISAM、MEMORY 和 MERGE。即您只能在实现表级锁定的数据库存储引擎上使用它。

MariaDB [tgs]> DELETE LOW_PRIORITY FROM EMPLOYEE WHERE dept IN ('Sales', 'Marketing');
Query OK, 2 rows affected (0.01 sec)

当您使用 LOW_PRIORITY 关键字时，只有在没有其他人从表中读取时才会执行删除操作。

10. 使用 INNER Join 从多个表中删除

在前面的所有示例中，我们使用 delete 命令仅从单个表中删除记录。

有两种方法可以指定从多个表中删除记录。

在第一种方法中，您可以在“FROM”关键字之前指定要从中删除记录的表，如下所示。下面将从员工和福利表中删除符合 where 条件中指定条件的记录的记录。

DELETE employee, benefits FROM employee 
 INNER JOIN benefits INNER JOIN contractor
 WHERE employee.id=benefits.id AND benefits.id=contractor.id;

在第二种方法中，您可以在“FROM”关键字后指定要从中删除记录的表，如下所示。以下内容将从员工和福利中删除符合 where 条件中指定条件的记录。

DELETE FROM employee, benefits USING employee 
 INNER JOIN benefits INNER JOIN contractor
 WHERE employee.id=benefits.id AND benefits.id=contractor.id;