高并发下数据库插入重复数据，有什么好方法

MySql避免重复插入记录的几种方法

本文章来给大家提供三种在mysql中避免重复插入记录方法，主要是讲到了ignore,Replace,ON DUPLICATE KEY UPDATE三种方法，有需要的朋友可以参考一下

方案一：使用ignore关键字

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

复制代码 代码如下:

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '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 ('test569', '99999', '123')

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

复制代码 代码如下:

if not exists (select phone from t where phone= '1') insert into t(phone, update_time) values('1', getdate()) elseupdate 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

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语句。

Code

CREATETABLE"#temp"AS (SELECTDISTINCT * FROM 表名)--创建临时表，并把DISTINCT 去重后的数据插入到临时表中

truncateTABLE 表名--清空原表数据

INSERTINTO 表名(SELECT * FROM"#temp")--将临时表数据插入到原表中

DROPTABLE"#temp"--删除临时表

具体思路是，首先创建一个临时表，然后将DISTINCT之后的表数据插入到这个临时表中；然后清空原表数据；再讲临时表中的数据插入到原表中；最后删除临时表。

二、部分数据去重方法

首先查找重复数据

select 字段1,字段2,count(*) from 表名 groupby 字段1,字段2 havingcount(*) >1

将上面的>号改为=号就可以查询出没有重复的数据了。

想要删除这些重复的数据，可以使用下面语句进行删除：

deletefrom 表名 a where 字段1,字段2 in

(select 字段1,字段2,count(*) from 表名 groupby 字段1,字段2 havingcount(*) >1)

上面的语句非常简单，就是将查询到的数据删除掉。不过这种删除执行的效率非常低，对于大数据量来说，可能会将数据库卡死。

基于上述情况，可以先将查询到的重复的数据插入到一个临时表中，然后对进行删除，这样，执行删除的时候就不用再进行一次查询了。如下：

CREATETABLE 临时表 AS

(select 字段1,字段2,count(*) from 表名 groupby 字段1,字段2 havingcount(*) >1)

下面就可以进行这样的删除 *** 作了：

deletefrom 表名 a where 字段1,字段2 in (select 字段1，字段2 from 临时表)

先建临时表再进行删除的 *** 作要比直接用一条语句进行删除要高效得多。

上面的语句会把所有重复的全都删除，在oracle中，有个隐藏了自动rowid，里面给每条记录一个唯一的rowid，我们如果想保留最新的一条记录，我们就可以利用这个字段，保留重复数据中rowid最大的一条记录就可以了。

下面是查询重复数据的一个例子：

select a.rowid,a.* from 表名 a

where a.rowid !=

(

selectmax(b.rowid) from 表名 b

where a.字段1 = b.字段1 and

a.字段2 = b.字段2

)

上面括号中的语句是查询出重复数据中rowid最大的一条记录。而外面就是查询出除了rowid最大之外的其他重复的数据了。

由此，我们要删除重复数据，只保留最新的一条数据，就可以这样写了：

deletefrom 表名 a

where a.rowid !=

(

selectmax(b.rowid) from 表名 b

where a.字段1 = b.字段1 and

a.字段2 = b.字段2

)

同理，上述代码的执行效率毕竟低，所以我们可以考虑建立临时表，将需要判断重复的字段、rowid插入临时表中，然后删除的时候在进行比较。

createtable 临时表 as

select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUPBY a.字段1,a.字段2

deletefrom 表名 a

where a.rowid !=

(

select b.dataid from 临时表 b

where a.字段1 = b.字段1 and

a.字段2 = b.字段2

)

commit

示例

假设存在一个产品信息表Products，其表结构如下：

CREATE TABLE Products (  

ProductID int,  

ProductName nvarchar (40),  

Unit char(2),  

UnitPrice money  

)

表中数据如图：

图中可以看出，产品Chang和Tofu的记录在产品信息表中存在重复。现在要删除这些重复的记录，只保留其中的一条。步骤如下：

第一步——建立一张具有相同结构的临时表

CREATE TABLE Products_temp (  

ProductID int,  

ProductName nvarchar (40),  

Unit char(2),  

UnitPrice money  

)

第二步——为该表加上索引，并使其忽略重复的值

方法是在企业管理器中找到上面建立的临时表Products _temp，单击鼠标右键，选择所有任务，选择管理索引，选择新建。如图2所示。

按照图2中圈出来的地方设置索引选项

第三步——拷贝产品信息到临时表

insert into Products_temp Select * from Products

此时SQL Server会返回如下提示：

服务器: 消息 3604，级别 16，状态 1，行 1

已忽略重复的键。

它表明在产品信息临时表Products_temp中不会有重复的行出现。

第四步——将新的数据导入原表

将原产品信息表Products清空，并将临时表Products_temp中数据导入，最后删除临时表Products_temp。

delete Products  insert into Products select * from Products_temp drop table Products_temp

这样就完成了对表中重复记录的删除。无论表有多大，它的执行速度都是相当快的，而且因为几乎不用写语句，所以它也是很安全的

---