MySQL批量插入返回自增ID的问题

业务场景

现需将表A中的数据经过转换后迁移入表B,并将一些附加数据存入表C,表C通过一个BId字段来与表B中的数据进行关联。

一个很简单的业务场景,做起来也很简单。由于每向B中插入一条记录,都需要向C中插入这条记录的附加数据,而向C中插入记录时需要B的主键BId,所以需要先向B插记录并得到插入的id,使用Mybatis的useGeneratedKeys选项,很容易就可以做到。

批量插入

现在的问题是,表A的数据量比较大,我就想,如果一条一条的插入,会不会比较慢。。如果用批量插入呢?对于批量插入,Mybatis是否支持useGeneratedKeys选项?经过一番研究,发现Mybatis是支持的,下面是Mybatis获取需要注入插入id的参数的代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
private Collection<Object> getParameters(Object parameter) {
Collection<Object> parameters = null;
if (parameter instanceof Collection) {
parameters = (Collection) parameter;
} else if (parameter instanceof Map) {
Map parameterMap = (Map) parameter;
if (parameterMap.containsKey("collection")) {
parameters = (Collection) parameterMap.get("collection");
} else if (parameterMap.containsKey("list")) {
parameters = (List) parameterMap.get("list");
} else if (parameterMap.containsKey("array")) {
parameters = Arrays.asList((Object[]) parameterMap.get("array"));
}
}
if (parameters == null) {
parameters = new ArrayList<Object>();
parameters.add(parameter);
}
return parameters;
}

可见,只要我们Mapper方法的参数是集合,或者拥有参数名为”collection”、”list”、”array”的集合参数,就可以支持批量插入的自增ID注入了!

在了解了这个事实以后,我就很happy的去做B表的批量插入了~~下面是mybatis插入的语句:

1
2
3
4
5
6
<insert id="addBs" parameterType="List" useGeneratedKeys="true" keyProperty="bId">
<![CDATA[ insert into B (BId, ...) values ]]>
<foreach collection="list" item="b" separator=",">
<![CDATA[ (seq, ...) ]]>
</foreach>
</insert>

写个简单的测试代码测试下,OK的,可以拿到批量插入记录的id,这样就可以批量的插入B记录,然后拿到BId再去批量的插入C记录了,大大的提升了数据迁移的效率!

问题的出现与研究

于是我就在测试环境用上面的代码做了数据迁移,一直没有发现有什么问题。。直到在项目上线前,我在线上数据库做了同样的数据迁移,几个小时后,出问题了。。发现好多B记录应该有对应的C记录结果却查询不到,而C记录的总数量是正确的,也就是说,上面的方法返回的id是有问题的!

现在来研究一下批量插入返回id的底层实现,其实Mybatis并没有做什么事情,这些返回的id是直接在jdbc驱动里面去拿的,于是去看了mysql-connector-java的相关代码,才终于知道为什么!

原来对于插入数据需要获取自增id的情况,实际上是在插入成功后用select LAST_INSERT_ID()来查询出来的。来看一下mysql官方对这个函数在批量插入后返回数据的解释

Important
If you insert multiple rows using a single INSERT statement, LAST_
INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

也就是说,在批量插入的情况下,LAST_INSERT_ID()返回的是成功插入的第一条数据的id,而mysql的AUTO_INCREMENT是全局连续自增的,所以后面插入的数据id是可以直接在第一条数据id基础上+1计算出来的,这也就是mysql-connector-java对于批量插入返回自增id的实现。那么问题来了,我们所使用的公司的DDB(分布式数据库),自增Id不是全局连续自增的!于是根据LAST_INSERT_ID()来计算其他插入数据的id是不靠谱的,是会出错的!

那么为什么之前测试环境没有发现这个问题呢?其实测试环境的数据也是有问题的,只是比较少没有发现而已。因为我每次批量插入实际上是对于一批均衡字段确定的数据进行的,所以这些插入最终肯定会插入到DDB的同一台节点上去,而同一台节点上如果批量插入的数据比较少,还是有很大可能的到连续自增id的。测试环境的数据量不大,每次批量插入的数据不多,所以出问题的数据比较少,没有被发现。

至于解决方案,当然是换成回一条一条插入记录的方法,然后修复数据就OK了。

总结

现在想想,之前做事情不求甚解,对于一个不常用而且没用过的功能,没有去看一下底层的代码实现,没有去想为什么可以支持这样的操作,就草草的去用了。后面不出问题的话也就是运气好,但是出了问题就麻烦了。

当然如果我一开始就不考虑批量插入,而是老老实实一个一个的去做插入,也就不会出现这个问题了。但我们也不能因为怕出现问题就不去尝试更好的解决方案,只不过在尝试没用过的解决方案之前,要先做深入一些的研究工作,只有了解其原理才能保证不会出问题。