MySQL外鍵類型及應(yīng)用場景總結(jié)
更新時間：2024年12月27日 08:43:32 作者：四七伵
這篇文章主要介紹了?MySQL?外鍵的類型（RESTRICT、CASCADE、SET?NULL、NO?ACTION）及其應(yīng)用場景、優(yōu)缺點(diǎn)和使用注意事項,通過創(chuàng)建和測試外鍵,闡述了不同類型外鍵在主表刪除或更新數(shù)據(jù)時子表的變化,需要的朋友可以參考下
前言： MySQL的外鍵簡介：在 MySQL 中，外鍵 (Foreign Key) 用于建立和強(qiáng)制表之間的關(guān)聯(lián)，確保數(shù)據(jù)的一致性和完整性。外鍵的作用主要是限制和維護(hù)引用完整性 (Referential Integrity)。
- 主要體現(xiàn)在引用操作發(fā)生變化時的處理方式（即
ON DELETE和ON UPDATE的行為）。 - 外鍵類型一共有四種：
RESTRICT、CASCADE、SET NULL、NO ACTION。接下來通過測試來演示各自的作用效果。
1、外鍵效果演示
1.1、創(chuàng)建和添加兩張表數(shù)據(jù)
-- 創(chuàng)建父表
CREATE TABLE `users` (
`user_id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) NOT NULL,
PRIMARY KEY (`user_id`)
);
-- 創(chuàng)建子表
CREATE TABLE `orders` (
`order_id` INT NOT NULL AUTO_INCREMENT,
`order_date` DATE NOT NULL,
`user_id` INT,
PRIMARY KEY (`order_id`)
);
-- 插入父表數(shù)據(jù)
INSERT INTO `users` (`username`) VALUES ('Alice');
INSERT INTO `users` (`username`) VALUES ('Bob');
-- 插入子表數(shù)據(jù)
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
- 創(chuàng)建
RESTRICT外鍵
-- 添加外鍵約束到現(xiàn)有的子表 `orders` ALTER TABLE `orders` ADD CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
- 主表 刪除和更新 已在 子表的外鍵中已存在 的數(shù)據(jù)
-- 刪除已被引用的外鍵 DELETE FROM `users` WHERE `user_id` = 1 -- 輸出結(jié)果 -- > 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.013s -- 修改已被引用的外鍵 UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 1 -- 輸出結(jié)果 -- > 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
- 查看子表變化
因為刪除和更新都執(zhí)行失敗，所以子表沒有變化。
總結(jié)：RESTRICT類型的外鍵，如果該記錄在子表中有引用，禁止刪除或更新父表中的記錄。
1.2.2、CASCADE
- 創(chuàng)建
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;
- 主表 刪除和更新 已在 子表的外鍵中已存在 的數(shù)據(jù)
-- 刪除已被引用的外鍵 DELETE FROM `users` WHERE `user_id` = 1 -- 輸出結(jié)果 -- > Affected rows: 1 > 查詢時間: 0.016s -- 修改已被引用的外鍵 UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 2 -- 輸出結(jié)果 -- > Affected rows: 1 > 查詢時間: 0.013s
- 查看子表變化
因為兩條SQL都執(zhí)行成功。
order_id = 1的數(shù)據(jù)被刪除，order_id = 2的user_id的值被修改為3
總結(jié)：CASCADE類型的外鍵，當(dāng)父表中的記錄被刪除或更新時，子表中的相關(guān)記錄也會自動被刪除或更新。
1.2.3、SET NULL
- 創(chuàng)建
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;
- 主表 刪除和更新 已在 子表的外鍵中已存在 的數(shù)據(jù)
-- 刪除已被引用的外鍵 DELETE FROM `users` WHERE `user_id` = 1 -- 輸出結(jié)果 -- > Affected rows: 1 > 查詢時間: 0.014s -- 修改已被引用的外鍵 UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 2 -- 輸出結(jié)果 -- > Affected rows: 1 > 查詢時間: 0.012s
- 查看子表變化
兩條SQL都執(zhí)行成功。
order_id = 1的user_id的值變?yōu)?code>NULL，order_id = 2的user_id的值變?yōu)?code>NULL，
總結(jié)：SET NULL類型的外鍵，當(dāng)父表記錄被刪除或更新時，子表中對應(yīng)的外鍵值會更新為 NULL。
1.2.4、NO ACTION
- 創(chuàng)建
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;
- 主表 刪除和更新 已在 子表的外鍵中已存在 的數(shù)據(jù)
-- 刪除已被引用的外鍵 DELETE FROM `users` WHERE `user_id` = 1 -- 輸出結(jié)果 -- > 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 -- 輸出結(jié)果 -- > 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
- 查看子表變化
因為刪除和更新都執(zhí)行失敗，所以子表沒有變化。
總結(jié)：NO ACTION類型的外鍵（和RESTRICT的作用相同），如果該記錄在子表中有引用，禁止刪除或更新父表中的記錄。
1.3、外鍵作用描述以及優(yōu)缺點(diǎn)總結(jié)
1.3.1、RESTRICT
- 描述：即父表記錄在被子表引用時，無法被刪除或更新。
- 適用場景：適合需要嚴(yán)格控制父表記錄操作的場景。
- 優(yōu)點(diǎn)：防止意外的數(shù)據(jù)丟失。
- 缺點(diǎn)：增加操作復(fù)雜性。
1.3.2、CASCADE
- 描述：級聯(lián)操作。當(dāng)父表中的記錄被刪除或更新時，子表中的相關(guān)記錄也會自動被刪除或更新。
- 適用場景：當(dāng)子表記錄與父表記錄綁定緊密時，例如訂單表和訂單明細(xì)表。
- 優(yōu)點(diǎn)：簡化了復(fù)雜的刪除或更新操作，自動維護(hù)數(shù)據(jù)一致性。
- 缺點(diǎn)：操作不當(dāng)可能導(dǎo)致數(shù)據(jù)大量丟失或被誤修改。
1.3.3、SET NULL
- 描述：當(dāng)父表記錄被刪除或更新時，子表中對應(yīng)的外鍵值會設(shè)置為
NULL。 - 適用場景：當(dāng)子表的記錄在父表記錄刪除后依然有意義時，外鍵列必須允許
NULL。 - 優(yōu)點(diǎn)：保留了子表記錄，同時刪除或更新父表記錄。
- 缺點(diǎn)：如果沒有后續(xù)維護(hù)，可能導(dǎo)致孤立的數(shù)據(jù)。
1.3.4、NO ACTION（等價于 RESTRICT）
- 描述：禁止刪除或更新父表中的記錄，如果該記錄在子表中有引用。
- 適用場景：強(qiáng)制父表記錄必須首先解除子表中的關(guān)聯(lián)。
- 優(yōu)點(diǎn)：明確控制了數(shù)據(jù)的刪除或更新，防止意外影響子表數(shù)據(jù)。
- 缺點(diǎn)：操作復(fù)雜性增加，要求開發(fā)者手動處理關(guān)聯(lián)關(guān)系。
2、外鍵類型適用場景總結(jié)（表格）
| 外鍵類型 | 適用場景 | 注意事項 |
|---|---|---|
| CASCADE | 父子關(guān)系強(qiáng)關(guān)聯(lián)，父表刪除或更新后子表無條件跟隨。 | 謹(jǐn)慎使用，避免誤刪除或誤更新。 |
| SET NULL | 子表記錄在父表刪除或更新后仍有意義，允許外鍵列為 NULL。 | 子表的外鍵列必須允許 NULL，需謹(jǐn)防數(shù)據(jù)孤立。 |
| NO ACTION / RESTRICT | 強(qiáng)制要求父表記錄的刪除或更新必須先解除子表關(guān)聯(lián)。 | 增加了操作復(fù)雜性，但能嚴(yán)格保護(hù)數(shù)據(jù)完整性。 |
3、外鍵于業(yè)務(wù)開發(fā)而言的優(yōu)缺點(diǎn)
3.1、優(yōu)點(diǎn)
- 數(shù)據(jù)完整性: 防止孤立記錄，確保父表與子表之間的關(guān)聯(lián)關(guān)系一致。
- 自動化處理: 配合 CASCADE 或 SET NULL，可以自動處理相關(guān)記錄，減少手動操作的復(fù)雜性。
- 業(yè)務(wù)約束: 通過外鍵約束明確表間關(guān)系，增強(qiáng)業(yè)務(wù)邏輯的約束力。
3.2、缺點(diǎn)
- 性能開銷: 外鍵約束會對插入、更新、刪除操作產(chǎn)生額外的性能開銷，尤其是在大量操作時。
- 操作復(fù)雜性: 需要對數(shù)據(jù)表操作進(jìn)行規(guī)劃，增加開發(fā)維護(hù)成本。
- 限制靈活性: 外鍵約束的存在可能限制某些業(yè)務(wù)操作，例如無法隨意刪除父表記錄。
4、外鍵的使用注意事項
- 引擎限制: MySQL 的外鍵功能僅支持 InnoDB 存儲引擎。
- 索引要求: 外鍵列和被引用列都必須建立索引（通常是主鍵或唯一鍵）。
- 規(guī)劃數(shù)據(jù)關(guān)系: 在設(shè)計時需明確父表與子表之間的關(guān)系和操作邏輯，避免誤操作。
- 性能考慮: 在高并發(fā)或大規(guī)模數(shù)據(jù)操作時，外鍵可能影響性能，需謹(jǐn)慎權(quán)衡。
以上就是MySQL外鍵類型及應(yīng)用場景總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySQL外鍵類型及應(yīng)用的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL 8.0 之索引跳躍掃描(Index Skip Scan)
這篇文章主要介紹了MySQL 8.0 之索引跳躍掃描(Index Skip Scan)的相關(guān)資料,幫助大家學(xué)習(xí)MySQL8.0的新特性，感興趣的朋友可以了解下2020-10-10
這篇文章主要介紹了mysql臨時表插入數(shù)據(jù)方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-09-09
解決MySQL innoDB間隙鎖產(chǎn)生的死鎖問題
線上經(jīng)常偶發(fā)死鎖問題,當(dāng)時處理一張表,也沒有聯(lián)表處理,但是有兩個mq入口,并且消息體存在一樣的情況,但是是偶發(fā)的,又模擬不出來什么場景會導(dǎo)致死鎖,只能進(jìn)行代碼分析,問題還原的方式去排查問題,本文給大家介紹了如何解決MySQL innoDB間隙鎖產(chǎn)生的死鎖問題2023-10-10
CentOs7安裝部署Sonar環(huán)境的詳細(xì)過程(JDK1.8+MySql5.7+sonarqube7.8)
這篇文章主要介紹了CentOs7安裝部署Sonar環(huán)境(JDK1.8+MySql5.7+sonarqube7.8),本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-06-06
mysql踩坑之limit與sum函數(shù)混合使用問題詳解
這篇文章主要給大家介紹了關(guān)于mysql踩坑之limit與sum函數(shù)混合使用問題的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用mysql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-06-06
這篇文章主要介紹了mysql的innodb和myisam的區(qū)別及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-03-03
MYSQL的慢SQL優(yōu)化的實(shí)現(xiàn)
本文主要介紹了MYSQL的慢SQL優(yōu)化的實(shí)現(xiàn),從慢SQL定義、影響、常見場景與危害、識別與監(jiān)控、分析、優(yōu)化方法、執(zhí)行計劃分析、案例分析、總結(jié)與最佳實(shí)踐、常見優(yōu)化誤區(qū)、日常開發(fā)預(yù)防措施等方面詳細(xì)闡述,感興趣的可以了解一下2026-05-05
通常我們在使用MySQL時,我們看到的只是輸入一條語句,返回一個結(jié)果,卻不知道這條語句在MySQL內(nèi)部的執(zhí)行過程,這篇文章主要給大家介紹了關(guān)于MySQL中一條查詢SQL語句的完整執(zhí)行流程,需要的朋友可以參考下2024-05-05
MySQL SELECT?...for?update的具體使用
本文主要介紹了MySQL的SELECT?...for?update的具體使用，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05
MySQL如何創(chuàng)建可以遠(yuǎn)程訪問的root賬戶詳解
作為MySQL數(shù)據(jù)庫管理員,創(chuàng)建遠(yuǎn)程用戶并設(shè)置相應(yīng)的權(quán)限是一項常見的任務(wù),下面這篇文章主要給大家介紹了關(guān)于MySQL如何創(chuàng)建可以遠(yuǎn)程訪問的root賬戶的相關(guān)資料,需要的朋友可以參考下2024-04-04