文档章节

mysql存在则更新,不存在则插入

LCZ777
 LCZ777
发布于 2015/09/22 19:15
字数 1857
阅读 5580
收藏 24
INSERT INTO ON DUPLICATE KEY UPDATE REPLACE INTO,两个命令可以处理重复键值问题,在实际上它之间有什么区别呢?
前提条件是这个表必须有一个 唯一索引或主键
unique

1、REPLACE发现重复的先删除再插入,如果记录有多个字段,在插入的时候如果有的字段没有赋值,那么新插入的记录这些字段为空。
2、INSERT发现重复的是更新操作。在原有记录基础上,更新指定字段内容,其它字段内容保留。


这样REPLACE的操作成本要大于 insert  ON DUPLICATE KEY UPDATE ,按道理应该选用insert  ON DUPLICATE KEY UPDATE




部分测试如下
2个 都是 影响的数据栏: 2


INSERT语法


INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
       [INTO] tbl_name [(col_name,...)]
       VALUES ({expr | DEFAULT},...),(...),...
       [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或:


INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
       [INTO] tbl_name
       SET col_name={expr | DEFAULT}, ...
       [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或:


INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
       [INTO] tbl_name [(col_name,...)]
       SELECT ...
       [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
 
一、DELAYED 的使用
     使用延迟插入操作

DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候,服务器把数据行放入一个队列中,并立即给客户端返回一个状态信息,这样客户端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据表中读取数据,队列中的数据就会被保持着,直到没有读取者为止。接着服务器开始插入延迟数据行(delayed-row)队列中的数据行。在插入操作的同时,服务器还要检查是否有新的读取请求到达和等待。如果有,延迟数据行队列就被挂起,允许读取者继续操作。当没有读取者的时候,服务器再次开始插入延迟的数据行。这个过程一直进行,直到队列空了为止。


几点要注意事项:

· INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED...SELECT语句的DELAYED。
· 服务器忽略用于INSERT DELAYED...ON DUPLICATE UPDATE语句的DELAYED。
· 因为在行被插入前,语句立刻返回,所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。
· 对于SELECT语句,DELAYED行不可见,直到这些行确实被插入了为止。
· DELAYED在从属复制服务器中被忽略了,因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。

注意,目前在队列中的各行只保存在存储器中,直到它们被插入到表中为止。这意味着,如果您强行中止了mysqld(例如,使用kill -9)或者如果mysqld意外停止,则所有没有被写入磁盘的行都会丢失。


二、IGNORE的使用
IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTER TABLE的运行。如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。并且,对错误值进行修正,使之尽量接近正确值。insert ignore into tb(...) value(...)这样不用校验是否存在了,有则忽略,无则添加 


三、ON DUPLICATE KEY UPDATE的使用
如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:


mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
 
       -> ON DUPLICATE KEY UPDATE c=c+1;
 
 
 
mysql> UPDATE table SET c=c+1 WHERE a=1;
 
如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
 
注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:
 
mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
 
如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。
 
您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义,其它时候会返回NULL。


 
示例:


 
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
 
       -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
 
本语句与以下两个语句作用相同:


 
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
 
       -> ON DUPLICATE KEY UPDATE c=3;
 
mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)
 
       -> ON DUPLICATE KEY UPDATE c=9;
 
当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。


 
 


 
总结:DELAYED 做为快速插入,并不是很关心失效性,提高插入性能。
 
        ignore     只关注主键对应记录是不存在,无则添加,有则忽略。
 

     ON DUPLICATE KEY UPDATE 在添加时操作,关注非主键列,注意与ignore的区别。有则更新指定列,无则添加。

insert into table values() ON DUPLICATE KEY UPDATE field1 =? ,field2  =? 逗号分隔

实例见http://www.itpub.net/forum.php?mod=viewthread&tid=1770206

在最终实践结果中,得到接过如下:
在数据库数据量很少的时候, 这两种方式都很快,无论是直接的插入还是有冲突时的更新,都不错,但在数据库表的内容数量比较大(如百万级)的时候,两种方式就不太一样了,

首先是直接的插入操作,两种的插入效率都略低, 比如直接向表里插入1000条数据(百万级的表(innodb引擎)),二者都差不多需要5,6甚至十几秒。究其原因,我的主机性能是一方面,但在向大数据表批量插入数据的时候,每次的插入都要维护索引的, 索引固然可以提高查询的效率,但在更新表尤其是大表的时候,索引就成了一个不得不考虑的问题了。

其次是更新表,这里的更新的时候是带主键值的(因为我是从另一个表获取数据再插入,要求主键不能变) 同样直接更新1000条数据, replace的操作要比insert on duplicate的操作低太多太多, 当insert瞬间完成(感觉)的时候,replace要7,8s, replace慢的原因我是知道的,在更新数据的时候,要先删除旧的,然后插入新的,在这个过程中,还要重新维护索引,所以速度慢,但为何insert on duplicate的更新却那么快呢。 在向老大请教后,终于知道,insert on duplicate 的更新操作虽然也会更新数据,但其对主键的索引却不会有改变,也就是说,insert on duplicate 更新对主键索引没有影响.因此对索引的维护成本就低了一些(如果更新的字段不包括主键,那就要另说了)。

本文转载自:

LCZ777
粉丝 54
博文 248
码字总数 65899
作品 0
杭州
程序员
私信 提问
Mysql 原生语句中save or update 的各种写法

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

Tek_Eternal
2015/02/09
1K
0
mongodb简单的基础操作

数据库操作 1、查看数据库 查看mongodb中的数据库(类似于mysql中的show databases); > show dbslocal 0.000GBtest 0.000GB 2、使用数据库 如果使用的数据库不存在,mongodb会自动创建对应的数...

mrmusic
2016/03/13
73
0
芝麻HTTP:MySQL存储

只要你的Scrapy Field字段名字和 数据库字段的名字 一样。那么恭喜你你就可以拷贝这段SQL拼接脚本。进行MySQL入库处理。 具体拼接代码如下:

芝麻软件
2018/03/01
22
0
芝麻HTTP: Scrapy小技巧

这两天上班接手,别人留下来的爬虫发现一个很好玩的 SQL脚本拼接。 只要你的Scrapy Field字段名字和 数据库字段的名字 一样。那么恭喜你你就可以拷贝这段SQL拼接脚本。进行MySQL入库处理。 ...

芝麻软件
2018/01/29
14
0
讨人喜欢的 MySQL replace into 用法(insert into 的增强版)

讨人喜欢的 MySQL replace into 用法(insert into 的增强版) 在向表中插入数据的时候,经常遇到这样的情况:1. 首先判断数据是否存在; 2. 如果不存在,则插入;3.如果存在,则更新。 在 ...

凡16
2014/03/07
193
0

没有更多内容

加载失败,请刷新页面

加载更多

Java描述设计模式(14):解释器模式

本文源码:GitHub·点这里 || GitEE·点这里 一、解释器模式 1、基础概念 解释器模式是对象的行为模式。给定一个语言之后,解释器模式可以定义出其文法的一种表示,并同时提供一个解释器。客...

知了一笑
19分钟前
4
0
019、Kubernetes 安装集群

概述 kubeadm 是 kubernetes 的集群安装工具,能够快速安装 kubernetes 集群,安装 kubernetes 主要是安装它的各个镜像,而 kubeadm 已经为我们集成好了运行 kubernetes 所需的基本镜像。但由...

北岩
34分钟前
4
0
Java每日面试题_02

13、Java中实现多态的机制是什么? Java中,靠的是父类或接口定义的引用变量可以指向子类或具体实现类的实例对象,而程序调用的方法在运行期才动态绑定,就是引用变量所指向的具体实例对象的方...

庭前云落
35分钟前
4
0
kafka 生成者,消费者 demo

写生成者和消费者 producer 生产者 public class ProducerDemo {public static void main(String[] args) throws Exception {Properties props = new Properties();props.put("zk......

Garphy
41分钟前
4
0
各版本JDK:JDK6;JDK7;JDK1.8;JDK11;JDK13

百度网盘 链接: https://pan.baidu.com/s/1S1W6XQOEj4m8uRQZ3rMbNA&shfl=shareset 提取码: 1jke jdk-13.0.1 jdk-11.0.5 jdk-8u231 华为镜像 链接: https://repo.huaweicloud.com/java/jdk/......

孑竹三秋
44分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部