MySQL防止重复插入记录的三种核心方法与去重策略

结果或目标

我们需要在插入数据时保证特定列（如主键或唯一索引）的唯一性。根据业务需求，可能有三种处理逻辑：

• 忽略：如果记录已存在，则不做任何操作，静默失败。
• 替换：如果记录已存在，先删除旧记录，再插入新记录（自增ID会变化）。
• 更新：如果记录已存在，更新指定字段；否则插入新记录。

准备条件

上述所有基于主键（PRIMARY KEY）或唯一索引（UNIQUE INDEX）的插入语句生效的前提是：
表结构必须包含能够标识记录唯一性的字段约束。若表中没有唯一约束，这些语句将等同于普通的INSERT操作，无法起到防重作用。

第1步：使用INSERT IGNORE忽略重复记录

目的：在尝试插入数据时，若遇到主键或唯一索引冲突，直接放弃插入并返回0行受影响，而非抛出错误。

入口与动作：在标准INSERT语句中加入IGNORE关键字。

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

完成状态：

• 若email在表中不存在：成功插入一行。
• 若email已存在：语句执行完毕，但无新数据写入，返回受影响行数为0。

应用场景：适用于批量导入数据时的过滤去重，如复制表内容避免重复：

INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`;

第2步：使用REPLACE INTO实现数据替换

目的：当检测到重复键值时，自动删除原行并插入新行。这相当于一个“先删后插”的组合操作。

注意事项：

• 由于涉及删除操作，自增主键（Auto Increment ID）通常会改变为新分配的ID。
• 返回值是“被删除行数 + 被插入行数”。若只新增1行，返回1；若替换了旧行，通常返回2。

语法格式：

REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);
REPLACE INTO `table_name` SET `col_name`='value';

完整算法逻辑：

1. 尝试将新行插入表中。
2. 若因主键/唯一索引冲突导致插入失败，从表中删除含有冲突值的旧行。
3. 再次尝试插入新行。

第3步：使用ON DUPLICATE KEY UPDATE进行原子更新

目的：在发生重复键冲突时，不删除原有行，而是执行UPDATE语句更新指定字段。这是性能最高且能保留原始主键的方案。

基础用法：

以下两条SQL语句效果相同，若a为唯一列且值为1：

-- 方式A: 使用 ON DUPLICATE KEY UPDATE
INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1;

-- 方式B: 普通UPDATE
UPDATE `table` SET `c`=`c`+1 WHERE `a`=1;

返回值差异：

• 作为新记录插入：受影响行数为 1。
• 作为旧记录更新：受影响行数为 2（MySQL内部视为更新了2次计数：一次删除确认冲突，一次更新执行）。

多值插入优化：

在多行插入时使用VALUES(col_name)函数引用即将插入的值，实现动态更新：

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

该语句在处理(1,2,3)冲突时，会将c更新为1+2=3；处理(4,5,6)冲突时，将c更新为4+5=9。

真实操作顺序：应用层与SQL层的混合策略

除了纯SQL方案，部分业务可能在PHP等应用层进行预判。需注意，应用层判断在高并发下可能存在竞态条件，生产环境推荐优先使用上述SQL层面的原子操作。

应用层检测示例（PHP MySQLi/PDO语境）

警告：原文提供的示例使用了已废弃的mysql_connect扩展。现代开发请使用mysqli或PDO，并务必使用预处理语句防止SQL注入。以下仅为逻辑示意：

$username = $_GET["name"];
// 1. 检查是否存在
$stmt = $pdo->prepare("SELECT id FROM usertable WHERE user_name = ?");
$stmt->execute([$username]);
$row = $stmt->fetch();

if (!$row) {
    // 2. 不存在则插入
    $insertStmt = $pdo->prepare("INSERT INTO student (user_name) VALUES (?)");
    $insertStmt->execute([$username]);
    echo "用户注册成功！";
} else {
    echo "该用户名已存在，请重新选择用户名！";
}

完成判断与排错

在执行上述操作前，若发现表结构没有设置唯一索引，REPLACE和ON DUPLICATE KEY UPDATE可能不会按预期触发删除或更新逻辑，导致数据冗余。建议先运行以下语句检查唯一性约束：

SHOW INDEX FROM table_name;

简要总结：查找与清理历史重复数据

对于已经存在的重复数据，可通过以下SQL策略进行清理或删除。此处以假设的主键字段peopleId为例：

1. 查找单个字段重复的记录：
   SELECT * FROM people WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(peopleId) > 1);
2. 删除单字段重复记录，仅保留最小RowID（如有自增ID）：
   DELETE FROM people WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(peopleId) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM people GROUP BY peopleId HAVING COUNT(peopleId) > 1);
3. 查找多个字段组合重复的记录（如 peopleId, seq）：
   SELECT * FROM vitae a WHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1);
4. 清理多字段重复记录：
   DELETE a FROM vitae a WHERE (a.peopleId, a.seq) IN (SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1) AND rowid NOT IN (SELECT MIN(rowid) FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1);

注意：在使用ROW_ID（MySQL中通常指隐式自增主键或其他代理键）进行删除判断时，需确保该字段确实能代表数据的创建顺序或唯一性，不同数据库方言可能有所不同。