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 ( ?, ?, ?, ? ) , ( ?, ?, ?, ? )
posted @ 2023-01-31 14:16  转身刹那的潇洒  阅读(9186)  评论(0编辑  收藏  举报