Mysql 增量更新 -ON DUPLICATE KEY UPDATE

关注

发布于: 2020 年 05 月 23 日

1.需求没有则插入，已有则更新，这就是增量更新。
不同数据库有自己的实现，Oracle用的是MERGE，Mysql用的是ON DUPLICATE KEY UPDATE 。
这篇文我只介绍用法，以及注意事项，因为我想用的时候找个样例好费劲。
2.环境
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL COMMENT '用户id',
	`income` int(11) DEFAULT NULL COMMENT '收入',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
3.测试3.1 第一种写法1.插入记录
insert into user (user_id,income) values (1,100),(2,200),(3,300)
﻿
﻿
2.继续插入
insert into user (user_id,income) values (1,1000),(2,2000),(3,3000)
这时肯定会报错： 1062 - Duplicate entry '1' for key 'PRIMARY'
﻿
3.已存在则更新使用ON DUPLICATE KEY UPDATE 实现这样的需求
 insert into user (user_id,income) values (1,1000),(2,2000),(3,3000)
 ON DUPLICATE KEY UPDATE income=values(income)
﻿
﻿
可以看到已经成功更新了income值
写法上加入ON DUPLICATE KEY UPDATE关键字，然后只是在插入语句基础上增加了更新语句
income=values(income) 
(user_id,income) values (1,1000),(2,2000),(3,3000)
对比看下上下两个写法是不是很类似？
4.不存在则插入
 insert into user (user_id,income) values (10,1000),(20,2000),(30,3000)
 ON DUPLICATE KEY UPDATE income=values(income)
﻿
可以看到也成功插入了新的记录。
5.注意事项表中必须有主键 PRIMARY KEY
3.2 第二种写法1.插入记录
insert into user (user_id,income) select 1 user_id,100 income from dual
注：先清空之前的测试记录
2.插入多条
	insert into user (user_id,income) 
  select t.user_id,t.income from 
	(select 2 user_id,200 income from dual
		union all 
  select 3 user_id,300 income from dual) t
﻿
﻿
3.存在则更新
	insert into user (user_id,income) 
  	select t.user_id,t.income from (
      select 1 user_id,1000 income from dual
      union all 
      select 2 user_id,2000 income from dual
      union all 
      select 3 user_id,3000 income from dual) t 
  ON DUPLICATE KEY UPDATE income=values(income)
  
﻿
4.不存在则插入
	insert into user (user_id,income) 
    select t.user_id,t.income from (
      select 10 user_id,10000 income from dual
      union all 
      select 20 user_id,20000 income from dual
      union all 
      select 30 user_id,30000 income from dual) t
  ON DUPLICATE KEY UPDATE income=values(income)
﻿
5.注意事项同第一种写法，表必须有主键
insert into select 这种写法的select子句注意使用派生表
举个例子：下面这种写法也能运行，但是这样容易出问题，因为需要匹配主键，用派生表的话内容可以用group by以保证子句的主键唯一。
	insert into user (user_id,income) 
	select 10 user_id,10000 income from dual
	union all 
  select 20 user_id,20000 income from dual
  union all 
  select 30 user_id,30000 income from dual
  ON DUPLICATE KEY UPDATE income=values(income)
﻿
4.神奇操作插入记录
insert into user (user_id,income) values (1,100),(2,200),(3,300)
注：先清空以前的测试数据
4.1 更新主键
 insert into user (user_id,income) values (1,1000)
 ON DUPLICATE KEY UPDATE user_id=5
﻿
看结果，主键也能被更新，但是上面的操作有什么意义？没有，所以是神奇操作。
4.2 不写values
 insert into user (user_id,income) values (10,1000),(20,2000),(30,3000)
 ON DUPLICATE KEY UPDATE income=income
﻿
也能插入，但是 income=income 这种写法就导致 ON DUPLICATE KEY UPDATE子 句没有意义了。再次执行上面的测试语句，既不会更新也不会插入，因为没什么可更新的。有什么意义？这个感觉有一点，这么写可以避免主键已存在错误：1062 - Duplicate entry '1' for key 'PRIMARY'
当然 这里写user_id=user_id也行
5.补充示例中没有写更新多值的写法，这里写一个模板，假设user表中还有个年龄 age 字段。
insert into user (user_id,income,age) values (10,1000,18),(20,2000,18),(30,3000,18)
ON DUPLICATE KEY UPDATE income=values(income),age=values(age)
﻿
以上。
﻿
相关资料：
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
﻿