程序员经验分享-hwhale

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

2023-11-11

目录

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的异同

3. 实验工程

（1）实验环境

（2）数据表

（3）数据库实体

（4）Dao和MyBatis映射文件

（5）请求参数实体

（6）响应实体

（7）接口

4. 实验结果

5. 总结

（1）实验结果汇总

（2）概括性结论

（3）最佳实践

（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  

输入输出：

数据库：

结论：

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

 

（4）

条件：

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

输入输出：

数据库：

结论：

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

 

（5）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insert insert selectKey parameterType single id yes insertSelectKey  

输入输出：

数据库：

结论：

MyBatis进行单个insert操作，采用selectKey返回主键的方式，用parameterType引用入参，keyProperty="主键属性名”，成功返回自增主键。

 

（6）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insert insert useGeneratedKeys parameterType single id yes insertUseGeneratedKeys  

输入输出：

数据库：

结论：

MyBatis进行单个insert操作，采用useGeneratedKeys返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，成功返回自增主键。

 

（7）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insert insert useGeneratedKeys param annotation batch entity.id no insertBatchParam 批量插入用@Param注解入参是没有办法返回主键的

输入输出：

数据库：

结论：

MyBatis进行批量insert操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“@Param的入参名.主键属性名”，无法自增主键。

 

（8）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insert insert useGeneratedKeys param annotation batch id no insertBatchParam 批量插入用@Param注解入参是没有办法返回主键的

输入输出：

数据库：

结论：

MyBatis进行批量insert操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“主键属性名”，无法自增主键。结合（7）、（8）可以得出Mybatis在批量insert时，如果用@Param标注了入参，是无法返回主键的。

 

（9）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insert insert useGeneratedKeys parameterType batch id yes insertBatch  

输入输出：

数据库：

结论：

MyBatis进行批量insert操作，采用useGeneratedKeys返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，成功返回自增主键。批量插入时为了返回主键必须用parameterType方式入参。

 

（10）

条件：

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

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为insert操作，采用selectKey返回主键的方式，用@Param标注入参，keyProperty=“@Param的入参名.主键属性名”，成功返回自增主键。

 

（11）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update selectKey param annotation single entity.id yes insertOrUpdateSelectKeyParam 返回的主键值是该connection范围内最近一次自增主键值，并不是更新记录对应的主键值（因为更新操作没有自增主键），如果connection上次调用last_insert_id()返回是null，则这个dao函数返回的主键是0

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为update操作，采用selectKey返回主键的方式，用@Param标注入参，keyProperty=“@Param的入参名.主键属性名”，返回自增主键。但是返回的主键值是上一次操作（10）曾经返回的主键值19。具体原因请参考selectKey使用select last_insert_id()来返回主键的原理（文章 ：https://blog.csdn.net/slvher/article/details/42298355 有详细说明）。所以insertOrUpdate函数在单个update操作时使用selectKey是无法正常返回主键的，除非用insert on duplicate key update id = last_insert_id(id)的办法替代，通过update时强制设置更新记录的id作为last_insert_id()函数的入参，然后将select last_insert_id()返回。

 

（12）

条件：

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

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为insert操作，采用selectKey返回主键的方式，用@Param标注入参，keyProperty=“主键属性名”，无法返回自增主键。

 

（13）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update selectKey param annotation single id no insertOrUpdateSelectKeyParam  

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为update操作，采用selectKey返回主键的方式，用@Param标注入参，keyProperty=“主键属性名”，无法返回自增主键。

 

（14）

条件：

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

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为insert操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“@Param的入参名.主键属性名”，成功返回自增主键。

 

（15）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update useGeneratedKeys param annotation single entity.id yes insertOrUpdateUseGeneratedKeysParam 返回的主键值就是更新记录对应的主键值。所以单个记录insertOrUpdate时useGeneratedKeys比selectKey更好的适用性

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为update操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“@Param的入参名.主键属性名”，成功返回自增主键。所以单个InsertOrUpdate操作时，在返回主键的功能上useGeneratedKeys比selectKey有更广泛的适应。

 

（16）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insert useGeneratedKeys param annotation single id no insertOrUpdateUseGeneratedKeysParam  

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为insert操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“主键属性名”，无法返回自增主键。

 

