SQL批量插入—针对重复数据插入的总结

1. 需求分析

需要批量插入一些数据，数据来源可能是其他数据库的表，也可能是一个外部excel的导入。那么问题来了，是不是每次插入之前都要查一遍，看看重不重复，在代码里筛选一下数据，重复的就过滤掉呢？

向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率。

2. on duplicate key update

（1）说明

1：当primary或者unique重复时，则执行update语句，这里说的update语句指的是遇到重复时执行on duplicate key update后的语句

2：如果ON duplicate KEY UPDATE username= username则表示遇到重复错误时不报错，同时也不会更新数据，这个id指的的对比的字段

3：：如果ON duplicate KEY UPDATE username = “bob”,则表示遇到冲突时将冲突的这条记录，执行UPDATE username = “bob”,即表示遇到冲突时会更新数据

（2）示例

当前有staff表，有一条测试数据，其中staff_id为主键

【1】测试示例一
针对staff_id=1的记录执行下面的插入语句

INSERT INTO staff (staff_id, username) VALUES (1,'bob') ON duplicate KEY UPDATE username = username;

【结果】
不会报错也不会执行执行，即忽略当前执行语句

【2】测试示例二
针对staff_id=1的记录执行下面的插入语句

INSERT INTO staff (staff_id, username) VALUES (1,'bob') ON duplicate KEY UPDATE username = "bob";

注意此时数据已被更新

2. insert … select … where not exist

（1）说明

上面描述的场景是必须是primary 和unique约束来判断，但是在很多的场景中我们不能去修改原表的约束，根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。
这种方法其实就是使用了mysql的一个临时表的方式，但是里面使用到了子查询，效率也会有一点点影响，如果能使用上面的就不使用这个。

【总结】
1：不需要要求严格的约束表结构限制
2：可以设置多条件联合判断

（2）示例

【需求】
当前有一个staff表，在插入时需要不能重复插入username和last_name相同的用户信息，目前表结构没有二者的约束，重复则跳过不插入

INSERT INTO staff (username ,last_name ) SELECT  'Alice' ,'Charles'  FROM dual WHERE NOT EXISTS (SELECT 1 FROM staff WHERE username='Alice' and  last_name='Charles' )

3. replace into

（1）说明

如果存在primary or unique相同的记录，则先删除掉。再插入新记录。这种方法就是不管原来有没有相同的记录，都会先删除掉然后再插入。

当前情况比较适用于数据完整性要求不高的情况，比如日志数据，只要保证插入即可不要报错影响主干业务

（2）示例

原表有一个id为1的数据，当再次插入一条ID为1的数据时

REPLACE INTO staff  (staff_id, username,last_name)  SELECT 1, 'Alice' ,'Charles' FROM dual