文档章节

mysql on duplicate update 语法

mrliuze
 mrliuze
发布于 2016/06/30 20:37
字数 850
阅读 265
收藏 4

钉钉、微博极速扩容黑科技,点击观看阿里云弹性计算年度发布会!>>>

mysql "ON DUPLICATE KEY UPDATE" 语法
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。
例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果:

复制代码 代码如下:


INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;


如果行作为新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2。
这个语法还可以这样用:
如果INSERT多行记录(假设 a 为主键或 a 是一个 UNIQUE索引列):

复制代码 代码如下:


INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;


执行后, c 的值会变为 4 (第二条与第一条重复, c 在原值上+1).

复制代码 代码如下:


INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);


执行后, c 的值会变为 7 (第二条与第一条重复, c 在直接取重复的值7).
注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!
这个语法和适合用在需要 判断记录是否存在,不存在则插入存在则更新的场景.

INSERT INTO .. ON DUPLICATE KEY更新多行记录
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

复制代码 代码如下:


INSERT INTO TABLE (a,b,c)
VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;


如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
如果你想了解更多关于INSERT INTO .. ON DUPLICATE KEY的功能说明,详见MySQL参考文档:13.2.4. INSERT语法

现在问题来了,如果INSERT多行记录, ON DUPLICATE KEY UPDATE后面字段的值怎么指定?要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE,到底他会更新一行记录,还是更新所有需要更新的行。这个问题困扰了我很久了,其实使用VALUES()函数一切问题都解决了。

举个例子,字段a被定义为UNIQUE,并且原数据库表table中已存在记录(2,2,9)和(3,2,1),如果插入记录的a值与原有记录重复,则更新原有记录,否则插入新行:

复制代码 代码如下:


INSERT INTO TABLE (a,b,c) VALUES
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);


以上SQL语句的执行,发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突,则执行ON DUPLICATE KEY UPDATE,将原有记录(2,2,9)更新成(2,5,9),将(3,2,1)更新成(3,3,1),插入新记录(1,2,3)和(4,8,2)
注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!

mrliuze
粉丝 16
博文 155
码字总数 12377
作品 0
浦东
程序员
私信 提问
加载中
请先登录后再评论。
mysql insertOrUpdate 方法

自己对这个方法又有点小心得 分享下 https://my.oschina.net/hccake/blog/777225 mysql "ON DUPLICATE KEY UPDATE" 语法 如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会......

Hccake
2016/08/18
734
0
Mysql 原生语句中save or update 的各种写法

Mysql 原生语句中save or update 的各种写法 背景   在平常的开发中,经常碰到这种更新数据的场景:先判断某一数据在库表中是否存在,存在则update,不存在则insert。如果使用Hibernate,它...

Tek_Eternal
2015/02/09
1.9K
0
Mysql避免重复插入记录方法

一、mysql replace用法 1.replace into replace into table (id,name)values('1','aa'),('2','bb') 此语句的作用是向表table中插入两条记录。如果主键id为1或2不存在 就相当于 insert into ......

osc_evi50j4g
2018/07/28
12
0
MySQL INSERT ...ON DUPLICATE KEY UPDATE

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插...

君主不是我
2014/09/02
2
0
关于sql批量插入中有部分数据错误

前言: 今天遇到一个问题,是用easypoi导入excle表格,其中员工的工号字段不能重复。 现在有两个思路, 思路一:是先对excle表格中字段去跟整个数据库员工表中当前所有员工做比对,去除当前e...

从入门到改行
2019/02/27
56
0

没有更多内容

加载失败,请刷新页面

加载更多

插入,在PostgreSQL中重复更新吗? - Insert, on duplicate update in PostgreSQL?

问题: Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: 几个月前,我从关于堆栈溢出的答案......

技术盛宴
43分钟前
28
0
互联网的寒冬下各大一线互联网公司还在用SpringBoot这是为什么?

引言 现在各大技术社区 Spring Boot 的文章越来越多,Spring Boot 相关的图文、视频教程越来越多,使用 Spring Boot 的互联网公司也越来越多; Java 程序员现在出去面试, Spring Boot 已经成...

北柠Java
46分钟前
8
0
vue+elementui实现简易的列筛选功能实现。

一、简易效果图: 二、需求背景 大家都知道,后管类系统当中,有时一个列表可能有很多列需要展示,如下图所示,但是用户在使用系统的时候,往往会需要针对其中某几列进行数据提取,在展示列比...

一生懸命吧
49分钟前
60
0
批处理问题记录——数字实验bat

记录学习批处理时的问题 批处理为输入一个数字,如果大于等于一百,直接输出输入数字,如果小于一百会重复+1,直到100后输出。 问题是,如果不输入数字,直接空格的话,批处理会出错。 寻求一...

愤怒的乌老大
55分钟前
6
0
算法题汇总

计算两个字符串中的最大的相同字符串

佳幂小煜
今天
27
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部