Mysql处理Duplicateentry‘6‘forkey‘PRIMARY‘问题及解决

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

Mysql处理Duplicate entry ‘6‘ for key ‘PRIMARY‘

在业务中app和设备都需要调用ip查询对应城市的天气接口，出现了ip这个唯一索引引起的插入冲突。

以前单系统处理这种主键冲突或唯一索引冲突，都是先查询后插入。

使用 if-else语句，判断dp_city是否存在，若存在则更新，不存在则插入。

if not exists (select ip from dp_city where ip = adress)
      insert into dp_city (ip ,name,...) values(adress,name,...)
else
      update dp_city set name= 'name',... where ip = adress

但这样写不仅效率太差，每次都需要执行两条SQL语句，一条语句用来判断dp_city是否在表中已经存在，另一条语句用来插入或更新表中数据。

而且在高并发的场景下，两个请求同一时刻都查到数据不存在，一个请求先插入数据，另一个请求再插入数据时就会出现主键或索引冲突的异常，也不能保证原子性。

解决这个问题最常规的做法是：加锁

加数据库悲观锁太影响性能；加数据库乐观锁，基于版本号判断，一般用于更新操作，像这种插入操作基本上不会用。

另一种办法是使用redis加入基于redis的分布式锁，锁定已存在的ip。但也如果过度依赖redis，若是出现网络超时，服务就悲剧了。

那么有没有更优雅高效的方法呢，通过查阅资料，发现MySQL一条语句很好的解决了这个问题：ON DUPLICATE KEY UPDATE

该语句的语法如下：

INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;

这个语法的目的是为了解决重复性，当数据库中存在某个记录时，执行这条语句会更新它，而不存在这条记录时，会插入它。

该语句规则如下：

如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。