mysql中on duplicate key update 使用详解

快来打我* 2023-02-11 14:28 3阅读 0赞

### 唠叨一下 ###

在mysql中插入数据，如果碰到主键冲突或者唯一索引冲突的时候，程序中会抛出SQLIntegrityConstraintViolationException异常，如果只是单纯的由于主键冲突或者唯一索引冲突引起的异常的话，这个问题很容易解决，如果是主键冲突的话可以更新为其他主键，如果是唯一索引的话可以将唯一索引删除或者将引起唯一索引冲突的数据更新一下；但是还有另外一种情况是比较棘手的，那就是我插入一条数据的时候如果出现这样的冲突的时候，我不希望什么都不做，而是当出现这样插入失败的时候我将冲突的这条数据进行一些更新操作，出现了这样的诉求的时候，下面的这个关键字就可以粉墨登场啦：on duplicate key update，这个关键字最重要的作用就是当我们在插入数据出现主键或唯一索引重复的时候，可以执行它后面的一个更新操作，而不是什么都不做，直接返回异常，接下来我们就来详细的了解一下 on duplicate key update 这个mysql中关键字在开发中正确使用。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nemVoYWkyMjM0_size_16_color_FFFFFF_t_70_pic_center]

### 实现原理 ###

*  尝试插入新数据到数据库表中，如果插入成功则直接返回插入的列。
 *  如果由于主键或唯一索引出现重复而造成插入失败的话，则先数据库表中已存在的出现冲突的数据行加上共享锁，然后将该数据返回数据库服务器中。
 *  数据库服务器在内存中对改行数据执行on duplicate key update 后的update语句。
 *  然后对该行数据加上排它锁。
 *  最后将内存中update后的数据写入改行，全部执行完毕

### 使用实例 ###

#### mybatis中单个插入on duplicate key update的使用 ####

@Insert({ "<script>",
                "insert into user set id = #{id}, mobile = #{mobile}, email = #{email}, name = #{name}, link_name = #{linkName}, create_user_id = #{createUserId} ",
                " on duplicate key update create_user_id = #{createUserId} ",
                "</script>"})
        int insertTest(User user);

#### mybatis中批量插入on duplicate key update 的使用 ####

@Insert({ "<script>",
                "insert into user (id , mobile, email, name, link_name, create_user_id) ",
                " values ",
                "<foreach collection='list' index='index' item='item' separator=','> (#{item.id}, #{item.mobile}, #{item.email}, #{item.name}, #{item.linkName}, #{item.createUserId} )</foreach>",
                " on duplicate key update create_user_id = values(create_user_id), mobile = values(mobile) ",
                "</script>"})
        int batchInsertTest(List<User> userList);

### 存在问题 ###

*  高并发情况可能会出现死锁

由于主键或唯一索引出现重复的时候，在更新该调存在的数据的时候，会为该条数据加一个共享锁和一个排它锁，最后才会写入该条数据，如果并发比较高，两个线程几乎同时来操作这条数据的话，如果两个线程同时给一条数据加上共享锁和排它锁的话，就会出现死锁的情况。

### 使用总结 ###

在使用 on duplicate key update 关键字的时候，一定要注意它操作后返回结果，它的返回结果和我们平常使用的insert是不同的，使用insert … on duplicate key update时，如果主键或唯一索引没有重复，成功插入了新增数据则返回1，如果主键或唯一索引已经重复，没有插入成功，但是 on duplicate key update 后的更新的字段的数据和 原数据不同，则表示更新成功，此时返回的结果是2， 如果 on duplicate key update 的更新数据和原始数据相同，则表示更新也没有成功，则返回0.  
在mysql中，同样可以实现通过判断主键或唯一索引是否存在来执行不同操作的关键字还有两个，分别是：replace into 和 insert ignore into，这几种每一种方式都会存在一些问题，具体存在哪些问题，就等着各位去进一步的深入探究啦

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nemVoYWkyMjM0_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200524032134158.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nemVoYWkyMjM0,size_16,color_FFFFFF,t_70#pic_center