MySQL ON DELETE CASCADE：自动删除相关表中的数据

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

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

MySQL `ON DELETE CASCADE`示例

让我们看一下使用 MySQL ON DELETE CASCADE的示例。

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

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

当您从buildings表中buildings一行时，您还需要删除rooms表中引用该行的所有行。例如，当您删除具有建筑物编号的行时。 2. 在buildings表中查询如下：

DELETE FROM buildings 
WHERE building_no = 2;Code language: SQL (Structured Query Language) (sql)

您还希望rooms表中引用 2 号楼的行也将被删除。

以下步骤演示了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
);Code language: SQL (Structured Query Language) (sql)

步骤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
);Code language: SQL (Structured Query Language) (sql)

请注意外键约束定义末尾的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');Code language: SQL (Structured Query Language) (sql)

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

SELECT * FROM buildings;Code language: SQL (Structured Query Language) (sql)

buildings表中有两行。

步骤5 。将行插入rooms表中：

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Showroom',2);Code language: SQL (Structured Query Language) (sql)

步骤6 。从rooms表中查询数据：

SELECT * FROM rooms;Code language: SQL (Structured Query Language) (sql)

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

步骤7 。删除建筑物编号为的建筑物。 2：

DELETE FROM buildings 
WHERE building_no = 2;Code language: SQL (Structured Query Language) (sql)

步骤8 。从rooms表查询数据：

SELECT * FROM rooms;Code language: SQL (Structured Query Language) (sql)

正如您所看到的，所有引用building_no 2 的行都被自动删除。

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

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

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

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

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'database_name'
        AND referenced_table_name = 'parent_table'
        AND delete_rule = 'CASCADE'Code language: SQL (Structured Query Language) (sql)

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

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'classicmodels'
        AND referenced_table_name = 'buildings'
        AND delete_rule = 'CASCADE'Code language: SQL (Structured Query Language) (sql)

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

本教程有帮助吗？

MySQL ON DELETE CASCADE示例

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

MySQL `ON DELETE CASCADE`示例

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