新增主键冲突-mysql、达梦

代码不听话

已于 2023-09-01 16:00:20 修改

阅读量288

点赞数

文章标签： sql mysql

于 2022-08-16 20:01:43 首次发布

本文链接：https://blog.csdn.net/qq_39132795/article/details/126373329

版权

mysql

 INSERT INTO IES_FICt_pack_info  (
            guid,
            package_no,
            package_name,
            esc_id,
            package_type,
            package_start,
            package_end,
            file_id,
            hits,
            operator_name,
            operator_time,
            Release_time,
            package_status)
        VALUES
        (
        #{guid},
        #{packageNo},
        #{packageName},
        #{escId},
        #{packageType},
        #{packageStart},
        #{packageEnd},
        #{fileId},
        #{hits},
        #{operatorName},
        #{operatorTime},
        #{ReleaseTime},
        #{packageStatus})
        ON DUPLICATE KEY UPDATE
        guid = values(guid),
        package_no = #{packageNo},
        package_name = #{packageName},
        esc_id = #{escId},
        package_type = #{packageType},
        package_start = #{packageStart},
        package_end = #{packageEnd},
        file_id = #{fileId},
        hits = #{hits},
        operator_name = #{operatorName},
        operator_time = #{operatorTime},
        Release_time = #{ReleaseTime},
        package_status = #{packageStatus}

insert ignore into table_name values…

使用insert ignore语法插入数据时，如果发生主键或者唯一键冲突，则忽略这条插入的数据。

达梦

 /**
     * 新增主键冲突修改
     * @param paramList 主表全部字段
     * @param pkColumnList 主键
     * @param tableName 表名
     * @param tablenameTemp 临时表名
     * @param noList 主表无主键字段
     * @param stringList 临时表全部字段
     * @return
     */
    int insertOrUpdateMarketBLog(@Param("keys") List<Object> paramList, @Param("pkColumnList") List<String> pkColumnList,
                                 @Param("tableNames") String tableName, @Param("tableNameTemp") String tablenameTemp,
                                 @Param("noList") List<String> noList,@Param("stringList") List<Object> stringList,@Param("owner") String woner);

    <insert id="insertOrUpdateMarketBLog">
        MERGE INTO "${owner}"."${tableNames}" t1 USING (
        select
        <foreach collection="stringList" item="key" separator=",">
            ${key}
        </foreach>
        from "${owner}"."${tableNameTemp}"
        ) t2  on
        <foreach collection="pkColumnList" item="key" open="(" close=")" separator="and">
            t1.${key} = t2.${key}
        </foreach>
        when matched then update set
        <foreach collection="noList" item="key" separator=",">
            t1.${key} = t2.${key}
        </foreach>
        when not matched then insert
        <foreach collection="keys" item="key" open="(" close=")" separator=",">
            ${key}
        </foreach>
        values
        <foreach collection="keys" item="key" open="(" close=")" separator=",">
            t2.${key}
        </foreach>
    </insert>