mysql 类似merge的写法

在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 UPDATESET 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},release_date = #{releaseDate}同时这种写法也支持批量插入和批量更新，如下

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(#{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})on duplicate key updatemedical_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()

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！