mysql 插入重复数据_MySQL插入重复数据-程序员宅基地

技术标签: mysql 插入重复数据  

MySQL中批量insert into时防止更新插入重复数据去重的方法,主要是讲到了ignore,Replace,ON DUPLICATE KEY UPDATE三种方法

方案一:使用ignore关键字

如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用:

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('[email protected]', '13112345678', '9999');

这样当有重复记录就会忽略,执行后返回数字0

还有个应用就是复制表,避免重复记录:

INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`;

方案二:使用Replace

语法格式:

代码如下:

REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);

REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;

REPLACE INTO `table_name` SET `col_name`='value',

...算法说明:

REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值,则在新记录被插入之前,旧记录被删除,即:

尝试把新行插入到表中

当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时:

从表中删除含有重复关键字值的冲突行

再次尝试把新行插入到表中

旧记录与新记录有相同的值的判断标准就是:

表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义。该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行。

返回值:

REPLACE语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和

受影响的行数可以容易地确定是否REPLACE只添加了一行,或者是否REPLACE也替换了其它行:检查该数是否为1(添加)或更大(替换)。

示例:

# eg:(phone字段为唯一索引)

REPLACE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('[email protected]', '13112345678', '123');

另外,在 SQL Server 中可以这样处理:

复制代码代码如下:

if not exists (select phone from t where phone= '1')   insert into t(phone, update_time) values('1', getdate()) else    update t set update_time = getdate() where phone= '1'

方案三:ON DUPLICATE KEY UPDATE

如‍上所写,你也可以在INSERT INTO…..后面加上 ON DUPLICATE KEY UPDATE方法来实现。如果您指定了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。

注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:

复制代码代码如下:

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。

复制代码代码如下:

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`);

本语句与以下两个语句作用相同:

复制代码代码如下:

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=3;

INSERT INTO `table` (`a`, `b`, `c`) VALUES (4, 5, 6) ON DUPLICATE KEY UPDATE c=9;

注释:当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

示例:

这个例子是我在实际项目中用到的:是将一个表的数据导入到另外一个表中,数据的重复性就得考虑(如下),唯一索引为:email:

复制代码代码如下:

INSERT INTO `table_name1` (`title`, `first_name`, `last_name`, `email`, `phone`, `user_id`, `role_id`, `status`, `campaign_id`)

SELECT '', '', '', `table_name2`.`email`, `table_name2`.`phone`, NULL, NULL, 'pending', 29 FROM `table_name2`

WHERE `table_name2`.`status` = 1

ON DUPLICATE KEY UPDATE `table_name1`.`status`='pending'

再贴一个例子:

复制代码代码如下:

INSERT INTO `class` SELECT * FROM `class1` ON DUPLICATE KEY UPDATE `class`.`course`=`class1`.`course`

其它关键:DELAYED  做为快速插入,并不是很关心失效性,提高插入性能。

IGNORE  只关注主键对应记录是不存在,无则添加,有则忽略。

特别说明:在MYSQL中UNIQUE索引将会对null字段失效,也就是说(a字段上建立唯一索引):

复制代码代码如下:

INSERT INTO `test` (`a`) VALUES (NULL);

是可以重复插入的(联合唯一索引也一样)。

在插入大量数据时可能同时遇到两个问题:

如果每条更新执行一次sql性能很低,也容易造成阻塞;

批量插入时又有可能遇到主键 或者 唯一索引重复的问题,导致整个批量插入失败

使用 ON DUPLICATE KEY UPDATE 一条sql解决批量插入和主键或者唯一索引重复问题

举例如下:

表结构如下:

create table mytable(

id int not null,

code char(6) not null,

description char(50) null,

number int not null,

name char(50) not null,

primary key id,

unique key uk_mytable_code (code));

批量插入如下:

INSERT INTO mytable(id, code, description, number, name)

VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)

ON DUPLICATE KEY UPDATE description = VALUES(description), number = VALUES(number)

//description=VALUES(description),number = VALUES(number) 表示出现在values中某列的id字段值与表中已有id字段值重复

(或者code的值与表中已有code字段值重复)时,会更新对应已存在记录的这两个字段

//还可以指定其它值或进行运算:number=number+ 1,number=number- 1

//因为这里未指定列name, 所以遇到重复id(或者code)列,表中该列的name字段不会更新

//如果某列作为新记录被插入,则受影响行的值为1;如果表中原有的记录被更新,则受影响行的值为2

Sqlite 批量操作时防止插入重复数据的方法:

在SQLITE中,可以用以下方法防止插入重复数据,

1.设置表的联合主键

2.在INSERT语句中添加OR IGNORE,即可(重复数据会跳过,且不影响后续数据插入)

