on duplicate key update的多种使用方式

一.on dupdate key update 语句基本功能是：当表中没有原来记录时，就插入，有的话就更新

1，on duplicate key update 语句根据主键id来判断当前插入是否已存在。
2，已存在时，只会更新on duplicate key update之后限定的字段。

二.on duplicate key update 语句也可以根据唯一键来判断当前插入的记录是否已存在

比如唯一主键PRIMARY KEY

其实就这些东西,总结起来就是判断是否需要新增还是更新数据,可以按照主键id或者唯一键来判定

<insert id="insertOrUpdate" keyProperty="id" useGeneratedKeys="true">
        INSERT INTO dst_111yao_doms.d_srcorder_trans_num(id, order_id, lock_rule_no, trans_number, trans_msg,
        if_lock, time_stamp, create_time, update_time, ext1, ext2, ext3, ext4, ext5)
        VALUES (#{id}, #{orderId}, #{lockRuleNo}, 1, #{transMsg}, #{ifLock}, #{timeStamp},
        #{createTime}, #{updateTime}, #{ext1}, #{ext2}, #{ext3}, #{ext4}, #{ext5})
        ON DUPLICATE KEY UPDATE
        id = values(id) , order_id = values(order_id) ,lock_rule_no = values(lock_rule_no), trans_number = trans_number +1 ,
        trans_msg = values(trans_msg) ,
        <if test="ifLock != null">
            if_lock = values(if_lock) ,
        </if>
         create_time = values(create_time) , update_time = values(update_time) , ext1 = values(ext1) , ext2 = values(ext2) , ext3 =
        values(ext3) , ext4 = values(ext4) , ext5 = values(ext5)
    </insert>

@Override
    public boolean addTransErrorNums(Long id, String orderId, String transMsg, String lockRuleNo, int ifLock) {
        DSrcorderTransNum dSrcorderTransNum = new DSrcorderTransNum();
        dSrcorderTransNum.setId(id);
        dSrcorderTransNum.setOrderId(orderId);
        dSrcorderTransNum.setTransMsg(transMsg);
        dSrcorderTransNum.setLockRuleNo(lockRuleNo);
        dSrcorderTransNum.setIfLock(ifLock);
        int i = this.dSrcorderTransNumMapper.insertOrUpdate(dSrcorderTransNum);
        return i > 0;
    }

if (dSrcorderTransNums0071 != null && dSrcorderTransNums0071.size() > 0) {
                        DSrcorderTransNum dSrcorderTransNum = dSrcorderTransNums0071.get(0);
                        Long id = dSrcorderTransNum.getId();
                        dSrcorderTransNumService.addTransErrorNums(id, orderId, message, lock_7_1, 0);
                    } else {
                        dSrcorderTransNumService.addTransErrorNums(null, orderId, message, lock_7_1, 0);
                    }

上面的代码例子:id如果传空,那么就是走新增呢,id传值,那么就是更新呢