mysql存在则更新，不存在则插入

Rust入门到删库跑路

已于 2022-10-21 11:11:24 修改

阅读量234

点赞数

文章标签： mysql 数据库

于 2022-09-02 17:58:37 首次发布

本文链接：https://blog.csdn.net/qq_37623854/article/details/126668184

版权

mysql存在则更新，不存在则插入

单条数据

<insert id="insertUpdate" parameterType="com.hx.electric.iot.plugin.types.dataObj.DeviceDo">
        insert
        into el_device (id, name, identifier, lastSynchronizeTime, productId, secretKey, synchronizeStatus, description,
                        updateTime, type, parentId, enabledState, createTime, typeValue)
        values (#{dataObj.id}, #{dataObj.name}, #{dataObj.identifier}, #{dataObj.lastSynchronizeTime},
                #{dataObj.productId}, #{dataObj.secretKey}, #{dataObj.synchronizeStatus}, #{dataObj.description},
                #{dataObj.updateTime}, #{dataObj.type}, #{dataObj.parentId},
                #{dataObj.enabledState}, #{dataObj.createTime}, #{dataObj.typeValue})
        on duplicate key
            update name                = #{dataObj.name},
                   identifier          = #{dataObj.identifier},
                   lastSynchronizeTime = #{dataObj.lastSynchronizeTime},
                   productId           = #{dataObj.productId},
                   secretKey           = #{dataObj.secretKey},
                   description         = #{dataObj.description},
                   updateTime          = #{dataObj.updateTime},
                   parentId            = #{dataObj.parentId},
                   enabledState        = #{dataObj.enabledState}
    </insert>

批量，且忽略插入错误的记录

ignore是忽略错误的记录

<insert id="insertUpdateBatch" parameterType="java.util.List">
        insert ignore
        into el_device (id, name, identifier, lastSynchronizeTime, productId, secretKey, synchronizeStatus, description,
                        updateTime, type, parentId, enabledState, createTime, typeValue)
        values
        <foreach collection="dos" item="item" separator=",">
            (#{item.id}, #{item.name}, #{item.identifier}, #{item.lastSynchronizeTime},
             #{item.productId}, #{item.secretKey}, #{item.synchronizeStatus}, #{item.description},
             #{item.updateTime}, #{item.type}, #{item.parentId},
             #{item.enabledState}, #{item.createTime}, #{item.typeValue})
        </foreach>
        on duplicate key
            update name                = values(name),
                   identifier          = values(identifier),
                   lastSynchronizeTime = values(lastSynchronizeTime),
                   productId           = values(productId),
                   secretKey           = values(secretKey),
                   description         = values(description),
                   updateTime          = values(updateTime),
                   parentId            = values(parentId),
                   enabledState        = values(enabledState)
    </insert>