insert...on duplicate key update语法详解_insert on duplicate key update-程序员宅基地

技术标签: SQL  

一.作用和使用场景

在mysql入库时,不能出现两条数据主键一致的情况,因为在两条数据的主键一致的情况下,mysql就会判定为待插入数据在数据库中存在重复数据,也就是说判断数据是否重复是根据主键来区别的

但是有一些场景,如日志文件解析入库,消息队列接收数据入库等情况下可能解析到或者接收到待插入的重复数据存在重复数据则更新不存在则插入

这时如下语句的写法就派上用场了,on duplicate key update的作用也就是说存在重复数据则更新不存在则插入。

 

 

二.例子详细讲解

场景大概是这样的,业务方的需求是查询一条语句在不在,如果在就给出一个update语句,更新这条记录,如果不在,就给出一个insert语句,插入这条记录。逻辑大概是:

result =  select * from table;
if result = 0
   insert the record into table;
else
   update the record;

这样的操作乍一看没有什么问题,但是仔细分析分析,还是有些瓶颈的,目前来看,我能分析到的瓶颈有两个,

其一:

每次要执行2个SQL,效率比较差;

其二:

当我们在高并发的情况下跑这条语句,如果程序崩溃,不能保证操作的原子性。


**说明:
1. on duplicate key update 含义:
  1)如果在INSERT语句末尾指定了 on duplicate key update,
      并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,
      则在出现重复值的行执行UPDATE;
  2)如果不会导致唯一值列重复的问题,则插入新行。
 
2. values(col_name)函数只是取当前插入语句中的插入值,并没有累加功能。
  如:count = values(count) 取前面 insert into 中的 count 值,并更新
        当有多条记录冲突,需要插入时,前面的更新值都被最后一条记录覆盖,
        所以呈现出取最后一条更新的现象。
  如:count = count + values(count) 依然取前面 insert into 中的 count 值,
        并与原记录值相加后更新回数据库,这样,当多条记录冲突需要插入时,
        就实现了不断累加更新的现象。
 
注:insert into ... on duplicate key update ... values() 这个语句
    尽管在冲突时执行了更新,并没有插入,但是发现依然会占用 id 序号(自增),
    出现很多丢失的 id 值,可参看下面案例

 函数使用说明:在一个 INSERT … ON DUPLICATE KEY UPDATE … 语句中,你可以在 UPDATE 子句中使用 VALUES(col_name ) 函数,用来访问来自该语句的 INSERT 部分的列值。换言之,UPDATE 子句中的 VALUES(col_name ) 访问需要被插入的 col_name 的值 , 并不会发生重复键冲突。这个函数在多行插入中特别有用。 VALUES() 函数只在 INSERT ... UPDATE 语句中有意义,而在其它情况下只会返回 NULL。

