Loading [MathJax]/jax/output/CommonHTML/config.js
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >深入分析Mybatis 使用useGeneratedKeys获取自增主键

深入分析Mybatis 使用useGeneratedKeys获取自增主键

作者头像
方丈的寺院
发布于 2019-09-10 03:07:17
发布于 2019-09-10 03:07:17
2.5K00
代码可运行
举报
文章被收录于专栏:方丈的寺院方丈的寺院
运行总次数:0
代码可运行

摘要

我们经常使用useGenerateKeys来返回自增主键,避免多一次查询。也会经常使用on duplicate key update,来进行insertOrUpdate,来避免先query 在insert/update。用起来很爽,但是经常踩坑,还不知为何。本篇就是深入分析获取自增主键的原理。

问题

首先摘两段我司一些老代码的bug

批量插入用户收藏

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
for (tries = 0; tries < MAX_RETRY; tries++) {
    final int result = collectionMapper.insertCollections(collections);
    if (result == collections.size()) {
        break;
    }
}
if (tries == MAX_RETRY) {
    throw new RuntimeSqlException("Insert collections error");
}
// 依赖数据库生成的collectionid
return collections;

collectionMapper.insertCollections 方法

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
<insert id="insertCollections" parameterType="list" useGeneratedKeys="true"
        keyProperty="collectionId">
    INSERT INTO collection(
    userid, item
    )
    VALUES
    <foreach collection="list" item="collection" separator=",">
        (#{collection.userId}, #{collection.item})
    </foreach>
    ON DUPLICATE KEY UPDATE
    status = 0
</insert>

不知道大家能不能发现其中的问题

分析

问题有两个

返回值result的判断错误

使用 on duplicate key 批量update返回影响的行数是和插入的数不一样的。犯这种错主要在于想当然,不看文档 看下官网文档写的很清楚

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENTFOUNDROWS flag to the mysqlrealconnect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

返回值有三种 0: 没有更新 1 :insert 2. update 还有一个特殊情况,update 一个相同值到原来的值,这个根据客户端配置,可能为0,可能为1。

所以这个判断明显错误

利用批量InsertOrUpdate的userGeneratedKey来返回自增主键

这个问题批量插入时有update语句时,就会发现有问题。返回的自增主键都是错的,这是为什么呢?

1. 首先我们看下mybatis对于useGeneratedKey的描述

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
>This tells MyBatis to use the JDBC getGeneratedKeys method to retrieve keys generated internally by the database (e.g. auto increment fields in RDBMS like MySQL or SQL Server). Default: false.

就是使用JDBC的getGeneratedKeys的方法来获取的。

2. 我们再找下JDBC的规范

Before version 3.0 of the JDBC API, there was no standard way of retrieving key values from databases that supported auto increment or identity columns. With older JDBC drivers for MySQL, you could always use a MySQL-specific method on the Statement interface, or issue the query SELECT LASTINSERTID() after issuing an INSERT to a table that had an AUTOINCREMENT key. Using the MySQL-specific method call isn't portable, and issuing a SELECT to get the AUTOINCREMENT key's value requires another round-trip to the database, which isn't as efficient as possible. The following code snippets demonstrate the three different ways to retrieve AUTOINCREMENT values. First, we demonstrate the use of the new JDBC 3.0 method getGeneratedKeys() which is now the preferred method to use if you need to retrieve AUTOINCREMENT keys and have access to JDBC 3.0. The second example shows how you can retrieve the same value using a standard SELECT LASTINSERTID() query. The final example shows how updatable result sets can retrieve the AUTO_INCREMENT value when using the insertRow() method.

意思就是JDBC3.0以前,有些乱七八糟的定义的,没有统一,之后统一成了getGeneratedKeys()方法。两边是一致的。实现的原理主要就是数据库端返回一个 LAST_INSERT_ID。这个跟 auto_increment_id强相关。

我们看下autoincrementid的定义。重点关注批量插入

For a multiple-row insert, LASTINSERTID() and mysqlinsertid() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

批量插入的时候只会返回一个id,这个id值是第一个插入行的AUTO_INCREMENT值。至于为什么这么干,能够使得mysql-server在master-slave架构下也能保证id值统一的原因可以看下这篇。本篇文章就不展开了。

那么mysql server只返回一个id,客户端批量插入的时候怎么能实现获取全部的id呢

3. 客户端的实现

我们看下客户端 getGeneratedKeys的实现。

JDBC com.mysql.jdbc.StatementImpl

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
public synchronized ResultSet getGeneratedKeys() throws SQLException {
       if (!this.retrieveGeneratedKeys) {
           throw SQLError.createSQLException(Messages.getString("Statement.GeneratedKeysNotRequested"), "S1009", this.getExceptionInterceptor());
       } else if (this.batchedGeneratedKeys == null) {
           // 批量走这边的逻辑
           return this.lastQueryIsOnDupKeyUpdate ? this.getGeneratedKeysInternal(1) : this.getGeneratedKeysInternal();
       } else {
           Field[] fields = new Field[]{new Field("", "GENERATED_KEY", -5, 17)};
           fields[0].setConnection(this.connection);
           return ResultSetImpl.getInstance(this.currentCatalog, fields, new RowDataStatic(this.batchedGeneratedKeys), this.connection, this, false);
       }
   }

看下调用的方法 this.getGeneratedKeysInternal()

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
protected ResultSet getGeneratedKeysInternal() throws SQLException {
        // 获取影响的行数
        int numKeys = this.getUpdateCount();
        return this.getGeneratedKeysInternal(numKeys);
    }

这里有个重要知识点了,首先获取本次批量插入的影响行数,然后再执行具体的获取id操作。

getGeneratedKeysInternal方法

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
protected synchronized ResultSet getGeneratedKeysInternal(int numKeys) throws SQLException {
       Field[] fields = new Field[]{new Field("", "GENERATED_KEY", -5, 17)};
       fields[0].setConnection(this.connection);
       fields[0].setUseOldNameMetadata(true);
       ArrayList rowSet = new ArrayList();
       long beginAt = this.getLastInsertID();
        // 按照受影响的范围+递增步长
        for(int i = 0; i < numKeys; ++i) {
              if (beginAt > 0L) {
                       // 值塞进去
                       row[0] = StringUtils.getBytes(Long.toString(beginAt));
                   }
            beginAt += (long)this.connection.getAutoIncrementIncrement();
        }
}

迭代影响的行数,然后依次获取id。

所以批量insert是正确可以返回的。但是批量insertOrUpdate就有问题了,批量insertOrUpdate的影响行数不是插入的数据行数,可能是0,1,2这样就导致了自增id有问题了。

比如插入3条数据,2条会update,1条会insert,这时候updateCount就是5,generateid就会5个了,mybatis然后取前3个塞到数据里,显然是错的。

以上是原理分析,如果想了解更详细的实验结果,可以看下实验

总结

批量insert

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
<insert id="insertAuthor" useGeneratedKeys="true"
    keyProperty="id">
  insert into Author (username, password, email, bio) values
  <foreach item="item" collection="list" separator=",">
    (#{item.username}, #{item.password}, #{item.email}, #{item.bio})
  </foreach>
</insert>

来自官网的例子,mapper中不能指定@Param参数,否则会有问题

批量insertOrUpdate

不能依赖useGeneratedKey返回主键。

参考

https://blog.csdn.net/slvher/article/details/42298355

https://blog.csdn.net/qq_27680317/article/details/81118070#%EF%BC%883%EF%BC%89%E6%9C%80%E4%BD%B3%E5%AE%9E%E8%B7%B5

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-last-insert-id.html

https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html

https://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-09-07,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 方丈的寺院 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
MySQL 的自增主键一定是连续
结合实例分析了自增值保存在哪里,自增值的修改策略,以及自增值不连续的四个场景,希望对各位小伙伴们有所帮助~
飞天小牛肉
2023/01/11
4.1K0
自增主键,很多人以为自己懂了,然而...
drop table t1; create table t1( id int not null auto_increment, name varchar(10) unique, count int default 0, primary key(id), index(name) )engine=innodb; insert into t1(name) values("zhangsan"),("lisi"),("wangwu"); select * from t1;
架构师之路
2020/03/23
3240
好险!一入职,就遇到MySQL这么大Bug!差点背锅走人~
今年,这种情况,有时候不找好下家还真不敢跳,这不,前段时间刚跳到新东家,刚办入职那天,就遇上事了,真的是吓出一身冷汗(老大一直盯着我,说要快速解决这个问题),差点被(背)开(锅)了....
用户6543014
2020/11/09
6810
好险!一入职,就遇到MySQL这么大Bug!差点背锅走人~
自增主键,三类插入测验答案,在这里。
《三类插入与自增键的关系》一文,基本解答了《自增键四道测验题》,仍有水友要求贴答案,原理都解释了,copy语句执行下,真的难么? 画外音:你们赢了,我还是贴一下执行结果。
架构师之路
2020/03/23
4510
自增主键,三类插入测验答案,在这里。
MySQL自增主键详解「建议收藏」
2.InnoDB引擎的自增值,在MySQL5.7及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值max(id),然后将max(id)+步长作为这个表当前的自增值
全栈程序员站长
2022/09/06
6.3K0
MySQL自增主键详解「建议收藏」
MySQL 主键自增注意事项
很多小伙伴应该知道,在 MySQL 中主键不应该使用随机字符串。但是主键不用随机字符串用什么?主键自增?主键自增就是最佳方案吗?有没有其他坑?今天我们就来讨论下这个话题。
江南一点雨
2024/06/07
5860
MySQL 主键自增注意事项
MyBatis魔法堂:Insert操作详解(返回主键、批量插入)
一、前言                                      数据库操作怎能少了INSERT操作呢?下面记录MyBatis关于INSERT操作的笔记,以便日后查阅。 二、 insert元素 属性详解                               其属性如下: parameterType ,入参的全限定类名或类型别名 keyColumn ,设置数据表自动生成的主键名。对特定数据库(如PostgreSQL),若自动生成的主键不是第一个字段则必须设置 keyPropert
^_^肥仔John
2018/01/18
10K0
MySQL重大Bug!自增主键竟然不是连续递增
AUTO_INCREMENT=2,表示下一次插入数据时,若需要自动生成自增值,会生成id=2。
JavaEdge
2021/10/18
2.8K0
【MyBatis-4】MyBatis批量insert、update、delete数据
在日常开发中,批量操作数据库数据是常见场景,比如批量插入、批量修改、批量删除数据。MyBatis为批量操作数据提供了非常便利的方案。
云深i不知处
2020/09/16
3.1K0
插入时,究竟发生了什么?(非开车,纯技术交流)
《MySQL自增ID,居然大部分人都搞错了?》中的作业题,有少量答对的人,但原理讲得不透,今天简单说下作业题中的答案,以及相关知识点。 作业题是这样的: drop table t1; create table t1(     id int not null auto_increment,     name varchar(10) unique,     count int default 0,     primary key(id),     index(name) )engine=innodb; ins
架构师之路
2022/06/06
4760
插入时,究竟发生了什么?(非开车,纯技术交流)
不懂就问:MySQL 自增主键一定是连续的吗?
如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不能保证连续递增。
SQL数据库开发
2024/04/24
2230
不懂就问:MySQL 自增主键一定是连续的吗?
mybatis 实现插入或更新数据功能,数据存在时只更新
在mysql中,提供有on duplicate key update 指令,该指令表示如果唯一索引(UNIQUE)或主键(PRIMARY KEY)出现重复值时,则执行更新操作;如果不存在唯一冲突,则执行插入操作。
鳄鱼儿
2024/05/21
8880
深度解析auto-increment自增列&quot;Duliplicate key&quot;问题
提示:公众号展示代码会自动折行,建议横屏阅读 问题描述 近期,线上有个重要Mysql客户的表在从5.6升级到5.7后master上插入过程中出现"Duplicate key"的错误,而且是在主备及RO实例上都出现。以其中一个表为例,迁移前通过“show create table” 命令查看的auto increment id为1758609, 迁移后变成了1758598,实际对迁移生成的新表的自增列用max求最大值为1758609。用户采用的是Innodb引擎,而且据运维同学介绍,之前碰到过类似问题,重启
腾讯数据库技术
2018/12/03
2.3K0
深度解析auto-increment自增列&quot;Duliplicate key&quot;问题
MySQL实战第三十九讲-自增主键为什么不是连续的?
在 第4篇 文章中,我们提到过自增主键,由于自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,因此索引更紧凑。
越陌度阡
2022/11/27
1K0
MySQL实战第三十九讲-自增主键为什么不是连续的?
MySQL自增主键为什么不连续
可以看到表定义中出现了AUTO_INCREMENT=2,表示下一次插入数据时如果需要自动生成自增值,那么id便是2。
shysh95
2022/04/07
8.7K0
MySQL自增主键为什么不连续
mybatis插入时获取自增主键
一、自增主键优缺点 1.优点 查询和插入的性能较高(增量增长,按序存放,具体可查看InnoDB相关资料了解B+树) 插入新记录时不用担心主键会重复 2.缺点 分布式系统中不太适用 二、回到正文 1.核心jar包 <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.
河岸飞流
2021/01/06
1.1K0
MyBatis批量插入获取自增ID
MyBatis3.3.1或者MyBatis3.4.X(自测使用3.4.6) ModuleMapper.xml
十毛
2019/03/27
4.6K0
如何插入? | 非开车,纯技术交流
《InnoDB自增键基础知识测试》中的四道测试题,全答对的朋友少之又少,为了讲清楚InnoDB自增键,今天先系统性讲讲,什么是插入,如何插入。
架构师之路
2020/03/23
5740
Mybatis中实现批量更新的几种姿势,总有一款适合你
mybatis中实现批量插入是很简单的,相比大家都知道,这里就不赘述,本文主要讲述如何实现批量更新。
品茗IT
2020/05/28
32K0
39 | 自增主键不连续
然后插入数据,最后看到,表会自动生成一个AUTO_INCREMENT的值,ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 ,表示下一次插入数据时,如果需要自动生成自增值,会生成 id=11。
HaC
2020/12/30
5.1K0
39 | 自增主键不连续
推荐阅读
相关推荐
MySQL 的自增主键一定是连续
更多 >
交个朋友
加入腾讯云官网粉丝站
蹲全网底价单品 享第一手活动信息
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验