示例如下:

insert OR IGNORE into PDA_Test (DeviceID,MethodName) values('1112','测试原有');

sqlite避免重复插入数据。

SQLite 数据库常用约束如下:

NOT NULL - 非空

UNIQUE - 唯一

PRIMARY KEY - 主键

FOREIGN KEY - 外键

CHECK - 条件检查

DEFAULT - 默认

用insert语句插入数据,为避免重复插入又不打断数据处理。

首先要避免重复插入,就必须在插入时引发冲突。在表中设置了id字段,该字段为UNIQUE属性,当插入的id已存在时引发冲突。

引发冲突后insert会做一些处理,处理方式由OR字句定义。包含如下:

ROLLBACK当发生约束冲突,立即ROLLBACK,即结束当前事务处理,命令中止并返回SQLITE_CONSTRAINT代码。若当前无活动事务(除了每一条命令创建的默认事务以外),则该算法与ABORT相同。

ABORT当发生约束冲突,命令收回已经引起的改变并中止返回SQLITE_CONSTRAINT。但由于不执行ROLLBACK,所以前面的命令产生的改变将予以保留。缺省采用这一行为。

FAIL当发生约束冲突,命令中止返回SQLITE_CONSTRAINT。但遇到冲突之前的所有改变将被保留。例如,若一条UPDATE语句在100行遇到冲突100th,前99行的改变将被保留,而对100行或以后的改变将不会发生。

IGNORE当发生约束冲突,发生冲突的行将不会被插入或改变。但命令将照常执行。在冲突行之前或之后的行将被正常的插入和改变,且不返回错误信息。

REPLACE当发生UNIQUE约束冲突,先存在的,导致冲突的行在更改或插入发生冲突的行之前被删除。这样,更改和插入总是被执行。命令照常执行且不返回错误信息。当发生NOT NULL约束冲突,导致冲突的NULL值会被字段缺省值取代。若字段无缺省值,执行ABORT算法

Sqlite执行insert or ignore 或insert or replace语句。

insert or replace into cardlog (mid,type) values (7,0);

insert or ignore into cardlog (mid,type) values (7,0);

上面的第一条语句是每次执行时,如果不存在,则添加,如果存在,则更新。

上面的第二条语句是每次执行时,如果不存在,则添加,如果存在,则不操作。

在MSSQL中,你可以使用诸如:

IF NOT EXISTS(SELECT * FROM Book WHERE ….) THEN INSERT INTO ... ELSE UPDATE SET ...

转自链接:https://www.365jz.com/article/24852,侵删

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

智能推荐

JScript 是 JavaScript 的缩写吗?_javascript的小写缩写是?-程序员宅基地

文章浏览阅读3.3k次。JScript 是 JavaScript 的缩写吗? 资策会数字教育研究所讲师 杨宏文  很多人都误以为JScript就是JavaScript的缩写,这一点是需要澄清的,让笔者先讲一段故事。 在Web Scripting language世界中第一个诞生的是,由网景公司(NetScape)所催生的JavaScript。到_javascript的小写缩写是?

iOS开发-设置启动图片LaunchImage_ios创建 launchimage-程序员宅基地

文章浏览阅读1.3k次。通常app在开启的时候都会有自己的启动图片,下面就开始设置app的启动图片。第一步:在图片资源包里创建LaunchImage。然后就创建好了LaunchImage,只不过一开始是空的,而这里我已经添加好了图片。注意:一定要按照尺寸添加全部图片,不然会报错。附上:iOS-AppIcon和LaunchImage尺寸附上仅供学习使用:配置好一部分的LaunchImage下载..._ios创建 launchimage

Mock进行单元测试_单体测试用mock吗-程序员宅基地

文章浏览阅读2.6k次,点赞4次,收藏17次。中文文档参考地址,点击这里汪文君Mockito实战视频汪文君Mockito源码,点击这里@InjectMocks用来修饰被测试的类:就是对哪个类中的方法进行单元测试的时候,就用该注解修饰这个类。InjectMocks创建这个类的对象并自动标记@Mock,@Spy等注解的属性注入到这个中。要求必须是类不能是接口。使用1.Maven<!-- https://mvnrepository.com/artifact/org.mockito/mockito-all --> &l_单体测试用mock吗

.tar.gz 跟.tgz_tgz和tar.gz-程序员宅基地

文章浏览阅读2.6k次。.tar.gz 跟.tgz 是一种格式吗?tar.gz_百度百科以.tar.gz为后缀的文件是一种压缩文件,在Linux和macOS下常见,Linux和macOS都可以直接解压使用这种压缩文件。_tgz和tar.gz

yolov3代码详细解读_yolov3代码详解-程序员宅基地

