避坑必看：很详尽的MyBatis返回自增主键实验（包括插入或更新SQL语句insert on duplicate key update的自增主键返回情况）

最新推荐文章于 2025-03-15 16:56:37 发布

花郎徒结

最新推荐文章于 2025-03-15 16:56:37 发布

阅读量3.2w

点赞数 42

分类专栏： MySQL MyBatis 文章标签： MySQL MyBatis Java 主键插入或更新

本文链接：https://blog.youkuaiyun.com/qq_27680317/article/details/81118070

版权

1. 实验对比维度

（1）单纯的insert和insert on duplicate key update

（2）selectKey和useGeneratedKeys

（3）@Param和parameterType

（4）单个和批量

（5）keyProperty写法

2. 基本概念介绍

（1）插入或更新SQL（简称InsertOrUpdate）

（2）selectKey和useGeneratedKeys的异同

（3）@Param和parameterType的异同

（4） Mybatis全局开启useGeneratedKeys=true的坑

本篇文章对MyBatis操作MySQL时自增主键返回情况进行详细的实验，给出不同情况下Mybatis返回自增主键的不同行为，仅基于实验结果，不做源码分析。

1. 实验对比维度

（1）单纯的insert和insert on duplicate key update

这也是本文最大的特点，查询网上各种阐述Mybatis返回主键的文章，基本只关注insert时Mybatis返回主键的情况，对于插入或更新的sql语句insert on duplicate key update时mybatis返回主键（此时还细分为仅insert，仅update和insert和update混合三种情况）的文章则比较少。

（2）selectKey和useGeneratedKeys

Mybatis用这两个方法都能返回主键，前者一般用于单个插入，后者一般用于批量插入。本文还会给出两者更加细致的实验区别。

（3）@Param和parameterType

Mybatis传参时可以用@Param注解或者在xml中用parmaterType引用java对象，本文会实验两者传参对返回主键的影响。

（4）单个和批量

单个插入、批量插入、单个插入或更新和批量插入或更新时Mybatis返回主键情况各不相同，本文针对此进行实验分析。

（5）keyProperty写法

这个维度和@Param、parameterType紧密结合。不管采用selectKey还是useGeneratedKeys，返回主键时都需要用keyProperty指定主键对应的Java对象属性名，以便将主键设置到Java对象上（达到返回主键的目的）。而@Param和parameterType影响入参名字，也就会影响keyProperty对应的属性名写法，进一步会影响到返回主键的行为。

2. 基本概念介绍

对本文感兴趣的人一般已经熟悉MySQL和Mybatis，在工程中也有大量应用，但是为了帮助读者更顺畅的阅读，笔者还是介绍下本文涉及的相关基本概念。

（1）插入或更新SQL（简称InsertOrUpdate）

当我们往数据库插入记录时，如果数据库原先不存在该记录，那么就正常插入（此时就是insert）；如果数据库原先存在该记录，那么就更新此记录（此时就是update），用一条SQL语句完成上述要求就是所谓的InsertOrUpdate。

MySQL判断记录是否存在的依据是主键或者唯一索引，insert在主键或者唯一索引已经存在的情况下会插入失败，而InsertOrUpdate在主键或者唯一索引已经存在的情况下就变成了根据主键或唯一索引update的操作。

有人会问原先有多个记录同时与插入的记录相同，会发生什么？答案是MySQL会更新第一个匹配的记录，其余的则不更新（网上看到的，笔者没有实验过），本质上这是由于单表中有多个唯一索引并存，所以有人会推荐一张表最好只建一个唯一索引。

MySQL实现InsertOrUpdate的语句有两种：一种就是本文要实验的Insert into values on duplicate key update语句。另一种是replace into values语句。insert on duplicate key update在发现记录已经存在时就地更新，或者说和update行为一致。replace into在发现记录已经存在时，先把原先的记录删除，然后再插入新的记录，相当于delete+insert操作。两者具体的细节可以参考下其他文章。

这里说明下为什么选取insert on duplicate key on而非replace into做实验：目前后端开发的数据表设计，比较流行用自增主键而不是自己选定字段做主键（这样做有诸多好处，可以参考MySQL的底层数据结构），并且也不依赖数据库提供的外键功能，而是在程序逻辑中保证数据一致性关系。这样一来replace into的功能就非常坑，它在记录已经存在时会改变主键，带来数据不一致的风险。实战中笔者建议除非特殊需求否则不要用replace into，即便当下没问题，将来扩展起来也容易埋坑。即便insert on duplicate key能真正做到Insert和Update一体，笔者基本也只用它来做批量更新用，不会使用其不存在插入、存在则更新的特性——宁愿在程序逻辑里先查一遍，区分需要insert和需要update的记录，该insert的insert，该update的update——因为MySQL的语言和Java等命令型语言不一样，它是有解释器的，开发者输入的SQL语句具体怎么执行不是由开发者决定的而是由MySQL决定的，所以功能越多的、语句越复杂的SQL执行的结果越不可控，也很难调试和维护。比如InsertOrUpdate在遇到多个相同的记录时只会更新第一个相同的记录就是不可控性的表现，非常坑，但是如果在应用程序中处理这些逻辑就很方便、很确定。

