写点什么

Mysql 增量更新 -ON DUPLICATE KEY UPDATE

用户头像
BerryMew
关注
发布于: 2020 年 05 月 23 日
Mysql增量更新-ON DUPLICATE KEY UPDATE

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



发布于: 2020 年 05 月 23 日阅读数: 120
用户头像

BerryMew

关注

不断重构 2017.10.25 加入

评论

发布
暂无评论
Mysql增量更新-ON DUPLICATE KEY UPDATE