Skip to main content

MySQL ON DELETE CASCADE:自动从相关表中删除数据

在上一个教程中,您学习了如何使用单个delete语句从多个相关表中删除数据。然而,MySQL为外键提供了一种更有效的方法,称为DELETE CASCADE referential action,它允许您在从父表中删除数据时自动从子表中删除数据。

MySQL上的删除级联示例

让我们来看一个在DELETE CASCADE上使用MySQL的例子。

假设我们有两张桌子:建筑物和房间。在这个数据库模型中,每栋建筑都有一个或多个房间。然而,每个房间只属于一栋建筑。没有建筑,房间就不可能存在。

建筑物和房间表之间的关系为一对多(1:N),如下图所示:

MySQL ON DELETE CASCADE - sample tables

从“建筑物”表中删除一行时,还需要删除“房间”表中引用“建筑物”表中该行的所有行。例如,当您在“建筑物”表中删除带有2号建筑物的行时,如以下查询所示:

DELETE FROM buildings 
WHERE building_no = 2;

您还希望同时删除房间表中与2号楼有关的行。

以下是演示ON DELETE级联引用操作如何工作的步骤。

第一步。创建“建筑物”表:

CREATE TABLE buildings (
building_no INT PRIMARY KEY AUTO_INCREMENT,
building_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
);

第二步。创建rooms表:

CREATE TABLE rooms (
room_no INT PRIMARY KEY AUTO_INCREMENT,
room_name VARCHAR(255) NOT NULL,
building_no INT NOT NULL,
FOREIGN KEY (building_no)
REFERENCES buildings (building_no)
ON DELETE CASCADE
);

请注意,外键约束定义末尾的ON DELETE CASCADE子句。

第三步。在“建筑物”表中插入行:

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
('ACME Sales','5000 North 1st Street CA 95134');

第四步。从建筑物表中查询数据:

SELECT * FROM buildings;

MySQL ON DELETE CASCADE buildings table

我们在建筑物表中有两行。

第五步。在rooms表中插入行:

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
('War Room',1),
('Office of CEO',1),
('Marketing',2),
('Showroom',2);

第六步。从rooms表中查询数据:

SELECT * FROM rooms;

MySQL ON DELETE CASCADE - rooms table

我们有三个房间属于1号楼,两个房间属于2号楼。

第七步。删除2号楼的建筑:

DELETE FROM buildings 
WHERE building_no = 2;

第八步。从rooms表查询数据:

SELECT * FROM rooms;

MySQL ON DELETE CASCADE - rooms table after delete

如您所见,所有参考2号楼的行都被自动删除。

请注意,ON DELETE CASCADE仅适用于带有支持外键的存储引擎的表,例如InnoDB。

有些表类型不支持外键,例如MyISAM,因此您应该为计划使用MySQL ON DELETE级联引用操作的表选择适当的存储引擎。

在删除级联操作中查找受MySQL影响的表的提示

有时,从表中删除数据时,了解哪个表受ON DELETE级联引用操作的影响很有用。您可以从信息模式数据库中的引用约束查询此数据,如下所示:

USE information_schema;

SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'database_name'
AND referenced_table_name = 'parent_table'
AND delete_rule = 'CASCADE'

例如,要在classicmodels数据库中查找与具有级联删除规则的buildings表关联的表,可以使用以下查询:

USE information_schema;

SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'classicmodels'
AND referenced_table_name = 'buildings'
AND delete_rule = 'CASCADE'

MySQL ON DELETE CASCADE tips

在本教程中,您学习了如何在从父表中删除数据时,使用MySQL ON DELETE CASCADE引用操作作为外键自动从子表中删除数据。