import lombok.Data;
@Data
public class Sheet {
 
    /**
     * 主鍵
     */
    private String id;
    /**
     * 客戶姓名
     */
    private String customerName;
    /**
     * 。。。省略其他字段
     */
 
    /**
     * 該字段為非業(yè)務字段。Mybatis配置文件需要要到該字段，方便進行insertOrUpdate操作
     */
    private int count;
}

Mybatis的mapper.xml配置文件代碼如下。

代碼含義：先執(zhí)行selectKey語句，把結果賦值給Sheet類的count屬性。

如果count大于0，表示記錄已存在，則進行update操作。
如果count等于0，表示沒有記錄，則進行insert操作。

<update id="insertOrUpdate" parameterType="Sheet" >
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            select count(1) from sheet where ID= #{id}
        </selectKey>
        <if test="count > 0">
            update sheet 
            <set>
                <if test="customerName != null and customerName != ''">
                    CUSTOMER_NAME= #{customerName},
                </if>
            </set>
            where ID = #{id}
        </if>
        <if test="count==0">
            insert into sheet
            <trim prefix="(" suffix=")" suffixOverrides=",">
                ID,
                <if test="customerName != null and customerName != ''">
                    CUSTOMER_NAME,
                </if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                #{id},
                <if test="customerName != null and customerName != ''">
                    #{customerName},
                </if>
            </trim>
        </if>
    </update>

selectKey標簽可以給update標簽中的parameterType屬性（model類）對應的對象設置屬性值。selectKey標簽的屬性描述：

keyProperty：selectKey 語句結果應該被設置的目標屬性。此處對應的就是Sheet類的count屬性。
resultType：結果的類型，此處為屬性count的類型。
order：可以被設置為 BEFORE 或 AFTER。BEFORE表示先執(zhí)行selectKey語句，后執(zhí)行update語句；AFTER表示先執(zhí)行update語句，后執(zhí)行selectKey語句。

Mybatis學習筆記:InsertOrUpdate

環(huán)境

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

前言

以前使用mongodb、JOOQ組件的時候都是有insertOrUpdate的功能，現(xiàn)在使用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 '關聯(lián)id',
  `type` int(11) NOT NULL DEFAULT '0' COMMENT '0：默認',
  `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT ' 狀態(tài)值',
  `create_at` varchar(64) NOT NULL DEFAULT '' COMMENT '創(chuàng)建人',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `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>

說明：

on duplicate key update這個是非常關鍵的地方，需要有唯一鍵和主鍵。
on duplicate key update后面跟著的name=values(name)算是一個固定寫法，作用：動態(tài)的傳入要修改的值。

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

注意：

當使用列別名時，必須在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;

主鍵和唯一索引

現(xiàn)在假設我們有這些索引：

唯一索引：biz_id、name、code

主鍵：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就會接著判斷是否唯一索引沖突：

① 唯一索引不沖突，那么久執(zhí)行更新

② 唯一索引沖突，就會報錯：