Mybatis：不存在则插入，存在则更新或忽略

Mybatis：不存在则插入，存在则更新或忽略

在实际开发中插入时可能存在数据重复问题，需要忽略或替换掉重复的数据（依据某个字段，比如Primary Key或Unique Key来确定是否重复）

其中常用有三种方式：

# 1、on duplicate key update 不存在则插入，存在则更新

# 2、replace into 先删除旧数据再插入最新的数据

# 3、insert ignore into 避免重复插入（存在则忽略）

表实例

表字段：

Column Name	Primary Key	Unique
s_id	true
s_name		true
s_birth
s_Sex

表SQL语句：

CREATE TABLE `student` (
  `s_id` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
  PRIMARY KEY (`s_id`),
  UNIQUE KEY `s_name` (`s_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

一、不存在则插入，存在则更新

单条插入

xml：

<insert id="insertDuplicateKeyUpdate">
    INSERT INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`)
    VALUES
        (
            #{sId,jdbcType=VARCHAR},
            #{sName,jdbcType=VARCHAR},
            #{sBirth,jdbcType=VARCHAR},
            #{sSex,jdbcType=VARCHAR}
        )
    ON DUPLICATE KEY UPDATE
    <trim prefix="" suffixOverrides=",">
        <if test="sId != null and sId != ''">
            s_id = VALUES(s_id),
        </if>
        <if test="sName != null and sName != ''">
            s_name = VALUES(s_name),
        </if>
        <if test="sBirth != null and sBirth != ''">
            s_birth = VALUES(s_birth),
        </if>
        <if test="sSex != null and sSex != ''">
            s_sex = VALUES(s_sex)
        </if>
    </trim>
</insert>

执行SQL：

INSERT INTO student
    (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES
    ( ?, ?, ?, ? )
ON DUPLICATE KEY UPDATE
    s_id = VALUES(s_id), s_name = VALUES(s_name), s_birth = VALUES(s_birth)

批量插入

xml：

<insert id="insertDuplicateKeyUpdateList">
    INSERT INTO student(`s_id`,`s_name`,`s_birth`,`s_sex`)
    VALUES
    <foreach collection="students" item="item" separator=",">
        (
            #{item.sId,jdbcType=VARCHAR},
            #{item.sName,jdbcType=VARCHAR},
            #{item.sBirth,jdbcType=VARCHAR},
            #{item.sSex,jdbcType=VARCHAR}
        )
    </foreach>
    ON DUPLICATE  KEY UPDATE
    <trim prefix="" suffixOverrides=",">
        <foreach collection="students" separator="," item="item">
            <if test="item.sId != null and item.sId != ''">
                s_id = VALUES(s_id),
            </if>
            <if test="item.sName != null and item.sName != ''">
                s_name = VALUES(s_name),
            </if>
            <if test="item.sBirth != null and item.sBirth != ''">
                s_birth = VALUES(s_birth),
            </if>
            <if test="item.sSex != null and item.sSex != ''">
                s_sex = VALUES(s_sex)
            </if>
        </foreach>
    </trim>
</insert>

执行SQL：

INSERT INTO student
    (`s_id`,`s_name`,`s_birth`,`s_sex`)
VALUES 
    ( ?, ?, ?, ? ) ,
    ( ?, ?, ?, ? )
ON DUPLICATE KEY UPDATE
    s_id = VALUES(s_id), s_name = VALUES(s_name), s_birth = VALUES(s_birth), s_sex = VALUES(s_sex) ,
    s_id = VALUES(s_id), s_name = VALUES(s_name), s_birth = VALUES(s_birth), null

二、先删除旧数据再插入最新的数据

单条插入

xml:

<insert id="insertReplaceInto">
    REPLACE INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`)
    VALUES
    (
        #{sId,jdbcType=VARCHAR},
        #{sName,jdbcType=VARCHAR},
        #{sBirth,jdbcType=VARCHAR},
        #{sSex,jdbcType=VARCHAR}
    )
</insert>

执行SQL

REPLACE INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ( ?, ?, ?, ? )

批量插入

xml:

<insert id="insertReplaceIntoList">
    REPLACE INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`)
    VALUES
    <foreach collection="students" separator="," item="item">
        (
            #{item.sId,jdbcType=VARCHAR},
            #{item.sName,jdbcType=VARCHAR},
            #{item.sBirth,jdbcType=VARCHAR},
            #{item.sSex,jdbcType=VARCHAR}
        )
    </foreach>
</insert>

执行SQL

REPLACE INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ( ?, ?, ?, ? ) , ( ?, ?, ?, ? )

三、避免重复插入（存在则忽略）

单条插入

xml:

<insert id="insertIgnoreInto" parameterType="java.util.List">
    INSERT IGNORE INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`)
    VALUES
    (
        #{sId,jdbcType=VARCHAR},
        #{sName,jdbcType=VARCHAR},
        #{sBirth,jdbcType=VARCHAR},
        #{sSex,jdbcType=VARCHAR}
    )
</insert>

执行SQL

INSERT IGNORE INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ( ?, ?, ?, ? )

批量插入

xml:

<insert id="insertIgnoreIntoList">
    INSERT IGNORE INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`)
    VALUES
    <foreach collection="students" separator="," item="item">
    (
        #{item.sId,jdbcType=VARCHAR},
        #{item.sName,jdbcType=VARCHAR},
        #{item.sBirth,jdbcType=VARCHAR},
        #{item.sSex,jdbcType=VARCHAR}
        )
    </foreach>
</insert>

执行SQL

INSERT IGNORE INTO student(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ( ?, ?, ?, ? ) , ( ?, ?, ?, ? )