mysql 类似merge的写法

聊我那个暖

已于 2023-02-23 16:29:37 修改

阅读量1.5k

点赞数

文章标签： mysql 数据库 oracle

于 2022-12-01 19:26:46 首次发布

本文链接：https://blog.youkuaiyun.com/c8051f340t/article/details/128137187

版权

在oracle数据库中有merge写法，通过判定唯一键，如果存在则更新，如果不存在则插入。

pg:

在pg数据库中也有这种写法，on conflict语法,需要在ON CONFLICT后指定唯一键以此来判定是更新还是插入

INSERT ... ON CONFLICT [ conflict_target ] conflict_action

INSERT INTO book_borrow_record ( book_id, borrow_times )
VALUES
    ( 'b0001', 1 ),
    ( 'b0002', 1 ),
    ( 'b0003', 1 )
ON CONFLICT ( book_id )
DO UPDATE
    SET borrow_times = book_borrow_record.borrow_times + 1;

mysql:

在mysql中也有类似写法，数据库版本为5.7，写法如下：

INSERT ... ON DUPLICATE KEY UPDATE

mysql在执行sql时会判断插入会不会报数据重复错误，如果会则执行更新。

insert into pay_sign_info (
    uid,user_name,
    id_card,city_code,gateway,
    sign_status,sign_date,release_date,
    sequence,sign_token,bank_card,
    bank_code,sign_mobile,create_time,
    update_time
) values (
    #{uid},#{userName},
    #{idCard},#{cityCode}, #{gateway},
    #{signStatus},#{signDate},#{releaseDate},
    #{sequence},#{signToken},#{bankCard},
    #{bankCode},#{signMobile},#{createTime},
    #{updateTime}
)  ON DUPLICATE KEY
UPDATE sign_status = #{signStatus},
    update_time = #{updateTime}
    <if test="releaseDate != null">
        ,release_date = #{releaseDate}
    </if>

同时这种写法也支持批量插入和批量更新，如下

insert into prescription_auth_info (
        medical_insurance_code,id_card_type,id_card,ec_token,patient_name,insu_admdvs,auth_rxno,epc_token,
        expirex_in,prsc_time,dept_name,vali_end_time,out_order_id,rx_trace_code,long_rx_flag,rx_file,rx_chk_biz_sn,
        rx_sign_verify_sn,dise_codg,merchant_id,patient_phone,creator,updater
        ) values
        <foreach collection="authInfoList" item="authInfo" separator=",">
            (#{authInfo.medicalInsuranceCode},
            #{authInfo.idCardType},
            #{authInfo.idCard},
            #{authInfo.ecToken},
            #{authInfo.patientName},
            #{authInfo.insuAdmdvs},
            #{authInfo.authRxno},
            #{authInfo.epcToken},
            #{authInfo.expirexIn},
            #{authInfo.prscTime},
            #{authInfo.deptName},
            #{authInfo.valiEndTime},
            #{authInfo.outOrderId},
            #{authInfo.rxTraceCode},
            #{authInfo.longRxFlag},
            #{authInfo.rxFile},
            #{authInfo.rxChkBizSn},
            #{authInfo.rxSignVerifySn},
            #{authInfo.diseCodg},
            #{authInfo.merchantId},
            #{authInfo.patientPhone},
            #{authInfo.creator},
            #{authInfo.updater})
        </foreach>
        on duplicate key update
        medical_insurance_code = values(medical_insurance_code),
        long_rx_flag = values(long_rx_flag),
        rx_file = values(rx_file),
        rx_chk_biz_sn = values(rx_chk_biz_sn),
        rx_sign_verify_sn = values(rx_sign_verify_sn),
        dise_codg = values(dise_codg),
        out_order_id = values(out_order_id),
        rx_trace_code = values(rx_trace_code),
        updater = values(updater),
        update_time =NOW()