萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 外鍵約束實戰:從語法到級聯刪除的完整指南

前置條件:必須使用 InnoDB

MyISAM 不支援外鍵。建表時指定 ENGINE=InnoDB,或既有表轉換:

ALTER TABLE `t` ENGINE=InnoDB;

外鍵語法全解析

[CONSTRAINT `fk_name`] FOREIGN KEY [`idx_name`] (`col1`, `col2`, ...)
REFERENCES `parent_tbl` (`ref_col1`, `ref_col2`, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

四大引用動作對照

動作父表刪除/更新時,子表行為典型場景
RESTRICT / NO ACTION阻止操作,拋錯核心主數據,嚴禁孤兒行
CASCADE同步刪除/更新子表對應行訂單-明細、商品-圖片等強依賴
SET NULL子表外鍵列置 NULL(需允許 NULL)用戶-文章,用戶刪除後文章歸屬「匿名」

建立外鍵的四個硬性條件

  1. 兩表皆為 InnoDB,非臨時表。
  2. 對應列資料類型、長度、字元集、排序規則 完全一致(含 UNSIGNED 屬性)。
  3. 父表被引用列需有索引(主鍵/唯一索引/普通索引均可);子表外鍵列會自動建立索引。
  4. 若顯式命名 CONSTRAINT fk_xxx,名稱在庫內唯一;否則 InnoDB 自動生成。

實戰案例:商品主表與圖片從表級聯刪除

-- 1. 父表
CREATE TABLE `products` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

-- 2. 子表(先建索引再加外鍵)
CREATE TABLE `product_images` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `url` VARCHAR(255) NOT NULL,
  INDEX `idx_product_id` (`product_id`)
) ENGINE=InnoDB;

-- 3. 加外鍵(級聯刪除)
ALTER TABLE `product_images`
ADD CONSTRAINT `fk_img_product`
FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE;

常見踩坑與對策

現象原因解決
ERROR 1215: Cannot add foreign key constraint類型不一致(如一邊 UNSIGNED 一邊無)SHOW CREATE TABLE 逐項對齊
加外鍵極慢/鎖表子表有大量數據,需驗證每行低峰期操作、分批導入、或先 DISABLE KEYS
死鎖多表級聯形成環、並發高避免環狀引用、縮短事務、按固定順序加鎖
備份還原報錯mysqldump 預設不導出外鍵定義加 --single-transaction --routines --triggers,還原時暫時 SET FOREIGN_KEY_CHECKS=0

何時不用外鍵?

  • 極高併發寫入(外鍵檢查增加鎖競爭)。
  • 微服務架構下跨庫引用(無法建立外鍵)。
  • 業務邏輯極其複雜,需應用層靈活控制(如軟刪除、歸檔)。

此時改用「應用層級聯」或「異步補償(MQ/定時任務)」保證最終一致性。

總結

外鍵是資料庫層面的「契約」,用得好能大幅減少應用代碼、保證強一致性;用不好會成效能殺手。遵循「類型完全一致、父表有索引、明確選擇 CASCADE/SET NULL」三原則,並在壓測中驗證鎖競爭,才能放心落地。

copyright © 萬盛學電腦網 all rights reserved