您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 外鍵約束深度解析：從 MyISAM 手動維護到 InnoDB 級聯自動化

編輯：MySQL 綜合教學

背景痛點：MyISAM 時代的關聯維護

博客系統：blogs (文章) 1:N comments (評論)。MyISAM 不支援外鍵，應用層必須手動處理：

刪除文章 → 應用層先刪除關聯評論。
更新文章 ID → 應用層同步更新評論 blog_id。
邏輯分散、易出錯、併發下難保證一致性。

InnoDB 外鍵約束：資料庫層面的自動化守護

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

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

核心動作選項詳解

子句	父表操作	子表自動響應
`ON UPDATE CASCADE`	更新 PK	同步更新 FK
`ON DELETE CASCADE`	刪除行	級聯刪除關聯行
`ON UPDATE SET NULL`	更新 PK	FK 置 NULL (需 FK 允許 NULL)
`ON DELETE SET NULL`	刪除行	FK 置 NULL
`ON UPDATE RESTRICT / NO ACTION`	更新 PK	阻止操作，拋錯誤
`ON DELETE RESTRICT / NO ACTION`	刪除行	阻止操作，拋錯誤 (預設)

實戰演示

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

-- 更新文章 ID：CASCADE 自動同步 comments.blog_id
UPDATE blogs SET id = 2 WHERE id = 1;
-- comments 表 blog_id 自動變為 2，無需應用層介入

-- 刪除文章：CASCADE 自動刪除評論
DELETE FROM blogs WHERE id = 2;
-- 關聯評論自動消失

效能與權衡

優點：資料完整性強保證、應用層簡化、ACID 合規。
缺點：InnoDB 比 MyISAM 寫入慢 (鎖、日志、簇表維護)；大批量刪除/更新可能引發鎖爭用、主從延遲。
建議：
- 核心業務 (訂單、帳戶、庫存) 必用外鍵。
- 高寫入日志、埋點、非關鍵關聯可應用層維護或軟刪除。
- 大量級聯操作分批執行，避免長事務鎖表。

MyISAM 5.7+ 也支援外鍵語法但不生效

MyISAM 僅解析語法不執行約束，務必使用 InnoDB。

PHP PDO 範例：簡化應用層

$pdo = new PDO('mysql:host=localhost;dbname=blog;charset=utf8', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $pdo->beginTransaction();
    $pdo->exec("DELETE FROM blogs WHERE id = 1"); -- 外鍵自動清理 comments
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage();
}