資料唯一性保障是資料庫設計的核心課題。MySQL 提供多種防重機制，本文深度解析 INSERT ... WHERE NOT EXISTS 模式、INSERT IGNORE、ON DUPLICATE KEY UPDATE 等原生方案，並探討應用層協同策略。

方案一：標準 SQL 模式 - INSERT ... SELECT ... WHERE NOT EXISTS

適用於跨表資料遷移、批量匯入去重等場景，符合 ANSI SQL 標準，可移植性最強。

批量插入去重範例

INSERT INTO `clients` (`client_id`, `client_name`, `client_type`)
SELECT `supplier_id`, `supplier_name`, 'advertising'
FROM `suppliers`
WHERE NOT EXISTS (
    SELECT 1 FROM `clients` 
    WHERE `clients`.`client_id` = `suppliers`.`supplier_id`
);

單筆插入去重範例

INSERT INTO `clients` (`client_id`, `client_name`, `client_type`)
SELECT 10345, 'IBM', 'advertising'
FROM `dual`
WHERE NOT EXISTS (
    SELECT 1 FROM `clients` WHERE `client_id` = 10345
);

關鍵點：使用虛擬表 dual 使 SELECT 可直接帶入常值，無需實體來源表。

方案二：MySQL 專用擴展 - INSERT IGNORE / ON DUPLICATE KEY UPDATE

需表擁有 主鍵 或 唯一索引 才能觸發衝突檢測。

忽略重複錯誤

INSERT IGNORE INTO `clients` (`client_id`, `client_name`) 
VALUES (10345, 'IBM');

重複鍵衝突時靜默忽略，不報錯、不插入，affected_rows 為 0。

衝突時更新（Upsert 模式）

INSERT INTO `clients` (`client_id`, `client_name`, `visit_count`)
VALUES (10345, 'IBM', 1)
ON DUPLICATE KEY UPDATE 
    `visit_count` = `visit_count` + 1,
    `last_visit` = NOW();

此為「不存在則插入，存在則更新」的原子操作，極適合計數器、狀態同步等高併發場景。

方案三：應用層「查詢-判斷-插入」反模式與改良

常見但低效的 PHP 寫法：

$title = 'www.example.com';
$sql = "SELECT * FROM `tablename` WHERE `title`='$title'";
$query = mysql_query($sql);
if (mysql_num_rows($query)) {
    exit('重複資料');
} else {
    mysql_query("INSERT INTO `tablename` VALUES('$title')");
    exit('儲存成功');
}

缺點分析

• 競態條件：查詢到插入間隙可能被並發請求插入相同資料
• 雙倍查詢：每次操作需 2 次資料庫往返
• SQL 注入風險：直接拼接變數極度危險

改良方案：預處理語句 + 事務 + 唯一索引

$stmt = $pdo->prepare("INSERT INTO `tablename` (`title`) VALUES (?)");
try {
    $pdo->beginTransaction();
    $stmt->execute([$title]);
    $pdo->commit();
    echo '儲存成功';
} catch (PDOException $e) {
    if ($e->getCode() == 23000) { // Integrity constraint violation
        echo '重複資料';
    } else {
        throw $e;
    }
    $pdo->rollBack();
}

方案選型決策矩陣

場景	推薦方案	核心優勢
跨表批量遷移去重	INSERT ... WHERE NOT EXISTS	標準 SQL、集合操作、效能佳
單筆業務寫入、需更新既有資料	ON DUPLICATE KEY UPDATE	原子化 Upsert、避免競態條件
單筆寫入、重複即拒絕	INSERT IGNORE	語法簡潔、無錯誤拋出
高併發計數器、統計聚合	ON DUPLICATE KEY UPDATE + 唯一索引	無鎖樂觀並發、效能最優

核心原則總結

1. 資料庫層面強制約束：務必在業務鍵上建立 UNIQUE INDEX 或 PRIMARY KEY
2. 優先使用原生原子操作：避免應用層「查-插」分離邏輯
3. 正確處理回傳值：檢查 affected_rows、LAST_INSERT_ID() 判斷操作結果
4. 監控死鎖與鎖等待：高併發下 ON DUPLICATE KEY UPDATE 可能引發間隙鎖衝突

建立「資料庫守門人」思維，將資料完整性約束下沉至儲存層，配合應用層優雅異常處理，才能構建健壯的資料寫入體系。