MySQL ON DELETE CASCADE(级联删除)[猿教程]

转载地址: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)，如下面的数据库图所示：

当您从 building 表中删除一行时，您还希望删除 rooms 表中引用 building 表中的行的所有行。例如，当您删除具有建筑物编号的行时。 2在buildings表中作为以下查询：

DELETE FROM buildings 
WHERE building_no = 2;

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

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

步骤 1. 创建building表：

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;

步骤 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;

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

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

DELETE FROM buildings 
WHERE building_no = 2;

步骤 8. 从rooms表中查询数据：

SELECT * FROM rooms;

如您所见，所有引用 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'

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

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 外键引用操作，以便在您从父表中删除数据时自动从子表中删除数据。