SQL如何插入数据时避免主键重复错误-SQL-PHP中文网

insert ignore 仅在存在 primary key 或 unique 约束时生效，依赖冲突错误捕获而非数据比对；无约束时等同普通 insert。

sql如何插入数据时避免主键重复错误

INSERT IGNORE 为什么只在有唯一约束时才生效

它不是靠“比对数据”来判断重复，而是依赖 MySQL 对 PRIMARY KEY 或 UNIQUE 约束冲突的错误捕获机制。没有这些约束，INSERT IGNORE 和普通 INSERT 完全一样——该报错还报错，该插照样插。

常见错误现象：Duplicate entry '1001' for key 'PRIMARY' 消失了，但 mysql_affected_rows() 返回 0；而如果字段超长或类型不匹配，依然会报 ERROR 1406 或 ERROR 1366。

必须提前建好主键或唯一索引，比如 ALTER TABLE users ADD UNIQUE (email);
大小写敏感性取决于排序规则：utf8mb4_unicode_ci 下 'A@B.COM' 和 'a@b.com' 视为重复；utf8mb4_bin 下则不重复
NULL 值在唯一索引中不算重复（MySQL 允许多个 NULL），所以 INSERT IGNORE 对含 NULL 的行不生效

ON DUPLICATE KEY UPDATE 更适合“存在即更新”场景

当你不只是想跳过，还想顺手更新时间戳、计数器或状态字段，ON DUPLICATE KEY UPDATE 是唯一靠谱的选择。它和 INSERT IGNORE 共享同一套唯一键触发逻辑，但行为可控得多。

示例：INSERT INTO logs (id, user_id, action, created_at) VALUES (123, 456, 'login', NOW()) ON DUPLICATE KEY UPDATE action = VALUES(action), updated_at = NOW();

VALUES(col) 引用的是本次 INSERT 中该列的值，不是原记录的旧值
只能响应一个冲突键（哪怕表上有多个唯一索引），冲突路径要提前想清楚
性能略低，因为每次冲突都要执行一次 UPDATE，但语义清晰，不易误用
事务中行为稳定，不会像 REPLACE INTO 那样触发外键级联删除

SQL Server 用 MERGE 替代 INSERT IGNORE

SQL Server 没有 INSERT IGNORE 语法，直接用 MERGE 实现等效逻辑。它明确区分 WHEN MATCHED 和 WHEN NOT MATCHED，避免了 REPLACE INTO 那种隐式删插带来的 ID 变动和触发器副作用。

典型写法：MERGE users AS target USING (SELECT 123 AS id, 'Alice' AS name) AS source ON target.id = source.id WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

必须指定 USING 子句，不能直接写 VALUES；批量插入需用 CTE 或临时表包装
如果还要支持“存在则更新”，补上 WHEN MATCHED THEN UPDATE SET ...
注意锁定行为：MERGE 默认加范围锁，高并发下可能比 NOT EXISTS + INSERT 更重
某些旧版驱动或 ORM 对 MERGE 支持不完整，上线前务必验证

没唯一索引时，别硬套 INSERT IGNORE

强行绕过约束设计，用 INSERT IGNORE + SELECT ... WHERE NOT EXISTS 组合，不仅慢，还容易漏掉并发间隙。真正的问题不在 SQL 写法，而在表结构本身。

例如：INSERT IGNORE INTO orders SELECT 'ORD-001', '2026-07-20' FROM dual WHERE NOT EXISTS (SELECT 1 FROM orders WHERE order_no = 'ORD-001');

这个查询在两个并发请求同时执行时，仍可能双双通过 NOT EXISTS 检查，然后都插入成功
加 WITH (UPDLOCK, HOLDLOCK) 能缓解，但锁粒度难控，且仅限 SQL Server
最稳妥解法：补唯一索引。哪怕只是 ALTER TABLE orders ADD CONSTRAINT uk_order_no UNIQUE (order_no);
如果真不能改表结构，应用层加分布式锁（如 Redis SETNX）比拼 SQL 更可靠

真正卡住人的从来不是语法怎么写，而是冲突判定依据是否落在数据库能强制保证的维度上——主键、唯一索引、业务组合键，缺一不可。