您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

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

編輯：MySQL 資料庫相關知識

為什麼在資料庫層面做級聯？

將參考完整性邏輯下沉至資料庫引擎（InnoDB），可獲得：

原子性保證：父子異動在同一事務內完成，避免應用層崩潰導致孤兒資料。
程式碼簡化：應用無需手寫多表 DELETE/UPDATE，減少 Bug 面。
效能優勢：引擎內部直接操作索引頁，比應用層多次往返快。
統一強制：任何存取路徑（應用、ETL、手工 SQL、備份還原）均受約束。

範例場景：部落格文章與留言

父表 blogs、子表 comments，一對多關係。

建表語句（支援級聯更新 + 級聯刪除）

CREATE TABLE `blogs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` TEXT NOT NULL,
  `content` TEXT,
  `author` VARCHAR(45),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `comments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `blog_id` INT UNSIGNED NOT NULL,
  `comment` TEXT NOT NULL,
  `author` VARCHAR(45),
  PRIMARY KEY (`id`),
  INDEX `idx_blog_id` (`blog_id`),
  CONSTRAINT `fk_comments_blog` 
    FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

級聯更新演示

-- 插入測試資料
INSERT INTO blogs (title, content, author) VALUES ('First Post', 'Hello World', 'Tom');
INSERT INTO comments (blog_id, comment, author) VALUES 
  (1, 'Great!', 'Susan'), (1, 'Thanks', 'Rose');

-- 將 blog id 從 1 改為 2（極少見，但展示機制）
UPDATE blogs SET id = 2 WHERE id = 1;
-- 觸發 ON UPDATE CASCADE，comments.blog_id 自動從 1 → 2
SELECT * FROM comments;  -- 兩筆 blog_id 均為 2

注意：生產環境極少更新 PK，此處僅示範機制。實務上 PK 不變，級聯更新多用於自然鍵作 FK 參考的場景（如 username 變更同步更新關聯表）。

級聯刪除演示

-- 刪除部落格文章
DELETE FROM blogs WHERE id = 2;
-- 觸發 ON DELETE CASCADE，關聯留言自動刪除
SELECT * FROM comments;  -- 空結果

單條 DELETE 即完成父子清理，無需應用層手動 DELETE FROM comments WHERE blog_id=2。

對比：無外鍵約束（MyISAM）的痛點

-- MyISAM 不支援 FK，需應用層雙刪
DELETE FROM blogs WHERE id = 1;
DELETE FROM comments WHERE blog_id = 1;
-- 風險：中間崩潰、並發競態、漏刪、權限不足導致部分成功

進階選項：ON UPDATE/DELETE 行為選擇表

選項	父表動作	子表反應	適用場景
CASCADE	UPDATE/DELETE	同步更新/刪除	強生命週期綁定（訂單-明細、文章-留言）
SET NULL	UPDATE/DELETE	FK 欄位設 NULL	子表生命週期獨立（員工-部門、文章-作者）
RESTRICT / NO ACTION	UPDATE/DELETE	拋錯阻止	嚴禁刪除父表（主檔保護）、需手工清理子表
SET DEFAULT	UPDATE/DELETE	FK 欄位設預設值	極少用，MySQL 解析但 InnoDB 拒絕執行

效能與運維考量

大量刪除父表會觸發大量子表級聯刪除，產生大事務、長鎖定、Binlog 膨脹。建議分批刪除（如每批 500~1000 行）或使用 pt-archiver 歸檔。
外鍵索引自動建立：InnoDB 會在 FK 欄位自動建立索引；若已有索引則複用，避免重複。
死鎖風險：並發 UPDATE 父表 PK + 子表 FK 可能死鎖；應用層統一按固定順序（父→子）操作，或降低隔離級別至 READ COMMITTED + innodb_locks_unsafe_for_binlog=1（慎用）。
工具支援：mysqldump --single-transaction、pt-online-schema-change、gh-ost 均完整支援 FK 約束。