ORACLE的merge into与MYSQL的replace into以及on duplicate key

本文以mybatis为背景

1、oracle用法：

MERGE INTO une_cbill_cloud  c USING (
            SELECT
            #{fid,jdbcType=VARCHAR} fid,
            #{fbid,jdbcType=VARCHAR} fbid,
            #{fissueType,jdbcType=VARCHAR} fissueType,
            #{feinvoiceCode,jdbcType= VARCHAR } feinvoiceCode,
            #{feinvoiceNumber,jdbcType= VARCHAR } feinvoiceNumber,
            #{feinvoiceContent,jdbcType=VARCHAR} feinvoiceContent,
            #{fcarrierName,jdbcType= VARCHAR } fcarrierName,
            #{fcarrierType,jdbcType= VARCHAR } fcarrierType,
            #{fcarrierValue,jdbcType= VARCHAR } fcarrierValue,
            #{finvoiceingPartySignature,jdbcType= VARCHAR } finvoiceingPartySignature,
            #{fsignatureType,jdbcType= VARCHAR } fsignatureType,
            #{fsignatureTime,jdbcType= VARCHAR } fsignatureTime,
            #{fserialNumber,jdbcType= VARCHAR } fserialNumber,
            #{frelatedInvoiceCode,jdbcType= VARCHAR } frelatedInvoiceCode,
            #{frelatedInvoiceNumber,jdbcType= VARCHAR } frelatedInvoiceNumber,
            #{fversion,jdbcType= INTEGER } fversion,
            #{finvoicingPartyNoNeedSign,jdbcType= VARCHAR } finvoicingPartyNoNeedSign
            FROM dual
        ) a
        ON (
        c.fbid = a.fbid AND
        fissuetype = a.fissueType
        )
        WHEN matched THEN
        UPDATE SET
        fsignaturetime = a.fsignaturetime,
        fversion = a.fversion
        WHEN NOT matched THEN
        INSERT INTO une_cbill_cloud( fid, fbid, fissuetype, feinvoicecode, feinvoicenumber,feinvoicecontent,fcarriername,fcarriertype, fcarriervalue,finvoiceingpartysignature,fsignaturealgorithm,fsignaturetype,fsignaturetime,fserialnumber,frelatedinvoicecode,frelatedinvoicenumber,fversion,finvoicingpartynoneedsign)
        VALUES (a.fid, a.fbid, a.fissueType, a.feinvoiceCode, a.feinvoiceNumber,a.feinvoiceContent,a.fcarrierName,afcarrierType,a.fcarrierValue, a.finvoiceingPartySignature, a.fsignatureAlgorithm, a.fsignatureType, a.fsignatureTime, a.fserialNumber, a.frelatedInvoiceCode,a.frelatedInvoiceNumber, a.fversion,a.finvoicingPartyNoNeedSign)

2、mysql用法：

replace into示例

replace into UNE_CBILL_PRINT_CLOUD(fid,feinvoicecode,ftype)VALUES('1','1','1');

ON DUPLICATE KEY示例

insert into UNE_CBILL_PRINT_CLOUD(fid,feinvoicecode,ftype)VALUES('1','1','1')
        ON DUPLICATE KEY UPDATE foperator = '1',ftype = '3';

replace into 效率比较底下，因为在更新数据的时候，要先删除旧的，然后插入新的，在这个过程中，还要重新维护索引；

insert on duplicate 的更新操作虽然也会更新数据，但其对主键的索引却不会有改变，也就是说，insert　on duplicate　更新对主键索引没有影响.因此对索引的维护成本就低了一些。