文章浏览阅读3.4k次,点赞15次,收藏73次。下文所有代码:https://pan.baidu.com/s/1p-Q-edFXXcvzxlZNd9saOw 提取码:x72s原理可以参考:yolov1-v5学习笔记及源码解读目录1 目录结构2 train.py2.1 数据读取 dataset.py2.2 网络搭建 models.py2.2.1 搭建模型2.2.2 yolo层的实现2.2.3 darknet进行模型前向传播3 test.py4 detect.py5 功能脚本5.1 utils.py:5.2 logger.py5.3 augment._yolov3代码详解

一步步制作Arduino智能小车_arduino小车-程序员宅基地

文章浏览阅读1.4w次,点赞21次,收藏285次。小白第一次实战_arduino小车

随便推点

Java购物小系统(使用最基础的语法实现的控制台购物系统)_java控制台采购系统-程序员宅基地

文章浏览阅读2.6k次。自述:本人是一名卑微专科大一软件技术学生,目前学习Java已经快三个月了吧,在这里给大家分享一下我的学习成果,希望看到的大佬可以给我一些建议,也希望这篇文章能给正在学习Java的小伙伴一些练习的途径:程序执行的入口是(InSystem.java)文件总共分为十一个Java文件 客户信息管理 添加客户信息 创建一个客户类,里面声明客户应具备的客户信息,如:客户编号、客户姓名、客户电话、客户积分等,声明该类的对象数组,利用对象数组存储客户的信息,_java控制台采购系统

Android开发 轻量缓存框架--ASimpleCache_android 轻量缓存文件-程序员宅基地

文章浏览阅读339次。做Android应用开发的同学们相信对“缓存”这个词不陌生,缓存可能有多方面的概念,这里大概列举下程序开发的缓存大概有哪些:1.服务端控制缓存如volley请求库,便是通过服务端的“Cache-Control”和“max-age”来告诉客户端有没有缓存以及缓存的时间,也是推荐的使用方式,但是需要服务端配合,比较灵活。2.客户端直接控制缓存有些时候不需要服务端来支持的_android 轻量缓存文件

《机器人动力学与控制》第七章—路径规划与避障 7.3 工作空间下使用人工势场法进行路径规划_排斥势场-程序员宅基地

文章浏览阅读1.8k次。上一节我们发现,在关节空间下,很难获得一个直白的障碍物空间QO的表达,也很难在关节空间下评估人工势场的梯度。事实上,对于一个曲面,一般来说并不存在一个描述点到曲面的闭合表达式。即便我们能够表达清楚QO,仍然很难获得关节空间下的梯度ρ。为了解决这些问题,我们将对上一节说到的人工势场法做出一点改进,使势场方程定义在工作空间 W 而不是关节空间 Q。本节我们会先讲解如何一个在工作空间里正确地描述势场,..._排斥势场

CSS学习笔记-程序员宅基地

文章浏览阅读166次,点赞2次,收藏6次。CSS学习笔记本文章是基于B站pink老师的视频和PPT,结合个人认为重点的部分以及自身模糊的知识点进行归纳。一、CSS(1)1. CSS简介CSS 是层叠样式表 ( Cascading Style Sheets ) 的简称。有时我们也会称之为 CSS 样式表或级联样式表。CSS 是也是一种标记语言 。2. CSS基础选择器2.1 选择器分类选择器分为基础选择器和复合选择器两个大类,我们这里先讲解一下基础选择器。基础选择器是由单个选择器组成的基础选择器又包括:标签选择器、 类选

北斗卫星与GPS介绍_北斗系统能获取z轴数据了吗-程序员宅基地

文章浏览阅读3.3k次。学习过空间解析几何的同学都知道,在一个立体直角坐标系中,任何一个点的位置都可以通过三个坐标数据X、Y、Z来得到确定。也就是说,只要能得到X、Y、Z三个坐标数据,就可以确知任何一点在空间中的位置。如果能测得某一点与其它三点A、B、C的距离,并确知A、B、C三点的坐标,就可以建立起一个三元方程组,解出该未知点的坐标数据,从而得到该点的确切位置。  GPS就是根据这一原理,在太空中建立了一个由24颗_北斗系统能获取z轴数据了吗

Java word导出,可导出一份word或多页word内容_有模板的word导出 需要导出多份 一个文件 java-程序员宅基地

文章浏览阅读6.4k次。一步步按着写,图片看不清的,右键打开新标签页查看图片,谢谢htmla href="javascript:void(0)" class="easyui-linkbutton" plain="true" id="btn_customOpen">导出worda>前端js(ids为传递参数)$.messager.confirm('温馨提示','确认打印选中用户信息!',functio_有模板的word导出 需要导出多份 一个文件 java