Mysql的奇技淫巧 - 避免重复插入数据

MySql

避免重复插入数据

通常那我们插入一条带有唯一性字段数据的时候，我们先去查一下数据库有没有相同数据，需要操作2次数据库，总感觉这样做特别的low，那么咱们来看下怎么操作一次数据库实现来这样的需求，当然这个唯一性数据要给他设置上主键索引或者唯一索引

1、初始演示表：

(1) 测试表结构

字段类型是否null默认说明idintno主键 自增namevarchar（200）no唯一索引urlvarchar（200）no..

（2）拥有数据

mysql> select *from test;+----+------+------+| id | name | url |+----+------+------+| 1 | 1 | 1 |+----+------+------+1 row in set (0.00 sec)

2、使用ignore关键词

mysql> INSERT IGNORE INTO `test` ( `name`, `url`) VALUES ( 'sqc', '6666'),('1','3333');Query OK, 1 row affected (0.03 sec)Records: 2 Duplicates: 1 Warnings: 0受影响的行: 1时间: 0.029s[正常SQL]INSERT INTO `test` ( `name`, `url`) VALUES ( 'sqc11', '6666'),('111','3333');受影响的行: 2时间: 0.042s可以看到ignore效率还是可观的

结果：

mysql> select *from test+----+------+------+| id | name | url |+----+------+------+| 1 | 1 | 1 || 4 | sqc | 6666 |+----+------+------+2 rows in set (0.00 sec)

可以看到当有重复记录就会忽略,不进行插入操作，执行后返回数字0 其他还有类型用法如：(复制表,并且避免重复记录)

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

3、使用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（添加）或2（替换）。替换过程影响行数会翻倍[SQL]REPLACE INTO `test` ( `name`, `url`) VALUES ( 'sqc', '6666'),('1','3333');受影响的行: 4时间: 0.025s发生了2次替换！[SQL]REPLACE INTO `test` ( `name`, `url`) VALUES ( 'sqc', '6666'),('2','3333');受影响的行: 3时间: 0.025s发生了1次替换！

4、ON DUPLICATE KEY UPDATE 方法

唯一键有重复的执行更新

注意：1、应尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。2、当您使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。3、ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！4、特别说明：在MYSQL中UNIQUE索引将会对null字段失效

例如，将上面的test表结构更改下 删除name（唯一索引）字段 ，列id 为 主键 （或 拥有UNIQUE索引），并且包含值1，则以下两个语句具有相同的效果：

INSERT INTO test (id,url) VALUES (1,3) ON DUPLICATE KEY UPDATE url=url+1;UPDATE test SET url=url+1 WHERE id=1;如果行作为新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2。 [SQL]INSERT INTO test (id,url) VALUES (1,3) ON DUPLICATE KEY UPDATE url=url+1;受影响的行: 2时间: 0.030s

这个语法还可以这样用:

执行前url = 1INSERT INTO test (id,url) VALUES (1,3),(1,444) ON DUPLICATE KEY UPDATE url=url+1;[SQL]INSERT INTO test (id,url) VALUES (1,3),(1,444) ON DUPLICATE KEY UPDATE url=url+1;受影响的行: 4时间: 0.030s执行后url = 3 (第二条与第一条重复, url在原值上又+1).

大家可以看到上面并没有用到咱们插入是传给url的值,如 (id,url) VALUES (1,3),(1,444),引用被插入的col的值使用VALUES函数 如：

INSERT INTO test (id,url) VALUES (1,55) ON DUPLICATE KEY UPDATE url=VALUES(url);执行后url的值等于55

总结

这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。insert ignore能忽略重复数据，只插入不重复的数据。replace into和insert ... on duplicate key update，都是替换原有的重复数据， 区别在于replace into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变； insert ... on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。

附：

有人对ON DUPLICATE KEY UPDATE 方法和 使用Replace在效率上的表现进行过研究，这里我直接给大家总结下结论吧:

在数据库数据量很少的时候，　这两种方式都很快，无论是直接的插入还是有冲突时的更新，都不错如果只是插入操作，比如直接向表里插入１０００条数据(百万级的表(innodb引擎))，二者都差不多需要５，６甚至十几秒；可见插入效率也是半斤八两更新表的时候replace的操作要比insert on duplicate的效率低很多，因为replace会先执行删除后执行插入，插入的主键id是自增的（不复用旧的），insert　on duplicate　更新对主键索引没有影响.因此对索引的维护成本就低了一些(如果更新的字段不包括主键，那就要另说了)