MySQL中ON DUPLICATE KEY UPDATE使用总结

场景

假设有一张如下的表，用来汇总每个店主每天的销售额:

CREATE TABLE `shop_sale_amount_summary` (
  `shop_sale_amount_summary_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `version` varchar(50) NOT NULL DEFAULT '' COMMENT '下单日期(年月日)',
  `sale_amount` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '每日销售额',
  `shop_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '店主id',
  `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除:0.未删除 1.删除',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`shop_sale_amount_summary_id`),
  UNIQUE KEY `uk_version_shop_id` (`version`, `shop_id`),
) ENGINE=InnoDB AUTO_INCREMENT=1;

当一条销售额数据过来的时候，这时候就需要通过select查询判断一条记录存不存在，存在则update更新，不存在则insert新插入一条记录，这种方式容易出现并发问题，当然可以通过加分布式锁解决，但是处理数据效率不高。还有另外一种方式，通过直接insert一条记录，看看是否有异常DuplicateKeyException。若有该异常，则进行update，依赖于当前表唯一性索引uk_version_shop_id。接下来的方式，可以让你彻底解放双手，那就是使用MySQL的INSERT ... ON DUPLICATE KEY UPDATE语句，可以一步到位解决上面的问题。

用法

语法格式

INSERT INTO shop_sale_amount_summary ( version, shop_id, sale_amount ) VALUES ( '20220122', 123, 90.12 ) 
ON DUPLICATE KEY UPDATE version = VALUES( version ),
						shop_id = VALUES( shop_id ),
						sale_amount = IF( sale_amount + VALUES ( sale_amount ) >= 0, sale_amount + VALUES ( sale_amount ), sale_amount )

当与表中现有记录的唯一索引或主键中发生冲突时，那么就会对该条记录进行更新，否则插入新的记录。
上面的SQL代表对shopId = 123，version=20220122的销售额进行累加。因为有了id自增，所以是基于唯一索引uk_version_shop_id判断记录唯一性。

VALUES(col_name) : 代表取当前insert语句中的插入值，比如:VALUES( version )表示取值’20220122’，VALUES( shop_id )表示取值123

IF(判断条件,值1,值2)：当判断条件=true时，取值1，false时，取值2

注意事项

因为该条语句是insert语句，不能带where条件
如果是插入操作，受到影响行的值为1；如果更新操作，受到影响行的值为2；如果更新的数据和已有的数据一样（就相当于没变，所有值保持不变），受到影响的行的值为0
使用的前提条件，要有主键索引（PRIMARY KEY）或唯一索引（UNIQUE KEY）
会存在自增Id值跳跃比较大的问题，也会有自增id不连续问题。这是因为on duplicate key update，在update的时候，id也会自增1，但在上述的业务场景下，不影响使用
在有可能并发事务执行相同的语句情况下不要使用该语句，可能导致产生death lock。而上述业务场景执行的前提，已经根据uk_version_shop_id做了分区串行处理，保证同一个uk_version_shop_id值的数据只会串行处理。

弊端-死锁

原因

更深入一层的原因是lock_mode X locks gap before rec insert intention waiting，事务1和事务2都互相持有相同的gap锁，都在等待对方释放gap锁。

在MVCC中，当前读和快照读的区别:当前读每次需要加锁（可以使共享锁或者互斥锁）获取到最新的数据，而快照读是读取的是这个事务开始的时候那个快照，这个是通过undo log去进行实现的。在RC隔离级别下如果要读取那么就是当前读，那么其实就需要加上S锁。S锁是共享锁，X锁是互斥锁。一般来说X锁和S、X锁都互斥，S锁和S锁不互斥。

使用下面命令，可以查看产生死锁的详细原因

1	SHOW ENGINE INNODB STATUS

查找关键字LATEST DETECTED DEADLOCK，找到对应输出信息位置。

解决方法

select… for update，不靠谱，也有可能死锁。
当并发事务执行都没select出记录，且where筛选条件落在同一个区间时，两个事务可获得同一区间的gap锁。此时再进行并发插入，其中一个会进入锁等待，待第二个session进行插入时，会出现互相等待，导致死锁。MySQL会根据事务权重选择一个事务进行回滚。
修改数据隔离级别为RR级别，RR隔离级别下会用快照读，从而不会加S锁，也不会有gap锁

场景

用法

语法格式

注意事项

弊端-死锁

原因

解决方法

参考资料