mysql插入转更新ON DUPLICATE KEY UPDATE

又双叒罬

背景：

mysql利用insert插入数据时，可能发生主键/唯一键冲突，若想在冲突时变更为update语句，可以借助于mysql的INSERT ... ON DUPLICATE KEY UPDATE语句

语法：

insert into table(key...) values(val...)on duplicate key update key1=val1,key2=val2创建表user，下面的例子都以该表为主
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NULL,
  `user_name` VARCHAR(45) NULL,
  `score` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC));
INSERT INTO user (id, user_id, user_name, score) VALUES (66, 1, 'tom', 97);
INSERT INTO user (id, user_id, user_name, score) VALUES (67, 2, 'marry', 95);单条记录插入：

- user_id为唯一键
insert into user(user_id,user_name,score) values(1,'tom',100)on duplicate key update score=100

• 前半段sql是正常的insert语句
  
• 后半段sql从on duplicate key开始，update将会在主键/唯一键冲突时执行。
  
• 若数据库中已经存在user_id=1的数据，将会执行update操作，将user_id=1数据中的score改为100
  

多条记录插入：

• user_id为唯一键
  

insert into user(user_id,user_name,score) values(1,'tom',100),(2,'marry',99)
on duplicate key update score=values(score)

insert intou ser(user_id,user_name,score) values(1,'tom',100),(2,'marry',99)
on duplicate key update score=values(user_id)

insert into user(user_id,user_name,score) values(1,'tom',100),(2,'marry',99)
on duplicate key update score=values(user_id)+values(score)

insert into user(user_id,user_name,score) values(1,'tom',100),(2,'marry',99)
on duplicate key update score=values(66)

• 多条记录插入时，需要利用values函数处理赋值
  
  
  
  • values内可以直接使用字段名，可以是当前被赋值的字段(正常情况)，也可以是其他字段
    
  • values内还可以是具体的常量值
    
  • 可以使用多个数字类型的字段经过values处理后再进行运算
    
  
  

细节：

• 在使用insert into ... on duplicate key update插入/更新数据时，affected-rows在不同情况下的值不同，下面以插入单条记录为例
  
  
  
  • 当数据成功插入时（没有冲突），affected-rows=1(仅有insert一个操作，插入n条记录就是n)
    
  • 当数据发生冲突并更新成功时，affected-rows=2(包含delete和update操作，插入n条记录就是2*n)
    
  • 当数据发生冲突，更新内容与原数据一致时，affected-rows=0。若开启了CLIENT_FOUND_ROWS，affected-rows=1(冲突记录数量，n条记录冲突就是n)
    
  
  

• 在冲突update时，若表中存在自增字段，自增字段也会+1(不管affects-rows是否>0)
  
  
  
  • 若id为自增，当前id为67，先执行一条有冲突的语句，此时自增id已经到68(原数据的id不会自增，只是自增id的计数器会+1)
    
  • 再插入一条新的记录，此时新纪录的id为69
    
  
  

// 插入冲突数据，执行update，当前最新id自增
insert into user(user_id,user_name,score) values(2,'marry',87) on duplicate key update score=87
// 插入一条新数据
insert into user(user_id,user_name,score) values(3,'kiti',99)使用场景：

• 数据时常变更，需要定时同步到mysql中，主键/唯一键一般不会修改或较少修改时，该方法可以代替delete + insert。
  
  
  
  • 比如学生最新成绩表，需要同步最新一次考试的分数，学生id主键一般不会变更频繁，利用on duplicate key刷新学生分数
    
  
  
• 表中某些字段需要插入和阶段性变更时，可以利用insert into ... on duplicate key 代替insert和update两种操作
  
  
  
  • 如订单记录表，生成订单需要插入表，同时需要阶段性的修改订单状态(下单、付款、完成等等)，订单记录id又是唯一键，此时可以利用该方法实现两种业务操作
    
  
  
• 待补充......
  

参考mysql5.7官方文档：https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html