（17）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update useGeneratedKeys param annotation single id no insertOrUpdateUseGeneratedKeysParam  

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为update操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“主键属性名”，无法返回自增主键。结合（12）、（13）、（16）、（17）得出在单个insertOrUpdate时，如果采用@Param标注入参，而keyProperty仅仅使用主键属性名，是无法返回自增主键的（或者说keyProperty无法正确的解析到入参对象）。

 

（18）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insert selectKey parameterType single id yes insertOrUpdateSelectKey  

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为insert操作，采用selectKey返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，成功返回自增主键。

 

（19）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update selectKey parameterType single id yes insertOrUpdateSelectKey 返回的主键值是该connection范围内最近一次自增主键值，并不是更新记录对应的主键值（因为更新操作没有自增主键），如果connection上次调用last_insert_id()返回是null，则这个dao函数返回的主键是0

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为update操作，采用selectKey返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，返回自增主键。但是返回的主键值是29，也就是（18）之前插入时返回的自增主键。和（11）类似，这种情况下返回的主键值是该connection范围内最近一次自增主键值，并不是更新记录对应的主键值（因为更新操作没有自增主键），如果connection上次调用last_insert_id()返回是null，则这个dao函数返回的主键是0。

 

（20）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insert useGeneratedKeys parameterType single id yes insertOrUpdateUseGeneratedKeys  

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为insert操作，采用useGeneratedKeys返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，成功返回自增主键。

 

（21）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update useGeneratedKeys parameterType single id yes insertOrUpdateUseGeneratedKeys 返回的主键值就是更新记录对应的主键值。所以单个记录insertOrUpdate时useGeneratedKeys比selectKey更好的适用性

输入输出：

数据库：

结论：

MyBatis进行单个insertOrUpdate操作，实际为update操作，采用useGeneratedKeys返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，成功返回自增主键。返回的主键值就是更新记录对应的主键值。所以单个记录insertOrUpdate时useGeneratedKeys比selectKey更好的适用性。

 

（22）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insert useGeneratedKeys param annotation batch entity.id no insertOrUpdateBatchParam  

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为insert操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“@Param的入参名.主键属性名”，无法返回自增主键。

 

（23）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update useGeneratedKeys param annotation batch entity.id no insertOrUpdateBatchParam  

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为update操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“@Param的入参名.主键属性名”，无法返回自增主键。

 

（24）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insertAndUpdate useGeneratedKeys param annotation batch entity.id no insertOrUpdateBatchParam  

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为insertAndUpdate操作（既有插入又有更新），采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“@Param的入参名.主键属性名”，无法返回自增主键。

 

（25）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insert useGeneratedKeys param annotation batch id no insertOrUpdateBatchParam  

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为insert操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“主键属性名”，无法返回自增主键。

 

（26）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update useGeneratedKeys param annotation batch id no insertOrUpdateBatchParam  

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为update操作，采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“主键属性名”，无法返回自增主键。

 

（27）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insertAndUpdate useGeneratedKeys param annotation batch id no insertOrUpdateBatchParam  

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为insertAndUpdate操作（既有插入又有更新），采用useGeneratedKeys返回主键的方式，用@Param标注入参，keyProperty=“主键属性名”，无法返回自增主键。

 

（28）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insert useGeneratedKeys parameterType batch id yes insertOrUpdateBatch 只返回第一个insert记录的主键并设置在列表第一个实体的主键属性上，
剩下的记录主键返回为null

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为insert操作，采用useGeneratedKeys返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，返回单个自增主键。注意只返回第一个insert记录的主键并设置在列表第一个实体的主键属性上， 剩下的记录主键返回为null，或者说批量插入或更新操作时，当实际操作全部插入时，返回主键失败。

 

（29）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate update useGeneratedKeys parameterType batch id yes insertOrUpdateBatch 只返回最后一个update记录的主键并设置在列表的第一个实体的主键属性上，剩下的记录主键返回为null

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为update操作，采用useGeneratedKeys返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，返回单个自增主键。注意只返回最后一个update记录的主键并设置在列表的第一个实体的主键属性上，剩下的记录主键返回为null，或者说批量插入或更新操作时，当实际操作全部更新时，返回主键失败。

 

