本文章来给大家提供三种在mysql中避免重复插入记录方法,主要是讲到了ignore,Replace,ON DUPLICATE KEY UPDATE三种方法,各位同学可尝试参考。
案一:使用ignore关键字
如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用:
代码如下 | 复制代码 |
1 INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999'); |
这样当有重复记录就会忽略,执行后返回数字0
还有个应用就是复制表,避免重复记录:
代码如下 | 复制代码 |
1 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 ('test569', '99999', '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' |
更多信息请看:http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#replace
方案三: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; |
如果行作为新记录被插入,则受影响行的值为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; |
注释:当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。
示例:
这个例子是我在实际项目中用到的:是将一个表的数据导入到另外一个表中,数据的重复性就得考虑(如下),唯一索引为:email:
代码如下 | 复制代码 |
INSERT INTO `table_name1` (`title`, `first_name`, `last_name`, `email`, `phone`, `user_id`, `role_id`, `status`, `campaign_id`) |
再贴一个例子:
代码如下 | 复制代码 |
1 INSERT INTO `class` SELECT * FROM `class1` ON DUPLICATE KEY UPDATE `class`.`course`=`class1`.`course` |
其它关键:DELAYED 做为快速插入,并不是很关心失效性,提高插入性能。
IGNORE 只关注主键对应记录是不存在,无则添加,有则忽略。
更多信息请看: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert
特别说明:在MYSQL中UNIQUE索引将会对null字段失效,也就是说(a字段上建立唯一索引):
代码如下 | 复制代码 |
1 INSERT INTO `test` (`a`) VALUES (NULL); |
是可以重复插入的(联合唯一索引也一样)。
原文地址:http://www.111cn.net/database/mysql/50135.htm
相关推荐
防止mysql重复插入记录的方法有很多种,常用的是ignore,Replace,ON DUPLICATE KEY UPDATE,当然我们也可以在php中加以判断了。 方案一:使用ignore关键字 如果是用主键primary或者唯一索引unique区分了记录的唯一性,...
如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用: 代码如下: INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES (‘test9@163.com’, ‘99999’, ...
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_...
insert on duplicate key update insert ignore insert ignore 会忽视数据库中已经存在的数据,根据主键或者唯一索引判断,如果数据库没有数据,就会插入新的数据,如果有数据的话就跳过这条数据 小case 表结构 ...
本篇文章主要介绍在插入数据到表中遇到键重复避免插入重复值的处理方法,主要涉及到IGNORE,ON DUPLICATE KEY UPDATE,REPLACE;接下来就分别看看这三种方式的处理办法。 IGNORE 使用ignore当插入的值遇到主键...
当unique列在一个UNIQUE键上插入包含重复值的记录时,我们可以控制MySQL如何处理这种情况:使用IGNORE关键字或者ON DUPLICATE KEY UPDATE子句跳过INSERT、中断操作或者更新旧记录为新值。 mysql> create table ...
当unique列在一个UNIQUE键上插入包含重复值的记录时,我们可以控制MySQL如何处理这种情况:使用IGNORE关键字或者ON DUPLICATE KEY UPDATE子句跳过INSERT、中断操作或者更新旧记录为新值。
一、背景 以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题:Step8:SQL Server ...(一) 导入差异数据,忽略重复数据,IGNORE INTO的使用 在MySQL创建表的时候,我们通常创建一个表的时候是以一个自增
【有重复的 唯一索引键 就忽略】 前言 最近这个需求是 php 接收前端数据,然后将数据存入数据库中,但相同记录不能存入。 查了查百度,都把简单的事情复杂化了,竟然还有先查询数据表再判断插入的方式(大拇指),通通...
insert的语法 代码如下: INSERT [LOW_PRIORITY | ... [ ON DUPLICATE KEY UPDATE col_name=expr, … ] 或: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr |
1.插入的数据来源自其他表 表A有id, cola 字段 ...现表A中有一部分记录了,再做如1中的插入操作可能遇到重复的key导致操作失败 代码如下: insert ignore into A(id,cola) select id,cola from B where
Laravel在重复的密钥上插入并忽略插入 不推荐使用此软件包,因为已将upsert和insertOrIgnore添加到Laravel。 如果需要透视功能,则可以轻松实现: BelongsToMany :: macro ( 'attachUpsert' , function ( $ id , ...
不要在MySQL上使用INSERT ON DUPLICATE KEY UPDATE ,因为只有当您非常谨慎地创建唯一索引时,这才起作用。 比使用ActiveRecord模拟ups的速度快70–90%。 支持MRI和JRuby。 用法 您传递一个唯一
我前两天在相关网站看见其相关的资料,觉得挺好,就拿出来供大家分享。 INSERT语法 INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 或:
于是我详细询问了一下他的需求,最终得知,这个过程中重复的数据只需要保存一条就可以了,可以容忍一部分数据丢失,而重复的字段恰好是时间字段,这样的话,只需要保证每个时间点都有一条记录即可。 听到这里,我...
单表的MySQL UPDATE语句: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] 多表的UPDATE语句...
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 …] [WHERE where_definition] [ORDER BY …] [LIMIT row_count] 其次,多表的UPDATE语句: UPDATE [LOW_PRIORITY] [IGNORE] ...