无论是insert on duplicate key update还是replace into，在插入时MySQL返回的影响行数就是插入的记录数，但是在更新时返回的影响行数时更新行数*2。所以笔者只会在批量更新时放心的使用insert on duplicate key update，这样根据返回值是否是参数大小的2倍就能判断是否只进行了更新操作。

笔者推崇开发中把数据库当作单纯的数据存储服务，减少其业务逻辑的负担，返璞归真。数据库语句应该尽量单表、单crud操作，不要动则几十上百行SQL，这样也符合当前微服务的趋势。笔者刚入行时经常听闻某某大神把几分钟的SQL优化到几秒，非常佩服，后来才发现本质上这是个比烂大赛，开发人员偷懒用重SQL语句弥补开发效率，一个联表查询就能从地球联到火星，留下了一大堆低效、无扩展性、难阅读、无从调试的代码，只能借助于SQL大神擦屁股，这种屁股擦起来其实对人也没啥长进（求职时看到职责描述有“能书写复杂SQL或者优化复杂SQL”的，亲们最好留个心眼...）。还是要做好系统架构、引导好团队的开发习惯、合理设计表和索引、细致负责的规划梳理业务，避免出现SQL大神才好。

题外话说的太多了，既然本文是Mybatis返回主键的实验，replace into这种会改变主键而且实战中也不建议使用的语句当然不予考虑。

（2）selectKey和useGeneratedKeys的异同

关于两者介绍的文章也很多。概括来说selectKey用于单个记录返回主键，useGeneratedKeys单记录多记录都能返回主键。所以单记录插入时实验会对比selectKey和useGeneratedKeys，而批量插入时只会采用useGeneratedKeys。

另外useGeneratedKeys=true需要数据库的支持，mysql可以使用，但是oracle不支持。selectKey实用的last_insert_id()的适应范围更广。

这里根据后面的实验先提前下个结论：在数据库兼容的情况下，返回主键的方式用useGeneratedKeys是最佳实践，selectKey在某些情况下（单记录）不会返回主键。

（3）@Param和parameterType的异同

基本异同也请参考其他文章。简单来说有个数据库映射实体Boy，其属性id映射数据库自增主键。如果dao中入参用@Param("entity")标注，那么在Mybatis的映射xml文件里，引用boy.id要写成#{entity.id}。如果在映射xml文件用parameterType="全路径名.Boy"，那么引用boy.id只用写成#{id}。

@Param在替换dao的入参实体时可能免去修改映射文件工作，而且多参数情况下只能用@Param。parameterType会让映射文件中引用对象属性写法变得简单，但是只能适应单参数情况。根据后面的实验结果，这里提前给结论：在需要返回主键场景（插入、批量插入、插入或更新，批量插入或更新）里，parameterType比@Param适应性更好，@param在某些场景下不会返回主键。

3. 实验工程

具体工程可以参考项目的github：https://github.com/FlowerL/jxshen-mybatis-test

（1）实验环境

只要mysql和mybatis的版本不至于太低都能复现。具体参数如下：

OS：mac

MySQL：5.7.20

SpringBoot：1.5.9

MyBatis：3.4.6

（2）数据表

一张boy表，自增主键id，name(varchar)唯一索引，created_time和modified_time记录创建和修改时间。

CREATE TABLE `boy` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `created_time` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `modified_time` timestamp NULL DEFAULT NULL COMMENT '记录修改时间',
  PRIMARY KEY (`id`),
  UNIQUE `unq_name` USING BTREE (`name`) comment ''
)

（3）数据库实体

就一个实体 BoyEntity

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class BoyEntity {

     private Long id;
     private String name;
     private Date createdTime;
     private Date modifiedTime;
}

（4）Dao和MyBatis映射文件

public interface BoyDao {

    int insertSelectKeyParam(@Param("entity") BoyEntity entity);

    int insertUseGeneratedKeysParam(@Param("entity") BoyEntity entity);

    int insertSelectKey(BoyEntity entity);

    int insertUseGeneratedKeys(BoyEntity entity);

    int insertBatchParam(@Param("entityList") List<BoyEntity> entityList);

    int insertBatch(List<BoyEntity> entityList);

    int insertOrUpdateSelectKeyParam(@Param("entity") BoyEntity entity);

    int insertOrUpdateUseGeneratedKeysParam(@Param("entity") BoyEntity entity);

