MySQL 级联删除

MySQL 中的 ON DELETE CASCADE 子句用于在我们从父表中删除行时自动从子表中删除匹配的记录。它是一种与外键相关的引用动作。

假设我们在外键关系中创建了两个具有 FOREIGN KEY 的表，使这两个表都成为父表和子表。接下来，我们为一个 FOREIGN KEY 定义一个 ON DELETE CASCADE 子句，必须为另一个 FOREIGN KEY 设置该子句才能在级联操作中成功。如果仅为一个 FOREIGN KEY 子句定义 ON DELETE CASCADE，则级联操作将引发错误。

一、MySQL ON DELETE CASCADE 示例

让我们了解如何在 MySQL 表中使用 ON DELETE CASCADE 子句。首先，我们将创建两个名为Employee 和 Payment的表。这两个表通过外键与 on delete 级联操作相关联。这里，Employee 是父表，Payment 是子表。以下脚本将创建两个表及其记录。

employee表：

以下语句创建一个表 Employee：

CREATE TABLE Employee (  
  emp_id int(10) NOT NULL,  
  name varchar(40) NOT NULL,  
  birthdate date NOT NULL,  
  gender varchar(10) NOT NULL,  
  hire_date date NOT NULL,  
  PRIMARY KEY (emp_id)  
);  

接下来，执行插入查询以填充记录。

INSERT INTO Employee (emp_id, name, birthdate, gender, hire_date) VALUES  
(101, 'Bryan', '1988-08-12', 'M', '2015-08-26'),  
(102, 'Joseph', '1978-05-12', 'M', '2014-10-21'),  
(103, 'Mike', '1984-10-13', 'M', '2017-10-28'),  
(104, 'Daren', '1979-04-11', 'M', '2006-11-01'),  
(105, 'Marie', '1990-02-11', 'F', '2018-10-12');  

执行SELECT 查询，将数据验证成表格，如下图所示：

payment表：

下面的语句创建一个付款表：

CREATE TABLE Payment (  
  payment_id int(10) PRIMARY KEY NOT NULL,  
  emp_id int(10) NOT NULL,  
  amount float NOT NULL,  
  payment_date date NOT NULL,  
  FOREIGN KEY (emp_id) REFERENCES Employee (emp_id) ON DELETE CASCADE  
);  

接下来，执行插入语句将记录填充到表中。

INSERT INTO Payment (payment_id, emp_id, amount, payment_date) VALUES   
(301, 101, 1200, '2015-09-15'),  
(302, 101, 1200, '2015-09-30'),  
(303, 101, 1500, '2015-10-15'),  
(304, 101, 1500, '2015-10-30'),  
(305, 102, 1800, '2015-09-15'),  
(306, 102, 1800, '2015-09-30');  

执行 SELECT 查询，将数据校验成表，如下图所示：

让我们从父表 Employee 中删除数据。为此，请执行以下语句：

mysql> DELETE FROM Employee WHERE emp_id = 102;  

上述语句将删除emp_id = 102的员工记录，并将数据引用到子表中。我们可以使用 SELECT 语句验证数据，该语句将提供以下输出：

在上面的输出中，我们可以看到所有引用 emp_id = 102 的行都被自动从两个表中删除。

二、如何通过 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'  

下面的语句在employeedb数据库中使用ON DELETE CASCADE 规则生成与Employee 表关联的表的结果：

USE information_schema;  
  
SELECT table_name FROM referential_constraints  
WHERE constraint_schema = 'employeedb'  
        AND referenced_table_name = 'Employee'  
        AND delete_rule = 'CASCADE';  

执行上述命令后，我们将得到以下输出：

三、MySQL 更新级联

MySQL中的 ON UPDATE CASCADE 子句，用于在我们更新父表中的行时自动更新子表中的匹配记录。下面的例子解释得更清楚。

首先，我们需要使用ALTER TABLE语句在表 Payment 中添加 ON UPDATE CASCADE 子句，如下所示：

ALTER TABLE Payment ADD CONSTRAINT `payment_fk`   
FOREIGN KEY(emp_id) REFERENCES Employee (emp_id) ON UPDATE CASCADE;  

它将给出以下输出：

在下面的脚本中，我们将更新父表中员工的 id，它也会自动在子表中反映此更改：

mysql> UPDATE Employee SET emp_id = 102 WHERE emp_id = 103;  

验证 Employee 和 Payment 表的内容，我们将看到emp_id列的值将成功更新。