Mysql唯一键重复新增或删除 mybatis新增或删除

最新推荐文章于 2024-05-04 13:51:18 发布

Connie1451

最新推荐文章于 2024-05-04 13:51:18 发布

阅读量522

点赞数

文章标签： mybatis mysql 数据库

本文链接：https://blog.csdn.net/Connie1451/article/details/126422029

版权

简述

根据唯一键，组合唯一键自动判断sql语句是新增或删除，包含mybatis的xml写法

sql语句

INSERT INTO USER (idcard, NAME, birth)
VALUES('321311314233432423423342','小嘿嘿' ， '2000-05-10'	)
 ON DUPLICATE KEY UPDATE NAME = '小嘿嘿' birth = '2000-05-10'

mybatis中写法


    <insert id="insertOrUpdate">
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
            select LAST_INSERT_ID()
        </selectKey>
        INSERT INTO USER
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="idcard != null">
                idcard,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="birth != null">
                birth,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">

            <if test="idcard != null">
                #{idcard},
            </if>
            <if test="name != null">
                #{ name},
            </if>
            <if test="birth != null">
                #{birth},
            </if>
        </trim>
        ON DUPLICATE KEY UPDATE
        <trim suffixOverrides=",">
            <if test="idcard != null">
                merchant_code = #{idcard,jdbcType=VARCHAR},
            </if>
            <if test="name != null">
                app_id = #{name,jdbcType=VARCHAR},
            </if>
            <if test="birth != null">
                brand_id = #{birth,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>