MySQL 外鍵級聯刪除與更新完整實戰指南

前置條件

• 存儲引擎**必須為 InnoDB**（MyISAM 不支持外鍵）。
• 父表被引用列必須有索引（主鍵/唯一鍵/普通索引均可）。
• 子表外鍵列自動創建索引（5.6+）。

外鍵動作選項對比

完整建表腳本

CREATE DATABASE IF NOT EXISTS fk_demo CHARSET utf8mb4;
USE fk_demo;

-- 父表：用戶主檔
CREATE TABLE `user` (
  `id` INT(4) NOT NULL,
  `sex` ENUM('f','m') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 子表：用戶擴展信息
CREATE TABLE `userinfo` (
  `sn` INT(4) NOT NULL AUTO_INCREMENT,
  `userid` INT(4) NOT NULL,
  `info` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`sn`),
  KEY `idx_userid` (`userid`),
  CONSTRAINT `fk_userinfo_user` FOREIGN KEY (`userid`) REFERENCES `user` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

測試數據準備

INSERT INTO `user` (`id`,`sex`) VALUES (1,'f'), (2,'m'), (3,'f');

INSERT INTO `userinfo` (`userid`,`info`) VALUES 
(1,'2005054dsf'), (1,'fdsfewfdsfds'), (1,'gdsgergergrtre'),
(2,'et34t5435435werwe'), (2,'435rtgtrhfghfg'), (2,'ret345tr4345'),
(3,'fgbdfvbcbfdgr'), (3,'45r2343234were'), (3,'wfyhtyjtyjyjy');

級聯刪除驗證

-- 刪除用戶 2
DELETE FROM `user` WHERE `id` = 2;

-- 驗證父表
SELECT * FROM `user`;  -- 僅剩 1,3

-- 驗證子表：userid=2 的 3 條記錄自動刪除
SELECT * FROM `userinfo`;  -- 僅剩 userid=1,3 的 6 條

級聯更新驗證

-- 將用戶 1 的 id 改為 4
UPDATE `user` SET `id` = 4 WHERE `id` = 1;

-- 驗證父表
SELECT * FROM `user`;  -- id 變為 3,4

-- 驗證子表：userid 自動從 1 更新為 4
SELECT * FROM `userinfo`;  -- userid 變為 3,4，數據完整保留

生產環境避坑指南

1. 大表慎用 CASCADE：刪除父表 1 萬行可能觸發子表百萬行級聯刪除，導致長事務、鎖升級、主從延遲。建議：
   → 應用層先清理子表 → 再刪父表，或採用軟刪除 (is_deleted)。
2. 外鍵索引必須顯式聲明：雖然 5.6+ 自動創建，但建議在建表時顯式 KEY idx_fk (fk_col)，便於審計與跨版本兼容。
3. 避免循環引用：Table A → Table B → Table A 會導致死鎖或級聯風暴。
4. 主從同步延遲風險：主庫級聯操作單事務提交，從庫 SQL 線程串行回放，大事務會顯著增加 Seconds_Behind_Master。考慮 slave_parallel_workers + slave_preserve_commit_order=1 並行回放。
5. 外鍵約束名全局唯一：CONSTRAINT fk_userinfo_user 命名規範：fk_子表_父表，避免 ERROR 1005 (HY000): Can't create table ... errno: 121。

替代方案：應用層維護引用完整性

高併發、大數據量場景常放棄 DB 級外鍵，改由應用層保證：

• 事務中先寫子表再寫父表（或反向），失敗回滾。
• 定期跑一致性檢查作業：SELECT * FROM child LEFT JOIN parent ON child.fk=parent.pk WHERE parent.pk IS NULL。
• 軟刪除 + 異步清理：UPDATE parent SET deleted=1 WHERE id=?，夜間批量清理孤兒子表數據。

總結

MySQL InnoDB 外鍵級聯功能完整且標準，**適用於中小表、強一致性要求高、寫入頻率低** 的場景。大表高併發建議應用層控制，犧牲部分自動化換取可控的性能與穩定性。