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

一时失言乱红尘 2022-10-10 01:45 431阅读 0赞

### 文章目录 ###

*   *  1. 需求分析
     *  2. on duplicate key update
     *   *  （1）说明
         *  （2）示例
     *  2. insert … select … where not exist
     *   *  （1）说明
         *  （2）示例
     *  3. replace into
     *   *  （1）说明
         *  （2）示例

## 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为主键  
![在这里插入图片描述][2021062517202873.png]  
【1】测试示例一  
针对staff\_id=1的记录执行下面的插入语句

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

![在这里插入图片描述][20210625172048791.png]

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

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

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

![在这里插入图片描述][20210625172102495.png]  
![在这里插入图片描述][20210625172105895.png]  
注意此时数据已被更新

## 2. insert … select … where not exist ##

### （1）说明 ###

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

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

### （2）示例 ###

【需求】  
当前有一个staff表，在插入时需要不能重复插入username和last\_name相同的用户信息，目前表结构没有二者的约束，重复则跳过不插入  
![在这里插入图片描述][20210625172133692.png]

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' )

![在这里插入图片描述][20210625172141931.png]

## 3. replace into ##

### （1）说明 ###

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

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

### （2）示例 ###

原表有一个id为1的数据，当再次插入一条ID为1的数据时  
![在这里插入图片描述][20210625172202576.png]

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

![在这里插入图片描述][20210625172209828.png]

[2021062517202873.png]: /images/20221005/1dad4c38a9d14a08b484e09d2a511a8e.png
[20210625172048791.png]: /images/20221005/4b1721c91b4e47bf932abef326bbbb21.png
[20210625172102495.png]: /images/20221005/3954367ecb4046cc961f877a2f9a2678.png
[20210625172105895.png]: /images/20221005/9dba4f94292f41138fa0b7c70427c449.png
[20210625172133692.png]: /images/20221005/c72615b6902c473b8bd43e095eeb64b0.png
[20210625172141931.png]: /images/20221005/30559a63e78444f4a329e0e305722bf9.png
[20210625172202576.png]: /images/20221005/86c13d7e08694a53b77c300ea0b402c0.png
[20210625172209828.png]: /images/20221005/79d88639406e4384a8954be8a7c43edc.png