    int insertOrUpdateSelectKey(BoyEntity entity);

    int insertOrUpdateUseGeneratedKeys(BoyEntity entity);

    int insertOrUpdateBatchParam(@Param("entityList") List<BoyEntity> entityList);

    int insertOrUpdateBatch(List<BoyEntity> entityList);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.jxshen.mybatis.test.dao.BoyDao">
    <resultMap id="BaseResultMap" type="com.jxshen.mybatis.test.entity.BoyEntity">
    </resultMap>

    <sql id="foreachSql">
        <foreach collection="itemList" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </sql>

    <sql id="insertForeachEntitySql">
        <foreach collection="entityList" item="entity" index= "index" separator =",">
            <include refid="insertEntitySql" />
        </foreach>
    </sql>

    <sql id="insertForeachSql">
        <foreach collection="list" item="entity" index= "index" separator =",">
            <include refid="insertEntitySql" />
        </foreach>
    </sql>

    <sql id="insertTableSql">
        (
            `name`,
            created_time,
            modified_time
        )
    </sql>

    <sql id="insertEntitySql">
        (
            #{entity.name},
            now(),
            now()
        )
    </sql>

    <sql id="insertSql">
        (
            #{name},
            now(),
            now()
        )
    </sql>

    <sql id="onDuplicateKeyUpdate">
        modified_time = now()
    </sql>

    <insert id="insertSelectKeyParam">
        <selectKey resultType="java.lang.Long" keyProperty="entity.id" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into boy <include refid="insertTableSql"/>
        values <include refid="insertEntitySql"/>
    </insert>

    <insert id="insertUseGeneratedKeysParam" useGeneratedKeys="true" keyProperty="entity.id">
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertEntitySql" />
    </insert>

    <insert id="insertSelectKey" parameterType="com.jxshen.mybatis.test.entity.BoyEntity">
        <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into boy <include refid="insertTableSql"/>
        values <include refid="insertSql"/>
    </insert>

    <insert id="insertUseGeneratedKeys" parameterType="com.jxshen.mybatis.test.entity.BoyEntity" useGeneratedKeys="true" keyProperty="id">
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertSql" />
    </insert>

    <insert id="insertBatchParam" useGeneratedKeys="true" keyProperty="entity.id">
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertForeachEntitySql" />
    </insert>

    <insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertForeachSql" />
    </insert>

    <insert id="insertOrUpdateSelectKeyParam">
        <selectKey resultType="java.lang.Long" keyProperty="entity.id" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertEntitySql" />
        on duplicate key update <include refid="onDuplicateKeyUpdate" />
    </insert>

    <insert id="insertOrUpdateUseGeneratedKeysParam" useGeneratedKeys="true" keyProperty="id">
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertEntitySql" />
        on duplicate key update <include refid="onDuplicateKeyUpdate" />
    </insert>

    <insert id="insertOrUpdateSelectKey" parameterType="com.jxshen.mybatis.test.entity.BoyEntity">
        <selectKey resultType="java.lang.Long" keyProperty="id" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertSql" />
        on duplicate key update <include refid="onDuplicateKeyUpdate" />
    </insert>

    <insert id="insertOrUpdateUseGeneratedKeys" parameterType="com.jxshen.mybatis.test.entity.BoyEntity" useGeneratedKeys="true" keyProperty="id">
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertSql" />
        on duplicate key update <include refid="onDuplicateKeyUpdate" />
    </insert>

    <insert id="insertOrUpdateBatchParam" useGeneratedKeys="true" keyProperty="id">
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertForeachEntitySql" />
        on duplicate key update <include refid="onDuplicateKeyUpdate" />
    </insert>

