并发插入语法 INSERT ... ON DUPLICATE KEY UPDATE

1.前言

在日常开发中会遇到这样一个场景，新增或变更
创建一条记录

• 如果数据库已经存在同一主键的记录，则执行更新操作 ;
• 如果不存在，则执行新增操作 ;

实现思路 1 > 业务层处理
业务层代码处理，但需要先查询，查询和插入并不是原子性操作，在并发量比较高的时候，可能会出现两个线程都查询某记录不存在，就会执行两次插入。解决并发就得加锁，但加锁势必会影响性能。

实现思路2 > 数据库层
mysql 中有一个语法，INSERT … ON DUPLICATE KEY UPDATE ，可以实现并且是原子性操作。

2.INSERT … ON DUPLICATE KEY UPDATE

创建测试表

CREATE TABLE `insert_duplicate_update_test` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `uniq_id` varchar(32) DEFAULT '' COMMENT '字段 a',
  `filed_a` varchar(32) DEFAULT '' COMMENT '字段 b',
  `filed_b` varchar(32) DEFAULT '' COMMENT '字段 c',
  `version` int(10) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_id` (`uniq_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
COMMENT=' INSERT ... ON DUPLICATE KEY UPDATE 语法测试表';

单条执行

INSERT INTO insert_duplicate_update_test ( uniq_id, filed_a, filed_b, version )
VALUES
	( '1001', '字段 a', '3字段 b', 1 ) 
	ON DUPLICATE KEY UPDATE version = version + 1;

SQL含义：

• 由于uniq_id字段有唯一索引，所以在uniq_id=1001已经存在情况下，会更新 version 字段执行 version + 1;

更新情况下与以下SQL效果一致

UPDATE insert_duplicate_update_test 
SET version = version + 1 
WHERE uniq_id='1001';

批量执行

INSERT INTO insert_duplicate_update_test 
( uniq_id, filed_a, filed_b, version )
VALUES
	( '1001', '字段 a', '字段 b', 1 ), 
  ( '1002', '字段 _2_2', '字段 b_2', 1 )
	ON DUPLICATE KEY UPDATE 
	filed_a=VALUES(filed_a),
	version = version + 1;

SQL含义：

• 批量新增数据，若uniq_id相同数据已经存在，则执行更新操作版本version累加，filed_a内容会替换为当前内容
• 若同一批数据内存在uniq_id相同也会执行新增或变更逻辑 ;

3.常见问题

1.数据已经存在但是没有执行变更逻辑 ;

• 检查字段中是否包含唯一索引列 ;
• 使用INSERT … ON DUPLICATE KEY UPDATE函数达到新增或更新的条件是，必须包含唯一索引列，或者主键列；

2.返回的更新行数比实际处理的行数不一致 ；
官方介绍

简译

• 如果新插入行，则每行的受影响行值为1 ;
• 如果更新了现有行，则为2 ;
• 如果更新前后没有变化，则为0 ;

3.INSERT … ON DUPLICATE KEY UPDATE …对主键的影响,自增ID超大跳跃 ;
mysql对自增有一个模式锁innodb_autoinc_lock_mode，详见：自增模式innodb_autoinc_lock_mode
默认是根据处理行数累加当前自增ID；因为该函数返回的行数可能会大于插入的行数，所以会出现ID自增跳跃的问题;

参考资料：官方介绍传送门

关注程序员小强公众号更多编程趣事，知识心得与您分享