（30）

条件：

sql actual action key method entry param isBatch keyProperty return key method name remark
insertOrUpdate insertAndUpdate useGeneratedKeys parameterType batch id yes insertOrUpdateBatch 只返回第一个insert记录的主键并设置在列表的第一个实体属性主键上，
剩下的记录主键返回null

输入输出：

数据库：

结论：

MyBatis进行批量insertOrUpdate操作，实际为insertAndUpdate操作（既有插入又有修改），采用useGeneratedKeys返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，返回单个自增主键。注意只返回第一个insert记录的主键并设置在列表的第一个实体属性主键上（结合实验截图能够看明白这句话的真实意思：返回实体列表boys的第一个实体name=boy29有主键id=69，但是69的主键其实是实体name=boy43在数据库中的主键，boy43实体是这次操作中第一个被插入到数据库的，第二个是boy44，前两个都是更新数据库）， 剩下的记录主键返回null，或者说批量插入或更新操作时，当实际操作部分插入部分更新时，返回主键失败。

 

5. 总结

（1）实验结果汇总

sql actual action key method entry param isBatch keyProperty return key method name remark
insert insert selectKey param annotation single entity.id yes insertSelectKeyParam  
insert insert selectKey param annotation single id no insertSelectKeyParam  
insert insert useGeneratedKeys param annotation single entity.id yes insertUseGeneratedKeysParam  
insert insert useGeneratedKeys param annotation single id no insertUseGeneratedKeysParam  
insert insert selectKey parameterType single id yes insertSelectKey  
insert insert useGeneratedKeys parameterType single id yes insertUseGeneratedKeys  
insert insert useGeneratedKeys param annotation batch entity.id no insertBatchParam 批量插入用@Param注解入参是没有办法返回主键的
insert insert useGeneratedKeys param annotation batch id no insertBatchParam 批量插入用@Param注解入参是没有办法返回主键的
insert insert useGeneratedKeys parameterType batch id yes insertBatch  
insertOrUpdate insert selectKey param annotation single entity.id yes insertOrUpdateSelectKeyParam  
insertOrUpdate update selectKey param annotation single entity.id yes insertOrUpdateSelectKeyParam 返回的主键值是该connection范围内最近一次自增主键值，并不是更新记录对应的主键值（因为更新操作没有自增主键），如果connection上次调用last_insert_id()返回是null，则这个dao函数返回的主键是0
insertOrUpdate insert selectKey param annotation single id no insertOrUpdateSelectKeyParam  
insertOrUpdate update selectKey param annotation single id no insertOrUpdateSelectKeyParam  
insertOrUpdate insert useGeneratedKeys param annotation single entity.id yes insertOrUpdateUseGeneratedKeysParam  
insertOrUpdate update useGeneratedKeys param annotation single entity.id yes insertOrUpdateUseGeneratedKeysParam 返回的主键值就是更新记录对应的主键值。所以单个记录insertOrUpdate时useGeneratedKeys比selectKey更好的适用性
insertOrUpdate insert useGeneratedKeys param annotation single id no insertOrUpdateUseGeneratedKeysParam  
insertOrUpdate update useGeneratedKeys param annotation single id no insertOrUpdateUseGeneratedKeysParam  
insertOrUpdate insert selectKey parameterType single id yes insertOrUpdateSelectKey  
insertOrUpdate update selectKey parameterType single id yes insertOrUpdateSelectKey 返回的主键值是该connection范围内最近一次自增主键值，并不是更新记录对应的主键值（因为更新操作没有自增主键），如果connection上次调用last_insert_id()返回是null，则这个dao函数返回的主键是0
insertOrUpdate insert useGeneratedKeys parameterType single id yes insertOrUpdateUseGeneratedKeys  
insertOrUpdate update useGeneratedKeys parameterType single id yes insertOrUpdateUseGeneratedKeys 返回的主键值就是更新记录对应的主键值。所以单个记录insertOrUpdate时useGeneratedKeys比selectKey更好的适用性
insertOrUpdate insert useGeneratedKeys param annotation batch entity.id no insertOrUpdateBatchParam  
insertOrUpdate update useGeneratedKeys param annotation batch entity.id no insertOrUpdateBatchParam  
insertOrUpdate insertAndUpdate useGeneratedKeys param annotation batch entity.id no insertOrUpdateBatchParam  
insertOrUpdate insert useGeneratedKeys param annotation batch id no insertOrUpdateBatchParam  
insertOrUpdate update useGeneratedKeys param annotation batch id no insertOrUpdateBatchParam  
insertOrUpdate insertAndUpdate useGeneratedKeys param annotation batch id no insertOrUpdateBatchParam  
insertOrUpdate insert useGeneratedKeys parameterType batch id yes insertOrUpdateBatch 只返回第一个insert记录的主键并设置在列表第一个实体的主键属性上，
剩下的记录主键返回为null
insertOrUpdate update useGeneratedKeys parameterType batch id yes insertOrUpdateBatch 只返回最后一个update记录的主键并设置在列表的第一个实体的主键属性上，剩下的记录主键返回为null
insertOrUpdate insertAndUpdate useGeneratedKeys parameterType batch id yes insertOrUpdateBatch 只返回第一个insert记录的主键并设置在列表的第一个实体属性主键上，
剩下的记录主键返回null