    <insert id="insertOrUpdateBatch" useGeneratedKeys="true" keyProperty="id">
        insert into boy <include refid="insertTableSql" />
        values <include refid="insertForeachSql" />
        on duplicate key update <include refid="onDuplicateKeyUpdate" />
    </insert>

</mapper>

12个SQL语句用来组合条件下返回主键的结果。函数的命名表明了不同条件，命名规则从左到右为：

insert / insertOrUpdate：表示语句是插入还是插入或更新

batch：带有batch的表示是批量操作，没有batch是单个操作

selectKey / useGeneratedKeys：表示返回主键用的何种方式

param：带有param表示是入参用@Param标注，否则在映射文件中用parameterType

keyProperty：在入参用@Param标注时keyProperty有id和entity.id两种写法，测试在@Param标注传参时不同keyProperty的写法能否成功返回主键。但是keyProperty的不同取值在dao函数中未做区分，直接写定在Mybatis映射文件中。实验时需要自己手动修改keyProperty来复现实验结果。

基本上看函数名就能明白组合的条件，另外再补充几点说明：

（4-1）@Param统一标注入参实体为entity（单个）或者entityList（批量）。

（4-2）为了复用，映射文件里用了很多<sql>，看映射语句时注意<sql>具体对应的片段，有些名字很相近的容易混淆。

（4-3）批量插入时如果用@Param标注入参，那么批量插入的foreach语句里collection引用的就是entityList，item为entity。如果批量插入用的parameterType入参，那么批量插入时foreach的语句里collection就是list对象，但是item仍取别名为entity。

（5）请求参数实体

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class BoyInsertParam {

    private BoyEntity boy;
    private List<BoyEntity> boys;
    private String method;
}

method：代表需要调用的dao方法

boy：如果dao方法入参是单个实体，则赋值给boy字段。boy.id必须为空，让数据库自增生成。

boys：如果 dao方法入参是批量实体，，则赋值给boys字段。每个boy.id必须为空，让数据库自增生成

（6）响应实体

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class BoyInsertRO {

    private BoyEntity boy;
    private List<BoyEntity> boys;
    private Integer num;
}

boy：如果是单个插入（插入或更新），dao操作后的BoyEntity赋值给boy字段，从boy.id能看出是否成功返回主键，以及返回主键是否是对应记录的。

boys：如果是批量插入（批量插入或更新），dao操作后List<BoyEntity>赋值给boys字段，从每个boy.id能看出是否成功返回主键，以及返回主键是否是对应记录的。

num：dao操作影响的行数。如果是插入，就是成功插入的数量，如果是更新，就是成功更新数量的两倍。如果是插入或者更新混合，则取决于插入和更新的记录数分别有多少。

（7）接口

@RestController
public class BoyController {

    @Autowired
    private BoyDao boyDao;

    @RequestMapping("/boy/insert")
    public BoyInsertRO insertOrUpdateBoy(@RequestBody BoyInsertParam param) throws InvocationTargetException, IllegalAccessException {
        int num = 0;

        for (Method method : boyDao.getClass().getDeclaredMethods()) {
            if (method.getName().equals(param.getMethod())) {
                method.setAccessible(true);
                if (method.getParameterTypes()[0].equals(BoyEntity.class)) {
                    num = (int) method.invoke(boyDao, param.getBoy());
                }
                else {
                    num = (int) method.invoke(boyDao, param.getBoys());
                }
            }
        }

        return BoyInsertRO.builder()
                .boy(param.getBoy())
                .boys(param.getBoys())
                .num(num)
                .build();
    }
}

采用json格式交互，接口中用反射找到具体要操作的dao函数。

4. 实验结果

依次调用12个dao函数，在必要时变更keyProperty的取值，一共进行30组实验。在最后汇总结果的表格里，首行用英文缩写表示不同的条件组合，现在说明如下：

sql：insert / insertOrUpdate。表示语句是insert into values还是insert into values on duplicate key update

actual aciton：insert / update / insertAndUpdate。对于insert sql的话只有insert一种情况；对于insertOrUpdate sql，可能数据是全部插入（insert）、全部更新（update）、部分插入部分更新（insertAndUpdate）。

key method：selectKey / useGeneratedKeys。Mybatis返回主键的方法。

entry param：param annotation / parameterType。入参形式。@Param标注入参（param annotation）或者parameterType指定参数类型（parameterType）。

isBatch：single / batch。是否批量操作。

keyProperty：id / entity.id。keyProperty的取值方式。在entry param为parameterType时只能取值id；在entry param为param annotation时可以为id或者entity.id。

return key：yes / no。返回的BoyEntity的id字段上是否有主键值。

method name：对应的dao函数名字。

remark：额外说明。比如return key是否就是BoyEntity对应的数据库记录主键。

原始的boy表为空，没有任何记录，自增主键从1开始。

下面展示每组实验结果（尤其是最后3组实验的结果非常有意思）：

（1）

条件：

sql	actual action	key method	entry param	isBatch	keyProperty	return key	method name
insert	insert	selectKey	param annotation	single	entity.id	yes	insertSelectKeyParam

用PostMan进行输入和输出：

数据库：

结论：

MyBatis进行单个insert操作，采用selectKey返回主键的方式，用@Param标注入参，keyProperty="@Param的入参名.主键属性名”，能够成功返回正确的自增主键。

（2）

条件：

sql	actual action	key method	entry param	isBatch	keyProperty	return key	method name	remark
insert	insert	selectKey	param annotation	single	id	no	insertSelectKeyParam

输入输出：

数据库：

结论：

MyBatis进行单个insert操作，采用selectKey返回主键的方式，用@Param标注入参，keyProperty="主键属性名”，不能返回自增主键。

（3）

条件：

sql	actual action	key method	entry param	isBatch	keyProperty	return key	method name	remark
insert	insert	useGeneratedKeys	param annotation	single	entity.id	yes	insertUseGeneratedKeysParam