Mybatis学习笔记：实现insertOrUpdate

环境

Intellij IDEA ： 2021.3
Mysql：8+
java:1.8+

前言

以前使用mongodb、JOOQ组件的时候都是有insertOrUpdate的功能，现在使用mybatis似乎没有提供这种功能。
最近研究了，这个功能其实是mysql提供的，利用的是duplicate key update;

---

假设，我们有这么一张表：

CREATE TABLE `relation` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '名称',
  `relation_id` varchar(64) NOT NULL DEFAULT '' COMMENT '关联id',
  `type` int(11) NOT NULL DEFAULT '0' COMMENT '0：默认',
  `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT ' 状态值',
  `create_at` varchar(64) NOT NULL DEFAULT '' COMMENT '创建人',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` varchar(64) NOT NULL DEFAULT '' COMMENT '更新人',
  `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新人',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_relation_id_type` (`relation_id`,`type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意： ix_relation_id_type：唯一索引

Dao

@Mapper
public interface FlowModelMapper {
    void insertOrUpdate(List<FlowModel> flowModel);
}

Mapper XML文件

<insert id="insertOrUpdate">
    insert into flow_model(name, relation_id, type, is_delete,create_at,update_at)
    values
    <foreach collection="list" item="p" index="index" separator=",">
        (
        #{p.name},
        #{p.relationId},
        #{p.type},
        #{p.isDelete},
        #{p.createAt},
        #{p.updateAt}
        )
    </foreach>
    on duplicate key update
    name=values(name),
    update_at=values(update_at)
</insert>

说明：

1. on duplicate key update这个是非常关键的地方，需要有唯一键和主键。
2. on duplicate key update后面跟着的name=values(name)算是一个固定写法，作用：动态的传入要修改的值。

在MySQL 8.0.20之后，VALUES()在mysql未来的版本会被删除。
官方建议，使用列别名的方式来写：

<insert id="insertOrUpdate">
    insert into flow_model(name, relation_id, type, is_delete,create_at,update_at)
    values
    <foreach collection="list" item="p" index="index" separator=",">
        (
        #{p.name},
        #{p.relationId},
        #{p.type},
        #{p.isDelete},
        #{p.createAt},
        #{p.updateAt}
        )
    </foreach>
    AS fm
    on duplicate key update
    name=fm.name,
    update_at=fm.update_at
</insert>

行别名

insert into …values

语法：insert into ...values(...) AS 行别名 ON DUPLICATE KEY UPDATE 使用行别名。

例如：下面的 new就是行别名。

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

列别名

或者是：insert into ...values(...) AS 行别名(列别名,列别名,列别名) ON DUPLICATE KEY UPDATE 使用别名

下面的m,n,p是随便取的列别名

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

注意：

1. 当使用列别名时，必须在VALUES子句后面使用行别名，即使在后面的子句中不使用行别名。

除了insert into … values 场景，insert into …set场景也适用。

语法和上面是一样的：

INSERT INTO t1 SET a=1,b=2,c=3 AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

主键和唯一索引

现在假设我们有这些索引：

1. 唯一索引：biz_id、name、code
2. 主键：id

insert into template_url(id,name, code, url, scope, description,
    biz_id, create_by, create_user_id, update_by, update_user_id)
    values
      (
	1,'yutao','yutao','www.baidu.com','yutao','yutao',0,'yutao',0,'yutao',0
      )
    ON DUPLICATE KEY UPDATE
	name=values(name),
    description=values(description),
    url=values(url),
    scope=values(scope),
    update_by=values(update_by),
    update_user_id=values(update_user_id)

主键冲突

假设这时，主键冲突，那么MySQL就会接着判断是否 唯一索引冲突：
① 唯一索引不冲突，那么久执行更新
② 唯一索引冲突，就会报错：

1062 - Duplicate entry '0-yutao-yutao111' for key 'template_url.uk_biz_id_code_name', Time: 0.004000s

编辑时，唯一索引的字段不要修改

总结

1. insertOrUpdate的实现是基于mysql的on duplicate key update 来实现的。
2. 使用ON DUPLICATE KEY UPDATE，如果行作为新行插入，则每行受影响的行值为1。如果更新现有行，则每行受影响的行值为2；如果将现有行设置为其当前值，则每行受影响的行值为0（可以通过配置，使其受影响的行值为1）。

官方地址：
13.2.6.2 INSERT … ON DUPLICATE KEY UPDATE Statement