IGNORE，REPLACE，ON DUPLICATE KEY UPDATE在避免重复插入记录时存在的问题及最佳实践_sql设置ignore on duplicate key update还有效果吗-CSDN博客

本文链接：https://blog.csdn.net/Saintyyu/article/details/104330881

本文分析了MySQL中防止重复插入的三种方法：IGNORE、REPLACE和ON DUPLICATE KEY UPDATE，探讨了它们的实现机制和存在的问题，如主从不一致、主键消耗过快等。最后，提出了在并发和业务场景下应如何选择最佳实践，指出IGNORE方案不实用，REPLACE存在数据丢失风险，而ON DUPLICATE KEY UPDATE需注意死锁问题。最佳实践建议在事务中结合SELECT和UPDATE避免冲突。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在实际业务场景中，经常会有这样的需求：插入一条记录，如果数据表中已经存在该条记录则更新它的部分字段，比如更新update_time或者在某些列上执行累加操作等。参考博客1中介绍了三种在MySQL中避免重复插入记录的方法，本文将在简单介绍这三种用法的基础上，深入分析这其各自存在的问题，最后给出在实际生产环境中对该业务场景的最佳实践。

为了便于后续描述，我们创建了如下的身份证记录表：

CREATE TABLE `identity_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `identity_id` varchar(30) NOT NULL COMMENT '身份证号',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_identity_id` (`identity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

该表有两个唯一键：主键和由identity_id字段构成的身份证号键唯一。创建该表时的AUTO_INCREMENT=0，表示主键id的自增起始值为0。此外，我们需要往该表中初始化一条数据：

insert into `identity_info`(`identity_id`,`name`) values('123456789012345678','张三');

此时如果执行如下查询语句：

select * from identity_info;

得到的结果如下：

为了后续比较，我们执行如下sql来查看该当前表的auto_increment值（其中的database_name需要替换为具体的数据库名）：

select auto_increment from information_schema.tables where table_schema='database_name' and table_name='identity_info';

查询结果如下：

该值为2，表示下次插入行记录的id字段值从2开始。

至此，前面描述的“数据表中已存在该条记录”的判断逻辑，在身份证记录表中的标准是指身份证号（identity_id字段的值）相同，因为我们认为用户在进行该操作时不会给某个身份证号指定分配主键（Id）值，所以以下讨论都不考虑主键（id）冲突的场景。

背景知识

为了更好地理解后续对INGORE、REPLACE和ON DUPLICATE KEY UPDATE实现机制的分析，我们需要先了解innodb_autoinc_lock_mode这参数。详细的介绍参见参考博客中的自增锁部分，本文简单介绍如下：自增锁是一种特殊的表级锁，主要用于获取事务中插入的自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以控制自增主键的生成策略，本质上就是控制auto_increment值的生成方式。通过执行如下sql可以看出当前使用的是哪种策略：

select @@innodb_autoinc_lock_mode;

本文执行结果如下：

实际上，innodb_autoinc_lock_mode值为1是数据库的默认策略。当然，除了1之外，该参数还有两种取值，分别是0和2，简单介绍如下：

当该值为0时，所有的插入语句都会获得一个特殊的表级AUTO-INC锁（即自增锁），用于插入具有AUTO_INCREMENT列的表。此锁定通常保持到语句执行结束（并非有某些博客中说的保持到事务结束），以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增由任何给定语句分配的值是连续的。这意味着，当innodb_autoinc_lock_mode=0时，只有插入成功之后，auto_increment值才会递增，插入失败不会递增。

当该值为1时（默认值），对于“Simple inserts”（要插入的行数事先已知）通过在mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁，它只在分配过程的持续时间内保持，而不是直到语句完成。 不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。这意味着，