MySQL 防止重複插入資料的三大核心方案與實戰技巧

MySQL 防止重複插入資料的三大核心方案與實戰技巧

業務開發中常遇到「不存在則插入，存在則更新/忽略」的需求。MySQL 提供三種原生語法高效解決，配合唯一索引使用效果最佳。

方案一：INSERT IGNORE —— 靜默忽略衝突

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`)
VALUES ('test@163.com', '99999', '9999');

• 需有 PRIMARY KEY 或 UNIQUE INDEX 判斷唯一性。
• 衝突時不報錯，返回受影響行數 0。
• 適用於「只插入不更新」場景，如同步備份表：
  INSERT IGNORE INTO table_1 (name) SELECT name FROM table_2;

方案二：REPLACE INTO —— 刪除舊行再插入新行

REPLACE INTO `table_name` (`col1`, `col2`) VALUES (val1, val2);
REPLACE INTO `table_name` (`col1`, `col2`) SELECT ...;
REPLACE INTO `table_name` SET `col1`='val1', `col2`='val2';

• 演算法：嘗試插入 → 唯一鍵衝突 → 刪除衝突行 → 再次插入。
• 返回值 = 刪除行數 + 插入行數。返回 1 表示純插入，>1 表示有替換。
• 注意：會觸發 DELETE + INSERT 觸發器，自增 ID 會變化。

方案三：INSERT ... ON DUPLICATE KEY UPDATE —— 精準更新指定欄位

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE `c` = `c` + 1;

• 唯一鍵衝突時執行 UPDATE，不改動其他欄位，自增 ID 不變。
• 受影響行數：1=新增，2=更新。
• 多重唯一鍵表慎用，可能只更新第一匹配行（隱含 LIMIT 1）。
• 支援 VALUES(col) 引用插入值，適合批量操作：
  INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

實戰範例：跨表同步資料（以 email 為唯一鍵）

INSERT INTO `table_name1` (`title`,`first_name`,`last_name`,`email`,`phone`,`user_id`,`role_id`,`status`,`campaign_id`)
SELECT '', '', '', `table_name2`.`email`, `table_name2`.`phone`, NULL, NULL, 'pending', 29
FROM `table_name2`
WHERE `table_name2`.`status` = 1
ON DUPLICATE KEY UPDATE `table_name1`.`status` = 'pending';

輔助方案：應用層檢查（PHP 範例）

<?php
$link = mysqli_connect('localhost','root','1234','dbname');
$username = $_GET['name'];
$sql = "SELECT 1 FROM usertable WHERE user_name=?";
$stmt = $link->prepare($sql);
$stmt->bind_param('s', $username);
$stmt->execute();
if ($stmt->get_result()->num_rows === 0) {
    $link->query("INSERT INTO student (user_name) VALUES ('$username')");
    echo "註冊成功";
} else {
    echo "用戶名已存在";
}
?>

建議：優先使用資料庫層級方案（IGNORE/REPLACE/ON DUPLICATE KEY），避免併發競態條件。

附錄：刪除重複資料 SQL 範本

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

-- 多欄位去重
DELETE FROM vitae a
WHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1)
  AND a.rowid NOT IN (SELECT MIN(rowid) FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1);