**案例:
0. 创建案例表 word_count_0626(单词计数表)
  use test;
  CREATE TABLE IF NOT EXISTS word_count_0626 (
  	id int(11) NOT NULL AUTO_INCREMENT,
  	word varchar(64) NOT NULL,
  	count int(11) DEFAULT 0,
  	date date NOT NULL,
  	PRIMARY KEY (id),
  	UNIQUE KEY word (word, date)  // (word,date) 两字段组合唯一
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
注:curdate() 为 "2019-06-26"
1. 执行第一次:(首次数据库表中没有数据,正常插入)
  insert into word_count_0626 (word, count, date) values 
  ('a',5,curdate()) 
  on duplicate key update count=values(count);
  # 结果显示:
  id   word    count    date 
  1    a       5        2019-06-26
  
2. 执行第二次:(与第一次的唯一(word,date)冲突,执行更新)
  insert into word_count_0626 (word, count, date) values 
  ('a',6,curdate()) 
  on duplicate key update count=values(count);
  # 结果显示:
  id   word    count    date 
  1    a       6        2019-06-26  (更新)
  
3. 执行第三次:
  insert into word_count_0626 (word, count, date) values 
  ('a',6,curdate()-1),    // 取前一天,不会冲突
  ('a',7,curdate()) // 冲突
  on duplicate key update count=values(count);
  # 结果显示:
  id   word    count    date 
  1    a       7        2019-06-26  (更新)
  3    a       6        2019-06-25  (新插入)
  
4. 执行第四次:(更新冲突的最后一条插入值)
  insert into word_count_0626 (word, count, date) values 
  ('a',2,curdate()),  // 冲突
  ('a',1,curdate())  // 冲突
  on duplicate key update count=values(count);
  # 结果显示:
  id   word    count    date 
  1    a       1        2019-06-26  (更新最后一条插入值)
  3    a       6        2019-06-25  (不变)
  
5. 执行第五次:(更新冲突的累加插入值)
  insert into word_count_0626 (word, count, date) values 
  ('a',2,curdate()),
  ('a',1,curdate()) 
  on duplicate key update count=count+values(count); // 实现每行累加
  # 结果显示:
  id   word    count    date 
  1    a       4        2019-06-26
  3    a       6        2019-06-25
 
6. 执行第六次:(无冲突插入,观察 id 键值,出现了很多丢失,id 直接跳到了 9)
  insert into word_count_0626 (word, count, date) values 
  ('b',2,curdate())
  on duplicate key update count=count+values(count);
  # 结果显示:
  id   word    count    date 
  1    a       4        2019-06-26
  3    a       6        2019-06-25
  9    b       2        2019-06-26

说明:

insert...on duplicate key方法

简单写一个例子,内容大致如下:

1、首先创建一个包含id,name,age的表,其中id是主键;

2、在这个表中插入一条id=1的记录;

3、使用insert...on duplicate key update语法插入一条id=2的记录;

4、使用同样的语法更新id=1的记录;

mysql 23:12:32>>create table test_1( 
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> age int not null
    -> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql 23:13:26>>insert into test_1 values (1,'yyz',16);
Query OK, 1 row affected (0.01 sec)

mysql 23:13:58>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  16 |
+----+------+-----+
1 row in set (0.00 sec)


mysql 23:14:36>>insert into test_1 (id,name,age) values (2,'yyz',18) on duplicate key update age=18;
Query OK, 1 row affected (0.01 sec)

mysql 23:15:08>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  16 |
|  2 | yyz  |  18 |
+----+------+-----+
2 rows in set (0.00 sec)

mysql 23:15:17>>insert into test_1 (id,name,age) values (1,'yyz',18) on duplicate key update age=18;
Query OK, 2 rows affected (0.00 sec)

mysql 23:15:28>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  18 |
|  2 | yyz  |  18 |
+----+------+-----+
2 rows in set (0.00 sec)

insert...on duplicate key update语法的作用,可以分析到,当发生主键冲突的时候,可以直接进行update操作,这个update操作里面可以更新任意想要更新的列;而没有主键冲突的时候,相当于对这个表进行了一次插入操作。

Replace操作

Replace语句。使用Replace插入一条记录时,如果不重复,Replace就和Insert的功能一样,如果有重复记录,Replace就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将Delete和Insert合二为一,形成一个原子操作。这样就可以不必考虑在同时使用Delete和Insert时添加事务等复杂操作了。

在使用Replace时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则Replace就和Insert完全一样的。

在执行Replace后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了Delete删除这条记录,然后再记录用Insert来插入这条记录。

不同之处

有了上面的知识储备,这两条命令的不同之处就显而易见了,replace是删除记录,然后再重新insert,而insert...on duplicate key update是直接在该条记录上修改,所以二者的差别主要有以下两处:

1、当表中存在自增值的时候,如果表中存在某条记录,replace语法会导致自增值+1,而insert...on duplicate key update语法不会;

2、当表中的某些字段中包含默认值的时候,replace操作插入不完全字段的记录,会导致其他字段直接使用默认值,而insert...on duplicate key update操作会保留该条记录的原有值。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ZYC88888/article/details/104250372

智能推荐

C语言实现 产生可能的集合_c语言集合-程序员宅基地

文章浏览阅读1.8k次,点赞2次,收藏7次。C语言实现 产生可能的集合。给定一组数字或符号,输出产生的所有可能的集合(包括空集合)。 _c语言集合

sqli-labs Less-25、25a(sqli-labs闯关指南 25、25a)_saqi-labs第25关报错注入-程序员宅基地

文章浏览阅读867次。sqli-labs Less-25、25a(sqli-labs闯关指南 25、25a)_saqi-labs第25关报错注入

pygame连续移动图片,并且背景颜色改变_pygame明明已经实现了持续移动-程序员宅基地

文章浏览阅读1.1k次。import sys, pygame,randompygame.init()size = width, height = 1920, 1800black =[(250,235,215),(0, 0, 0),(0,238,238)]screen = pygame.display.set_mode(size)ball = pygame.image.load("11.jpg")ballrect = ball.get_rect()pygame.key.set_repeat(pygame.KEY_pygame明明已经实现了持续移动

H5嵌入原生开发小结----兼容安卓与ios的填坑之路-程序员宅基地

文章浏览阅读251次。一开始听说开发H5,以为就是做适配现代浏览器的移动网页,心想不用管IE了,欧也。到今天,发现当初too young too simple,兼容IE和兼容安卓与IOS,后者让你更抓狂。接下来数一下踩过的坑。主要分UI展示,键盘,输入框等等。解决bug最苦恼的问题不是没有解决方案,而是你没有找到真正的原因。再就是现象难以重现,每次都要发布代码,然后到手机app中去测试,模拟。这些地方会耗费大量的精力。..._ios输入框被键盘挡住的解决办法

一个实习生java面试过程班,秋招,实习,面试,offer之路_java实习生面试流程-程序员宅基地

文章浏览阅读931次。不知不觉已经工作一年多的,我是2019年7月毕业的,但是如果算上实习就工作差不多两年了的吧。最近不是刚刚过了圣诞节吗?然后又准备到元旦了,迎来2021年!在微信公众号上看到小部分公众号在总结2020年了。所以就勾起自己从毕业到现在的回忆,顺便总结一下,自己如何从准备秋招到拿到offer的,算作记录一下自己的另一个阶段。犹记得,当初高考结束的时候后,自己填报的志愿大部分都是计算机相关的,因为从高中开始,就一直对于电脑方面比较感兴趣,可能跟自己小时候喜欢看科幻片有关吧????(一个科幻迷)。小时候,就觉得以_java实习生面试流程

vue项目启动报错 Error: spawn cmd ENOENT_note that the development build is not optimized. -程序员宅基地

文章浏览阅读5.9k次,点赞5次,收藏8次。【代码】vue项目启动报错 Error: spawn cmd ENOENT_note that the development build is not optimized. to create a production bui

随便推点

成功解决for循环语句中,后几次循环输出数据一直全部为空_taro小程序 定时器循环方法里面数据赋值一直为空-程序员宅基地

文章浏览阅读5.5k次,点赞6次,收藏12次。成功解决for循环语句中,后几次循环输出数据一直全部为空目录解决问题解决思路解决方法解决问题for循环语句中,后几次循环输出数据一直全部为空解决思路 数据为空,如果不是数据本身的问题,那么,很有可能是在for循环中,定义变量被覆盖,下次循环中,该变量内的数据已被重新定义,再次循环时该变量已经被改变!解决方法for循环的定义中,切记,变量不要覆盖!..._taro小程序 定时器循环方法里面数据赋值一直为空

Linux>>CentOS 7镜像下载及安装_centos7 2009j最小化版下载-程序员宅基地

文章浏览阅读1.3k次。Linux>>CentOS 7镜像下载下载映像文件,地址https://www.centos.org/download/1.虚拟机配置好后,安装等待就好了…2.出现如下界面选择简体中文,继续…3.进入如下页面,选择分区:添加新的挂载点–>4.选择软件选择,进入如下界面5.选择网络和主机名,开启,然后开始安装…6.设置root密码,密码最好复杂点…7.创建用户,8.完成安装,重启…9.重启后点击许可协议,接受即可,完成配置…10.完成安装_centos7 2009j最小化版下载

java中的数据类型概述_请对java的数据类型所包含的内容进行表述。-程序员宅基地

文章浏览阅读109次。数据类型的作用:数据类型用来声明变量,程序在运行过程中根据不同的数据类型分配不同大小的空间.数据类型在java语言中包括两种:第一种:基本数据类型基本数据类型又可以分为四大类八小种第一类:整数型(byte,short,long,int)第二类:浮点型(float,double)第三类:布尔型(boolean)boolean只有两个值true和false,true表示真,false表示假.第四类:字符型(char)java中规定字符型字面量必须使用单引号括起来.第二种:_请对java的数据类型所包含的内容进行表述。

第三章_简述core dom与html dom访问和修改节点属性值的方法-程序员宅基地

文章浏览阅读591次。第三章 JavaScript操作DOM对象3.1 DOM操作DOM是Document Object Model的缩写,即文档对象模型。3.1.1 DOM操作分类操作DOM是通常分为三类:DOM Core(核心)、HTML-DOM和CSS-DOM。1.DOM CoreDOM Core不是JavaScript的专属品,它的用途不仅限于处理一种使用标记语言编写出来的文档。2.HTML-DOM它提供了一些更简单的标记来描述各种HTML元素的属性。3.CSS-DOMCSS-DOM技术的主要作用是_简述core dom与html dom访问和修改节点属性值的方法

pyecharts 入门之词云图(六)_pyecharts制作词云图-程序员宅基地

文章浏览阅读382次。pyecharts 关于词云图的绘制_pyecharts制作词云图

【Spring Boot 4(1),2021年春招Java面试题-程序员宅基地

文章浏览阅读43次。<artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.1</version> <groupId>com.oracle.ojdbc</groupId> <artifactId>ojdbc8</artifactId> <scope>runtime</scope> ..