Skip to main content

第 25 篇 DML 语句之删除数据

imodouAbout 5 minMySQL

《MySQL 入门教程》第 25 篇 DML 语句之删除数据

上一篇我们介绍了如何使用 UPDATE 语句更新数据，本篇继续学习 MySQL 中的数据删除操作，也就是DELETE语句。

25.1 单表删除

MySQL 使用 DELETE 语句删除表中的数据，基本的语法如下：

DELETE
FROM table_name
[WHERE conditions]
[ORDER BY ...]
[LIMIT row_count];

其中，DELETE 表示删除数据；table_name 是表名；只有满足 WHERE 条件的数据行才会被删除，如果没有指定条件将会删除表中的全部数据；如果指定了 ORDER BY 子句，按照顺序删除数据行；如果指定了 LIMIT 子句，最多会删除 row_count 行数据。

例如，以下语句删除了 emp_devp 表中姓名为“关平”的员工：

DELETE
FROM emp_devp
WHERE emp_name = '关平';

以下语句使用 ORDER BY 和 LIMIT 子句删除了 emp_devp 表中月薪最高的员工：

DELETE
FROM emp_devp
ORDER BY salary DESC
LIMIT 1;

LIMIT 子句可以实现大数据量的多批次删除。

如果删除了外键约束中的父表数据，可能会导致违反外键约束。例如，以下删除语句违反了外键约束：

DELETE
FROM department 
WHERE dept_id = 1;
ERROR 1452 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ds`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`))

由于员工表中存在部门编号为 1 的数据，所以无法删除部门表中的父记录。此时，如果在 DELETE 语句中使用了IGNORE选项，将会忽略执行过程的中错误，当然也不会删除数据。例如：

mysql> DELETE IGNORE
    -> FROM department
    -> WHERE dept_id = 1;
Query OK, 1 row affected, 1 warning (0.09 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                      |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (`ds`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果想要在删除父表数据的同时删除子表中相关的数据，可以使用外键的级联删除（ON DELETE CASCADE）功能。例如：

CREATE TABLE t_parent(id int NOT NULL PRIMARY KEY, c1 int);
INSERT INTO t_parent VALUES (1, 1), (2, 2);

CREATE TABLE t_child(
    id int NOT NULL PRIMARY KEY,
    pid int NOT NULL,
    FOREIGN KEY (pid)
      REFERENCES t_parent (id)
      ON DELETE CASCADE 
      ON UPDATE CASCADE
);
INSERT INTO t_child VALUES (1, 1), (2, 1), (3, 2);

SELECT * FROM t_child;
id|pid|
--|---|
 1|  1|
 2|  1|
 3|  2|

其中，t_child 的 pid 字段引用了 t_parent 字段的主键；外键约束中的 ON DELETE CASCADE 表示删除父表记录时级联删除子表中的数据，ON UPDATE CASCADE 表示更新父表记录时级联更新子表中的数据。以下语句演示了级联删除的效果：

DELETE 
FROM t_parent
WHERE id = 1;

SELECT * FROM t_child;
id|pid|
--|---|
 3|  2|

删除 t_parent 中 id 等于 1 的记录之后，t_child 中相应的记录也被删除。

如果 DELETE 语句中没有指定 WHERE 条件，将会删除表中的全部数据。此时，我们可以使用TRUNCATE语句快速删除所有数据：

TRUNCATE [TABLE] table_name;

例如，以下语句删除了 emp_devp 中的所有数据：

TRUNCATE TABLE emp_devp;

TRUNCATE 语句相当于 DROP TABLE 加上 CREATE TABLE。虽然 TRUNCATE 和 DELETE 的逻辑效果类似，但是它属于 DDL 语句，而且它们之间存在一些区别：

  • TRUNCATE 删除并重建表，通常比 DELETE 快很多，尤其对于大表而言；
  • TRUNCATE 会进行隐式提交，所以无法回滚；
  • TRUNCATE 语句需要获取表上的独占锁；
  • 如果存在外键约束，父表无法使用 TRUNCATE 语句；除非是自引用的外键约束；
  • TRUNCATE 语句通常返回“0 rows affected”，不代表被删除的数据行数；
  • TRUNCATE 语句会将表中的 AUTO_INCREMENT 重置为初始值；
  • TRUNCATE 语句不会触发 ON DELETE 触发器。

25.2 跨表删除

与 UPDATE JOIN 语句实现跨表更新类似，DELETE JOIN语句也可以通过关联其他表中的数据进行删除操作。

DELETE table_name
FROM table_name
[INNER JOIN | LEFT JOIN] other_table ON conditions
WHERE conditions;

DELETE
FROM table_name
USING table_name
[INNER JOIN | LEFT JOIN] other_table ON conditions
WHERE conditions;

其中，table_name 是需要删除数据的表名；第一种语法只删除 DELETE 后面表中的数据，第二种语法只删除 FROM 后面表中的数据；INNER JOIN或者LEFT JOIN用于连接其他的表；跨表删除不支持 ORDER BY 和 LIMIT 子句。

例如，以下语句通过关联 employee 中的数据删除 emp_devp 中的数据：

DELETE ed
FROM emp_devp ed
LEFT JOIN employee e ON (e.emp_id = ed.emp_id)
WHERE e.emp_id IS NULL;

DELETE
FROM ed
USING emp_devp ed
LEFT JOIN employee e ON (e.emp_id = ed.emp_id)
WHERE e.emp_id IS NULL;

该语句可以用于删除 emp_devp 中不属于 employee 表的数据。以上关联删除语句也可以通过一个子查询实现：

DELETE
FROM emp_devp
WHERE emp_id NOT IN (SELECT emp_id FROM employee);

25.3 多表删除

MySQL 中的DELETE JOIN语句也可以用于同时删除多个表中的数据。

DELETE t1, t2
FROM t1
[INNER JOIN | LEFT JOIN] t2 ON conditions
[INNER JOIN | LEFT JOIN] other_table ON conditions
WHERE conditions;

DELETE
FROM t1, t2
USING t1
[INNER JOIN | LEFT JOIN] t2 ON conditions
[INNER JOIN | LEFT JOIN] other_table ON conditions
WHERE conditions;

与上面的跨表更新类似，第一种语法只删除 DELETE 后面表中的数据，第二种语法只删除 FROM 后面表中的数据；连接其他的表 other_table 是可选项，也可以连接多个表；多表删除也不支持 ORDER BY 和 LIMIT 子句。

例如：

DELETE
FROM p, c
USING t_parent p
INNER JOIN t_child c ON (c.pid = p.id)
WHERE p.c1 = 2;

该语句通过连接操作同时删除了 t_parent 和 t_child 中的数据。

Default footer
Copyright © 2024 imodou