MySQL 批量插入：如何不插入重复数据？

• 使用场景
• • 1、insert ignore into
  • 2、on duplicate key update
  • 3、insert … select … where not exist
  • 4、replace into
  • 5、代码中使用线程池进行批量查询,做判断, 批量更新插入

使用场景

最近我老是遇到几万条或者几十万数据的开发, 导入数据的时候肯定是要去重的, 由于刚开始写的时候是一条条数据去查做更新处理, 结果导入数据的时候可想而知, 3万数据花了大半个小时, 客户使用感觉已太慢了, 没得办法了, 只能优化了, 打开百度先看看前辈们是如何优化的, 查看了几分钟, 最终选择了一种合适自已方案的, 优化了一下, 效率是原来的三四十倍, 一分钟不到, 就完成了.
先说一下我查看的都有那些方法.

1、insert ignore into

当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。例如：

INSERT IGNORE INTO user (name) VALUES ('张三')

这种方法很简便，但是有一种可能，就是插入不是因为重复数据报错，而是因为其他原因报错的，也同样被忽略了～

2、on duplicate key update

当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。

例如，为了实现name重复的数据插入不报错，可使用如下语句：

INSERT INTO user (name) VALUES ('张三') ON duplicate KEY UPDATE id = id

这种方法有个前提条件，就是，需要插入的约束，需要是主键或者唯一约束（在你的业务中那个要作为唯一的判断就将那个字段设置为唯一约束也就是unique key）。

Mybatis中使用如下

 insert into user (id,username,mobile_number) values      (     #{item.id},#{item.username},#{item.mobileNumber})ON duplicate KEY UPDATE id = id 

3、insert … select … where not exist

根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：

INSERT INTO user (name) SELECT '张三' FROM table WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1)

这种方法其实就是使用了mysql的一个临时表的方式，但是里面使用到了子查询，效率也会有一点点影响，如果能使用上面的就不使用这个。

4、replace into

如果存在primary or unique相同的记录，则先删除掉。再插入新记录。

REPLACE INTO user SELECT 1, 'telami' FROM books

这种方法就是不管原来有没有相同的记录，都会先删除掉然后再插入。

5、代码中使用线程池进行批量查询,做判断, 批量更新插入

    //线程池数据分批导入    private void batchDeal(List<Map> data, List deptList) throws Exception { int batchNum = 1000;//根据需求自定义 int totalNum = data.size(); //通过取余的新建线程 int pageNum = totalNum % batchNum == 0 ? totalNum / batchNum : totalNum / batchNum + 1; ScheduledThreadPoolExecutor executor = new ScheduledThreadPoolExecutor(pageNum); try {     CountDownLatch countDownLatch = new CountDownLatch(pageNum);     int fromIndex, toIndex;     for (int i = 0; i < pageNum; i++) {  fromIndex = i * batchNum;  toIndex = Math.min(totalNum, fromIndex + batchNum);  List<Map> subData = data.subList(fromIndex, toIndex);  Runnable runnable = () -> {      if (subData != null && subData.size() > 0){   List list = new ArrayList();   try {String str = subData.stream() .map(item -> item.get("唯一主键")) .filter(Objects::nonNull).map(item -> item.toString().trim()) .collect(Collectors.joining(","));String[] arr = str.replace("\"\",", "").split(",");//批量获取List collateralList = aService.list(new QueryWrapper().in(StrUtil.isNotBlank(str), "Id", arr));   for (Map map : subData){    String primaryKey = null;    if (ObjectUtil.isNotEmpty(map.get("唯一主键"))) { primaryKey = map.get("唯一主键").toString().trim(); if (StrUtil.isNotBlank(primaryKey)) {     A a= new A(map, deptList);//自定义构造函数     if (collateralList != null && collateralList.size() > 0) {  for (A c : aList) {      if (primaryKey.equals(c.id())) {   a.setId(c.getId());   a.setUpdateBy("张三");   a.setUpdateTime(new Date());      }  }     }     list.add(tbCollateral);//添加 }else {     failCount++; }    }}aService.saveOrUpdateBatch(list);//批量保存更新   }catch (Exception e){failReason += e.getMessage();e.printStackTrace();//数据回滚,不影响事物正常执行//TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();   }      }      // 发出线程任务完成的信号      countDownLatch.countDown();  };  executor.execute(runnable);     }     // 主线程必须在启动其它线程后立即调用CountDownLatch.await()方法，     // 这样主线程的操作就会在这个方法上阻塞，直到其它线程完成各自的任务。     // 计数器的值等于0时，主线程就能通过await()方法恢复执行自己的任务。     countDownLatch.await(); } finally {     // 关闭线程池，释放资源     executor.shutdown(); }    }

字体下载