前言： MySQL的外鍵簡(jiǎn)介：在 MySQL 中，外鍵 (Foreign Key) 用于建立和強(qiáng)制表之間的關(guān)聯(lián)，確保數(shù)據(jù)的一致性和完整性。外鍵的作用主要是限制和維護(hù)引用完整性 (Referential Integrity)。

• 主要體現(xiàn)在引用操作發(fā)生變化時(shí)的處理方式（即 ON DELETE 和 ON UPDATE 的行為）。
• 外鍵類型一共有四種： RESTRICT、 CASCADE 、 SET NULL 、 NO ACTION。接下來(lái)通過測(cè)試來(lái)演示各自的作用效果。

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、測(cè)試外鍵作用效果

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)
> 查詢時(shí)間: 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)
> 查詢時(shí)間: 0.009s

• 查看子表變化

因?yàn)閯h除和更新都執(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
> 查詢時(shí)間: 0.016s


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

-- 輸出結(jié)果 --
> Affected rows: 1
> 查詢時(shí)間: 0.013s

• 查看子表變化

因?yàn)閮蓷lSQL都執(zhí)行成功。order_id = 1的數(shù)據(jù)被刪除，order_id = 2的user_id的值被修改為3

總結(jié)：CASCADE類型的外鍵，當(dāng)父表中的記錄被刪除或更新時(shí)，子表中的相關(guān)記錄也會(huì)自動(dòng)被刪除或更新。

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
> 查詢時(shí)間: 0.014s


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

-- 輸出結(jié)果 --
> Affected rows: 1
> 查詢時(shí)間: 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)父表記錄被刪除或更新時(shí)，子表中對(duì)應(yīng)的外鍵值會(huì)更新為 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`))
> 查詢時(shí)間: 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`))
> 查詢時(shí)間: 0.025s

• 查看子表變化

因?yàn)閯h除和更新都執(zhí)行失敗，所以子表沒有變化。

總結(jié)：NO ACTION類型的外鍵（和RESTRICT的作用相同），如果該記錄在子表中有引用，禁止刪除或更新父表中的記錄。

1.3、外鍵作用描述以及優(yōu)缺點(diǎn)總結(jié)

1.3.1、RESTRICT

• 描述：即父表記錄在被子表引用時(shí)，無(wú)法被刪除或更新。
• 適用場(chǎng)景：適合需要嚴(yán)格控制父表記錄操作的場(chǎng)景。
• 優(yōu)點(diǎn)：防止意外的數(shù)據(jù)丟失。
• 缺點(diǎn)：增加操作復(fù)雜性。

1.3.2、CASCADE

• 描述：級(jí)聯(lián)操作。當(dāng)父表中的記錄被刪除或更新時(shí)，子表中的相關(guān)記錄也會(huì)自動(dòng)被刪除或更新。
• 適用場(chǎng)景：當(dāng)子表記錄與父表記錄綁定緊密時(shí)，例如訂單表和訂單明細(xì)表。
• 優(yōu)點(diǎn)：簡(jiǎn)化了復(fù)雜的刪除或更新操作，自動(dòng)維護(hù)數(shù)據(jù)一致性。
• 缺點(diǎn)：操作不當(dāng)可能導(dǎo)致數(shù)據(jù)大量丟失或被誤修改。

1.3.3、SET NULL

• 描述：當(dāng)父表記錄被刪除或更新時(shí)，子表中對(duì)應(yīng)的外鍵值會(huì)設(shè)置為 NULL。
• 適用場(chǎng)景：當(dāng)子表的記錄在父表記錄刪除后依然有意義時(shí)，外鍵列必須允許 NULL。
• 優(yōu)點(diǎn)：保留了子表記錄，同時(shí)刪除或更新父表記錄。
• 缺點(diǎn)：如果沒有后續(xù)維護(hù)，可能導(dǎo)致孤立的數(shù)據(jù)。

1.3.4、NO ACTION（等價(jià)于 RESTRICT）

• 描述：禁止刪除或更新父表中的記錄，如果該記錄在子表中有引用。
• 適用場(chǎng)景：強(qiáng)制父表記錄必須首先解除子表中的關(guān)聯(lián)。
• 優(yōu)點(diǎn)：明確控制了數(shù)據(jù)的刪除或更新，防止意外影響子表數(shù)據(jù)。
• 缺點(diǎn)：操作復(fù)雜性增加，要求開發(fā)者手動(dòng)處理關(guān)聯(lián)關(guān)系。

2、外鍵類型適用場(chǎng)景總結(jié)（表格）

3、外鍵于業(yè)務(wù)開發(fā)而言的優(yōu)缺點(diǎn)

3.1、優(yōu)點(diǎn)

• 數(shù)據(jù)完整性: 防止孤立記錄，確保父表與子表之間的關(guān)聯(lián)關(guān)系一致。
• 自動(dòng)化處理: 配合 CASCADE 或 SET NULL，可以自動(dòng)處理相關(guān)記錄，減少手動(dòng)操作的復(fù)雜性。
• 業(yè)務(wù)約束: 通過外鍵約束明確表間關(guān)系，增強(qiáng)業(yè)務(wù)邏輯的約束力。

3.2、缺點(diǎn)

• 性能開銷: 外鍵約束會(huì)對(duì)插入、更新、刪除操作產(chǎn)生額外的性能開銷，尤其是在大量操作時(shí)。
• 操作復(fù)雜性: 需要對(duì)數(shù)據(jù)表操作進(jìn)行規(guī)劃，增加開發(fā)維護(hù)成本。
• 限制靈活性: 外鍵約束的存在可能限制某些業(yè)務(wù)操作，例如無(wú)法隨意刪除父表記錄。

4、外鍵的使用注意事項(xiàng)

• 引擎限制: MySQL 的外鍵功能僅支持 InnoDB 存儲(chǔ)引擎。
• 索引要求: 外鍵列和被引用列都必須建立索引（通常是主鍵或唯一鍵）。
• 規(guī)劃數(shù)據(jù)關(guān)系: 在設(shè)計(jì)時(shí)需明確父表與子表之間的關(guān)系和操作邏輯，避免誤操作。
• 性能考慮: 在高并發(fā)或大規(guī)模數(shù)據(jù)操作時(shí)，外鍵可能影響性能，需謹(jǐn)慎權(quán)衡。

以上就是MySQL外鍵類型及應(yīng)用場(chǎng)景總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySQL外鍵類型及應(yīng)用的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論