（2）概括性结论

（2-1）使用@Param标注入参时，在任何情况下keyProperty必须用“@Param的入参名.主键属性名”（entity.id）的形式才能正确解析到入参对象上并设置主键。

（2-2）批量insert时，采用@Param标注入参是没有办法返回主键的，所以parameterType在返回主键上比@Param更具通用性。

（2-3）insertOrUpdate时，selectKey只能正确返回插入时主键，无法正确返回更新时主键，useGenerateKeys在插入或者更新的情况下都能正确的返回主键。所以在insertOrUpdate时采用useGeneratedKey更具通用性。

（2-4）批量insertOrUpdate时，在任何情况下都无法正确返回主键，所以程序逻辑请不要依赖批量insertOrUpdate返回主键。

（3）最佳实践

最佳实践应人而异，这只是笔者根据实验得出的个人最佳实践想法。只是为了在笔者当前的工程里统一规范。基本思想是在返回自增主键这件事上，parameterType比@Param更具通用性，useGeneratedKey比selectKey更具通用，所以统一插入、批量插入、插入或更新为以下最佳工程实践（以本实验为例）：

（3-1）插入最佳实践：

int insert(BoyEntity entity);
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.jxshen.mybatis.test.entity.BoyEntity">
        insert into boy (`name`, created_time, modified_time)
        values (#{name}, now(), now())
</insert>

省略paramerType也是可以的。

（3-2）批量插入最佳实践：

int insertBatch(List<BoyEntity> entityList);
<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
        insert into boy (`name`, created_time, modified_time)
        values 
        <foreach collection="list" item="entity" index= "index" separator =",">
            (#{entity.name}, now(), now())
        </foreach>
</insert>

单个入参时没有用@Param注解，加不加parameterType=“”java.util.List"都是可以的。 

（3-3）插入或更新最佳实践：

int insertOrUpdate(BoyEntity entity);
<insert id="insertOrUpdateUseGeneratedKeys" useGeneratedKeys="true" keyProperty="id" parameterType="com.jxshen.mybatis.test.entity.BoyEntity">
        insert into boy (`name`, created_time, modified_time)
        values (#{name}, now(), now())
        on duplicate key update modified_time = now()
    </insert>

省略paramerType也是可以的。

（3-4）批量插入或更新无法返回主键，所以最佳实践就是不要返回主键，不要自动生成主键，只做批量更新。

int insertOrUpdateBatch(List<BoyEntity> entityList);
<insert id="insertOrUpdateBatch">
        insert into boy (`name`, created_time, modified_time)
        values 
        <foreach collection="list" item="entity" index= "index" separator =",">
            (#{entity.name}, now(), now())
        </foreach>
        on duplicate key update modified_time = now()
    </insert>

单个入参时没有用@Param注解，加不加parameterType=“”java.util.List"都是可以的。 

（3-5）最简化实践：上述四个语句中数据表字段和对象属性字段都可以提取成sql，方便引用，减少冗余。那么最简化后的dao和映射文件如下：

public interface BoyDao {

    int insert(BoyEntity entity);

    int insertBatch(List<BoyEntity> entityList);

    int insertOrUpdate(BoyEntity entity);

    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="insertTableSql">
        (
            `name`,
            created_time,
            modified_time
        )
    </sql>

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

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

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

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

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

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

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

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

</mapper>

上述全部用parameterType入参，而且单参数下可以不用在映射文件中显示指定parameterType。全部用userGeneratedKeys返回主键，复用sql片段。唯一冗余的地方在于insertEntitySql和insertForeachEntitySql，因为后者用于批量插入，而批量插入参数是list，在foreach中要指定每个list元素的别名（这里取entity），那么在批量插入时就要给对象属性前加上别名，这个和单个插入时直接#{属性名}不一样。如果要取消上述冗余，那么可以去掉insertEntitySql，只用insertForeachEntitySql，insert和insertOrUpdate也用insertForeachEntitySql片段，此时这两个dao函数入参就要用@Param("entity")，keyProperty=“entity.id”，导致单个操作时需要注解@Param入参，批量操作时则用parameterType，这里又产生不一致。笔者经过实践，倾向插入类型的dao函数，入参统一不用注解，映射文件insertEntitySql和insertForeachEntitySql都写全。软件开发权衡很重要，到底哪个方案更好要看具体场景。

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

有小伙伴可能会说上述（3-5）的最简化实践还能再简化，因为既然已经统一用useGeneratedKeys返回主键的机制，而Mybatis可以全局开启useGeneratedKeys=true（默认为false），如下所示：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="useGeneratedKeys" value="true" /> 
    </settings>

    <mappers>
    </mappers>
</configuration>

那么每个映射文件里需要返回主键的语句连useGeneratedKeys=true都省了，只用keyProperty指定对应主键的领域模型属性就可以了。那么这么做坑在哪里了呢？

回看我们最后3组实验，映射文件语句为：

<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>

意思为MyBatis进行批量insertOrUpdate操作，采用useGeneratedKeys返回主键的方式，用parameterType引用入参，keyProperty=“主键属性名”，插入、更新或者既有插入又有更新的情况下，实验返回主键。从结果离我们已经知道虽然是批量操作但是mybatis只会返回一个主键，并将其设置到第一个实体对应的主键属性上。我们之前实验传入的实体都是不带id的。那么如果我们传入的参数本身就带有id，会发生什么呢？让我们来实验一把。

当前数据库：

输入输出：

一个输入4个实体，其中{id=74, name=boy40}和{id=75, name=boy41}是数据库已经存在的记录，并且程序里的实体有对应的id取值。{name=boy50}和{name=boy51}是新记录，没有id信息。我们将其一起insertOrUpdate并用useGeneratedKeys返回主键信息。插入后的数据库如下所示：

结合PostMan返回的结果，和之前的实验一样，Mybatis将第一个插入的记录boy50的主键79返回给了第一个实体boy=40，也就是改变了实体{id=74, name=boy40}在内存的id。剩下实体主键Mybatis都不返回，如果原先实体就有id，那么id保持不变，比如{id=75, name=boy41}。

到现在大家发现坑在哪里了没有？如果全局默认useGeneratedKeys=true，那么批量InsertOrUpdate时，会存在Mybatis改变内存里已有主键信息风险，而且这种改变很可能是错误的。不仅仅是InsertOrUpdate，我们还能预期其他潜在风险。所以如果全局开启useGeneratedKeys=true，会让我们程序不可控性增加，还是老老实实在需要返回主键的地方明确的指定useGeneratedKeys=true吧。

最后回头看开篇提到的为什么笔者只用insert on duplicate key update做批量更新（单独update语句是无法做批量更新的，只能循环用update的dao更新，效率很低），而不是批量插入或更新，原因就在于批量更新一般是知道实体的主键的，所以也不用返回主键。但是批量更新或者插入，如果想要知道新插入记录的主键，就要开启useGeneratedKeys=true的功能，而在批量插入或更新的情况下返回的主键是错误的。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

MyBatis

Java

主键

插入或更新

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

随机推荐

热门标签