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

MySQL 防止重複插入方案總結：IGNORE、REPLACE、ON DUPLICATE KEY UPDATE 深度對比

編輯：MySQL 綜合教學

MySQL 防止重複插入方案總結：三大核心語法深度對比

針對「不存在插入、存在更新/忽略」需求，MySQL 提供三種原生語法，配合唯一索引使用。本文系統對比適用場景、行為差異與效能特徵。

方案對比表

語法	衝突行為	受影響行數	自增 ID	觸發器	典型場景
INSERT IGNORE	靜默忽略，不報錯	0（衝突） / 1（新增）	不變	不觸發	日誌同步、去重導入
REPLACE	刪除舊行 → 插入新行	1（新增） / 2+（替換）	重新生成	觸發 DELETE + INSERT	需全量覆蓋、可接受 ID 變化
ON DUPLICATE KEY UPDATE	更新指定欄位	1（新增） / 2（更新）	保持不變	觸發 UPDATE	計數器增量、狀態同步、精準更新

核心語法範例

1. INSERT IGNORE

INSERT IGNORE INTO t (uk, col) VALUES ('a', 1);
INSERT IGNORE INTO t1 (name) SELECT name FROM t2;

2. REPLACE

REPLACE INTO t (uk, col) VALUES ('a', 2);
REPLACE INTO t (uk, col) SELECT uk, col FROM src;

3. ON DUPLICATE KEY UPDATE

INSERT INTO t (uk, counter) VALUES ('a', 1)
ON DUPLICATE KEY UPDATE counter = counter + 1;

-- 批量引用插入值
INSERT INTO t (uk, a, b) VALUES ('x', 1, 2), ('y', 3, 4)
ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b);

多重唯一鍵陷阱

表有多個 UNIQUE KEY 時，ON DUPLICATE KEY UPDATE 僅更新第一個匹配的行（隱含 LIMIT 1），可能導致非預期行被更新。建議：單表僅保留一個業務唯一鍵，或改用應用層邏輯。

應用層檢查（PHP 參考）

<?php
$mysqli = new mysqli('localhost','root','pass','db');
$name = $_GET['name'];
$stmt = $mysqli->prepare('SELECT 1 FROM users WHERE username=?');
$stmt->bind_param('s', $name);
$stmt->execute();
if ($stmt->get_result()->num_rows === 0) {
    $mysqli->query("INSERT INTO users (username) VALUES ('$name')");
    echo '註冊成功';
} else {
    echo '用戶名已存在';
}
?>

建議：高併發場景務必使用資料庫層級原子語法，避免「查詢-插入」間隙產生競態條件。

附錄：刪除重複資料通用模板

-- 單欄位去重，保留最小 id
DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY uk HAVING COUNT(*) > 1)
  AND uk IN (SELECT uk FROM t GROUP BY uk HAVING COUNT(*) > 1);

-- 多欄位去重
DELETE FROM t WHERE (uk1, uk2) IN (SELECT uk1, uk2 FROM t GROUP BY uk1, uk2 HAVING COUNT(*) > 1)
  AND id NOT IN (SELECT MIN(id) FROM t GROUP BY uk1, uk2 HAVING COUNT(*) > 1);