MySQL外键类型及应用场景总结

目录

• 1、外键效果演示
• 1.1、创建和添加两张表数据
• 1.2、测试外键作用效果
• 1.2.1、RESTRICT
• 1.2.2、CASCADE
• 1.2.3、SET NULL
• 1.2.4、NO ACTION
• 1.3、外键作用描述以及优缺点总结
• 1.3.1、RESTRICT
• 1.3.2、CASCADE
• 1.3.3、SET NULL
• 1.3.4、NO ACTION（等价于 RESTRICT）
• 2、外键类型适用场景总结（表格）
• 3、外键于业务开发而言的优缺点
• 3.1、优点
• 3.2、缺点
• 4、外键的使用注意事项

前言： mysql的外键简介：在 MySQL 中，外键 (Foreign Key) 用于建立和强制表之间的关联，确保数据的一致性和完整性。外键的作用主要是限制和维护引用完整性 (Referential Integrity)。

• 主要体现在引用操作发生变化时的处理方式（即 ON DELETE 和 ON UPDATE 的行为）。
• 外键类型一共有四种： RESTRICT、 CASCADE 、 SET NULL 、 NO ACTION。接下来通过测试来演示各自的作用效果。

1、外键效果演示

1.1、创建和添加两张表数据

-- 创建父表
CREATE TABLE `users` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`user_id`)
);

-- 创建子表
CREATE TABLE `orders` (
  `order_id` INT NOT NULL AUTO_INCREMENT,
  `order_date` DATE NOT NULL,
  `user_id` INT,
  PRIMARY KEY (`order_id`)
);

-- 插入父表数据
INSERT INTO `users` (`username`) VALUES ('Alice');
INSERT INTO `users` (`username`) VALUES ('Bob');

-- 插入子表数据
INSERT INTO `orders` (`order_date`, `user_id`) VALUES ('2024-12-25', 1);
INSERT INTO `orders` (`order_date`, `user_id`) VALUES ('2024-12-26', 2);

1.2、测试外键作用效果

1.2.1、RESTRICT

• 创建RESTRICT外键

-- 添加外键约束到现有的子表 `orders`
ALTER TABLE `orders`
ADD CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

• 主表 删除和更新 已在 子表的外键中已存在 的数据

-- 删除已被引用的外键
DELETE FROM `users` WHERE `user_id` = 1

-- 输出结果 --
> 1451 - Cannot delete or update a parent row: a foreign key c编程客栈onstraint fails (`test`.`orders`, CONSTRAINT `fk_user_id` FjavascriptOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE jsRESTRICT)
> 查询时间: 0.013s


-- 修改已被引用的外键
UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 1

-- 输出结果 --
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
> 查询时间: 0.009s

• 查看子表变化

因为删除和更新都执行失败，所以子表没有变化。

总结：RESTRICT类型的外键，如果该记录在子表中有引用，禁止删除或更新父表中的记录。

1.2.2、CASCADE

• 创建CASCADE外键

-- 添加外键约束到 `orders` 表，使用 CASCADE
ALTER TABLE `orders`
ADD CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

• 主表 删除和更新 已在 子表的外键中已存在 的数据

-- 删除已被引用的外键
DELETE FROM `users` WHERE `user_id` = 1

-- 输出结果 --
> Affected rows: 1
> 查询时间: 0.016s


-- 修改已被引用的外键
UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 2

-- 输出结果 --
> Affected rows: 1
> 查询时间: 0.013s

• 查看子表变化

因为两条SQL都执行成功。order_id = 1的数据被删除，order_id = 2的user_id的值被修改为3

总结：CASCADE类型的外键，当父表中的记录被删除或更新时，子表中的相关记录也会自动被删除或更新。

1.2.3、SET NULL

• 创建SET NULL外键

-- 确保子表的外键列允许 NULL
ALTER TABLE `orders`
MODIFY COLUMN `user_id` INT NULL;

-- 添加外键约束到 `orders` 表，使用 SET NULL
ALTER TABLE `orders`
ADD CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE SET NULL
ON UPDATE SET NULL;
编程

