mysql on delete_MySQL ON DELETE CASCADE

MySQL ON DELETE CASCADE

简介：在本教程中，您将学习如何 对外键使用MySQL ON DELETE CASCADE引用操作来从多个相关表中删除数据。

在上一个教程中，您学习了如何使用单个外键的ON DELETE CASCADE引用操作，允许您在从父表中删除数据时自动从子表中删除数据。

MySQL ON DELETE CASCADE 实例

我们来看一个使用MySQL的例子ON DELETE CASCADE。

假设我们有两个表：buildings和rooms。在此数据库模型中，每个建筑物都有一个或多个房间。但是，每个房间只属于一栋建筑。没有建筑物就不会有房间。

buildings 和rooms表之间的关系是一对多(1：N)，如下面的数据库图所示：

当我们从buildings表中删除一行时，我们还想删除rooms  表中引用buildings表中行的行。例如，当我们删除一个没有建筑物的行时。2在buildings  表作为以下查询：

DELETE FROM buildings

WHERE

building_no = 2;

我们希望rooms表中引用建筑物编号2的行也将被删除。

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

第1步，创建buildings表：

CREATE TABLE buildings (

building_no INT PRIMARY KEY AUTO_INCREMENT,

building_name VARCHAR(255) NOT NULL,

address VARCHAR(255) NOT NULL

);

第2步，创建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  在外键约束定义的末尾添加了子句。

第3步，将数据插入buildings表中：

INSERT INTO buildings(building_name,address)

VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),

('ACME Sales','5000 North 1st Street CA 95134');

第4步。从buildings表中查询数据：

SELECT * FROM buildings;

运行结果：

+-------------+------------------+--------------------------------+

| building_no | building_name | address |

+-------------+------------------+--------------------------------+

| 1 | ACME Headquaters | 3950 North 1st Street CA 95134 |

| 2 | ACME Sales | 5000 North 1st Street CA 95134 |

+-------------+------------------+--------------------------------+

2 rows in set (0.00 sec)

我们在buildings表中有两行。

第5步，将数据插入rooms表中：

INSERT INTO rooms(room_name,building_no)

VALUES('Amazon',1),

('War Room',1),

('Office of CEO',1),

('Marketing',2),

('Showroom',2);

第6步，从rooms表中查询数据：

SELECT * FROM rooms;

运行结果：

+---------+---------------+-------------+

| room_no | room_name | building_no |

+---------+---------------+-------------+

| 1 | Amazon | 1 |

| 2 | War Room | 1 |

| 3 | Office of CEO | 1 |

| 4 | Marketing | 2 |

| 5 | Showroom | 2 |

+---------+---------------+-------------+

5 rows in set (0.01 sec)

我们有3个房间属于建筑1和2个房间属于建筑2。

第7步，删除建筑物号码的建筑物。2：

DELETE FROM buildings

WHERE

building_no = 2;

第8步，从rooms表中查询数据：

SELECT * FROM rooms;

运行结果：

+---------+---------------+-------------+

| room_no | room_name | building_no |

+---------+---------------+-------------+

| 1 | Amazon | 1 |

| 2 | War Room | 1 |

| 3 | Office of CEO | 1 |

+---------+---------------+-------------+

3 rows in set (0.00 sec)

如您所见，所有引用building_no2 的行都  被删除了。

注意：ON DELETE CASCADE  仅适用于存储引擎支持外键的表，  例如InnoDB。某些表类型不支持MyISAM等外键，因此您应为计划使用MySQL ON DELETE CASCADE  引用操作的表选择适当的存储引擎  。

查找受MySQL ON DELETE CASCADE操作影响的表的提示

有时，ON DELETE CASCADE  当您从表中删除数据时，了解哪个表受MySQL 参照操作影响很有用  。您可以从查询数据referential_constraints的information_schema  数据库，如下所示：

USE information_schema;

SELECT

table_name

FROM

referential_constraints

WHERE

constraint_schema = 'database_name'

AND referenced_table_name = 'parent_table'

AND delete_rule = 'CASCADE'

例如，要查找与数据库mysqldemo中具有CASCADE  删除规则的表关联的表buildings，请使用以下查询：

USE information_schema;

SELECT

table_name

FROM

referential_constraints

WHERE

constraint_schema = 'classicmodels'

AND referenced_table_name = 'buildings'

AND delete_rule = 'CASCADE'

运行结果：

+------------+

| table_name |

+------------+

| rooms |

+------------+

1 row in set (0.12 sec)

在本教程中，我们逐步向您展示了如何在ON DELETE CASCADE  从父表中删除数据时，使用外部键的MySQL 引用操作从子表中自动删除数据。