萬盛學電腦網

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

MySQL 外鍵建立完整指南:語法、陷阱與最佳實踐

前置條件:InnoDB 強制要求

  • 父表與子表均為 ENGINE=InnoDB
  • 引用欄位與被引用欄位資料型別、長度、字符集、校對規則 完全一致
  • 被引用欄位必須有索引 (主鍵/唯一鍵/普通索引),InnoDB 會自動建立

標準建表語法

-- 父表
CREATE TABLE `parent` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 子表:行內定義
CREATE TABLE `child` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` INT UNSIGNED NOT NULL,
  `data` VARCHAR(100),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 或 ALTER TABLE 追加
ALTER TABLE `child`
  ADD CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE;

關鍵選項詳解

選項行為適用場景
RESTRICT / NO ACTION拒絕父表刪除/更新預設,嚴格資料完整性
CASCADE級聯刪除/更新子表訂單-明細、分類-商品等強關聯
SET NULL子表外鍵設為 NULL外鍵欄位允許 NULL,弱關聯
SET DEFAULT設為預設值極少用,需預設值存在

常見錯誤與排查

  1. Errno 150 / 121:型別不匹配 (如 INT vs BIGINT)、字符集不同、父表無索引、表非 InnoDB
  2. 外鍵名衝突:Constraint 名稱在資料庫內唯一,建議 fk_子表_父表 命名
  3. 循環引用:表 A 引用 B,B 引用 A,需先建表後 ALTER TABLE 分別加
  4. 匯入失敗:mysqldump 匯出含外鍵,匯入時順序錯誤 → SET FOREIGN_KEY_CHECKS=0; 先匯入再開啟

效能與維護考量

  • 寫入開銷:每次 INSERT/UPDATE/DELETE 需檢查父表,高併發下可觀
  • 鎖擴大:外鍵檢查會在父表加共享鎖 (S-lock),大事務易死鎖
  • 備份/遷移:mysqldump --single-transaction --master-data=2 保證一致性
  • 線上變更:pt-online-schema-change 處理大表外鍵變更

現代架構建議

微服務/分庫分表架構下,跨服務/跨庫禁用資料庫層面外鍵,由應用層 (Saga/事件驅動) 保證最終一致性。單體應用核心域模型內,關鍵強一致性場景 (訂單-支付、庫存-扣減) 可保留外鍵,非核心關聯改用應用層校驗 + 定期對賬。

copyright © 萬盛學電腦網 all rights reserved