• 主表 删除和更新 已在 子表的外键中已存在 的数据

-- 删除已被引用的外键
DELETE FROM `users` WHERE `user_id` = 1

-- 输出结果 --
> Affected rows: 1
> 查询时间: 0.014s


-- 修改已被引用的外键
UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 2

-- 输出结果 --
> Affected rows: 1
> 查询时间: 0.012s

• 查看子表变化

两条SQL都执行成功。order_id = 1的user_id的值变为NULL，order_id = 2的user_id的值变为NULL，

总结：SET NULL类型的外键，当父表记录被删除或更新时，子表中对应的外键值会更新为 NULL。

1.2.4、NO ACTION

• 创建NO ACTION外键

-- 添加外键约束，使用 NO ACTION
ALTER TABLE `orders`
ADD CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

• 主表 删除和更新 已在 子表的外键中已存在 的数据

-- 删除已被引用的外键
DELETE FROM `users` WHERE `user_id` = 1

-- 输出结果 --
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))
> 查询时间: 0.013s


-- 修改已被引甮的外键
UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 2

-- 输出结果 --
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))
> 查询时间: 0.025s

• 查看子表变化

因为删除和更新都执行失败，所以子表没有变化。

总结：NOpython ACTION类型的外键（和RESTRICT的作用相同），如果该记录在子表中有引用，禁止删除或更新父表中的记录。

1.3、外键作用描述以及优缺点总结

1.3.1、RESTRICT

• 描述：即父表记录在被子表引用时，无法被删除或更新。
• 适用场景：适合需要严格控制父表记录操作的场景。
• 优点：防止意外的数据丢失。
• 缺点：增加操作复杂性。

1.3.2、CASCADE

• 描述：级联操作。当父表中的记录被删除或更新时，子表中的相关记录也会自动被删除或更新。
• 适用场景：当子表记录与父表记录绑定紧密时，例如订单表和订单明细表。
• 优点：简化了复杂的删除或更新操作，自动维护数据一致性。
• 缺点：操作不当可能导致数据大量丢失或被误修改。

1.3.3、SET NULL

• 描述：当父表记录被删除或更新时，子表中对应的外键值会设置为 NULL。
• 适用场景：当子表的记录在父表记录删除后依然有意义时，外键列必须允许 NULL。
• 优点：保留了子表记录，同时删除或更新父表记录。
• 缺点：如果没有后续维护，可能导致孤立的数据。

1.3.4、NO ACTION（等价于 RESTRICT）

• 描述：禁止删除或更新父表中的记录，如果该记录在子表中有引用。
• 适用场景：强制父表记录必须首先解除子表中的关联。
• 优点：明确控制了数据的删除或更新，防止意外影响子表数据。
• 缺点：操作复杂性增加，要求开发者手动处理关联关系。

2、外键类型适用场景总结（表格）

3、外键于业务开发而言的优缺点

3.1、优点

• 数据完整性: 防止孤立记录，确保父表与子表之间的关联关系一致。
• 自动化处理: 配合 CASCADE 或 SET NULL，可以自动处理相关记录，减少手动操作的复杂性。
• 业务约束: 通过外键约束明确表间关系，增强业务逻辑的约束力。

3.2、缺点

• 性能开销: 外键约束会对插入、更新、删除操作产生额外的性能开销，尤其是在大量操作时。
• 操作复杂性: 需要对数据表操作进行规划，增加开发维护成本。
• 限制灵活性: 外键约束的存在可能限制某些业务操作，例如无法随意删除父表记录。

4、外键的使用注意事项

• 引擎限制: MySQL 的外键功能仅支持 InnoDB 存储引擎。
• 索引要求: 外键列和被引用列都必须建立索引（通常是主键或唯一键）。
• 规划数据关系: 在设计时需明确父表与子表之间的关系和操作逻辑，避免误操作。
• 性能考虑: 在高并发或大规模数据操作时，外键可能影响性能，需谨慎权衡。

以上就是MySQL外键类型及应用场景总结的详细内容，更多关于MySQL外键类型及应用的资料请关注编程客栈(www.devze.com)其它相关文章！