一种批量插入数据saveIgnore并返回主键的方法

2017/05/25 08:52:18 No Comments

在mysql中,提供了save ignore语法,用于在插入数据如果出现冲突时忽略信息的处理方式.在这种情况下,一般主键id是通过自动生成,ignore通过一些惟一索引进行控制.在程序中的期望即如果惟一索引不冲突就插入新的数据,如果冲突则不再插入. 但都希望能够在处理成功之后拿到这些数据的主键(不管是之前的还是新插入的),以便于后续进行处理.相应的简单业务逻辑如下所示.

        List<T> personList = xxxList;
        mysql.saveIgnore(personList);
        
        //进行后续逻辑,如转账
        personList.forEach(t-> {
            mysql.addMoney(t.getId(), 100);
        });

在标准的jdbc中,如果是save,或者是mysql的save values(value1) (value2) 这种语法,是能够通过 statement.getGeneratedKeys()返回自动生成的主键.但对于save ignore无效,mysql并不是返回由于冲突处理的之前的主键信息.

常规的作法就是在save ignore之后,再通过相应的惟一索引来进行查询. 类似如下的sql方式

select id from t where t.name in(name1,name2);

这种方式,对于如果惟一索引仅有一列是没有问题的.但如果是多列组成的,则生成如下的sql

select id from t where (t.name,t.code) in ((name1,code1),(name2,code2));

理论上,这种方式也没有问题,但是在mysql中,这种语法并不能命中相应的索引,会造成全局扫描(验证版本5.5,5.6)
修改in版本为如下sql

select id from t where (t.name = name1 and t.code = code1) or (t.name = name2 and t.code = code2);

这种方式可以命中索引,但是整个sql会变得很长,可以看出,相应的惟一索引列会出现多次.如果是5000个数据处理(批量处理肯定数据会很多才有意义),则整个sql会很长.

本文的方式是通过临时表,提前插入索引数据,再通过表关联来获取相应的数据.这样可以避免上面sql过长的问题,并且利用mysql内存表快速处理数据.整个原理可以理解为以下几个步骤

  1. 创建临时表,表数据仅为惟一索引要求列,并建立相应的惟一索引
  2. 插入待处理数据中指定属性到临时表当中
  3. 使用save ignore语法插入数据到实际表中
  4. 两表关联,使用临时表关联实际表,指定相应的惟一列进行关联条件,查询出主键信息
  5. 程序中将主键处理到相应的对象中

(more…)