mysql的ON DUPLICATE KEY的用法

1. 直接更改字段值

Insert into table(code,name)
values('a','aa')
ON DUPLICATE KEY
update updateTime=now()

2. 根据原值修改当前值

传入参数 #{step}

INSERT INTO  table(code,version) 
VALUES ('a',(@newVersion:=#{step})) 
ON DUPLICATE KEY UPDATE 
version=(@newVersion:=version+#{step});

3. 批量修改

-- bean_name为唯一字段        
INSERT INTO job (bean_name,params,cron_expression)
        select  bean_name,params,cron_expression
        from m_job
        where  id=#{dtmJobId}
        ON DUPLICATE KEY UPDATE
        cron_expression=values(cron_expression),
        params=values(params)

 

4. mybatis

    @Insert("<script>" + " insert into  incr_member_point_tm \n" +
            "        ( out_id,type)\n" +
            "        VALUES\n" +
            "        <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\">\n" +
            "            (\n" +
            "            #{item.outId},#{item.type}" +
            "            )\n" +
            "        </foreach>\n" +
            "        ON DUPLICATE KEY UPDATE\n" +
            "        UPDATE_TIME = now()\n" +
            "</script>")
    void batchSave(List<IncrMemberPointTmEo> list);