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

MySql

避免重复插入数据

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

1、初始演示表:

(1) 测试表结构

字段 类型 是否null 默认 说明
id int no 主键 自增
name varchar(200) no 唯一索引
url varchar(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 = 1
INSERT 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在效率上的表现进行过研究,这里我直接给大家总结下结论吧:

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