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);

@Insert("<script>" + " insert into  orm_user(name, password,salt, email, phone_number) \n" +
    "        VALUES\n" +
    "        <foreach collection=\"list\" item=\"item\" index=\"index\" separator=\",\">\n" +
    "            (\n" +
    "            #{item.name},#{item.password},#{item.salt},#{item.email},#{item.phoneNumber}" +
    "            )\n" +
    "        </foreach>\n" +
    "        ON DUPLICATE KEY UPDATE\n" +
    "        salt = values(salt),\n" +
    "        password = values(password),\n" +
    "        email = values(email),\n" +
    "        phone_number = values(phone_number),\n" +
    "        last_update_time = now()\n" +
    "</script>")
int batchSave(@Param("list") List<User> list);

 5.  实现自增功能(mysql的自增因为间隙锁会跳值所以不合用)

@Table(name = "meta_entity_version")
public class MetaEntityVersionEo {



    @Column(name = "code")
    @TableField("code")
    private String code;


    @Column(name = "version")
    @TableField("version")
    private Integer version;


    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public Integer getVersion() {
        return version;
    }

    public void setVersion(Integer version) {
        this.version = version;
    }
}




@Mapper
public interface MetaEntityVersionMapper extends  BaseMapper<MetaEntityVersionEo> {

    @Select({"<script>"
            + " INSERT INTO  meta_entity_version(code,version) "
            + " VALUES (#{code},(@newVersion:=#{step})) "
            + " ON DUPLICATE KEY UPDATE "
            + " version=(@newVersion:=version+#{step});"
            + " select @newVersion;"
            + "</script>"})
    Integer incrBy(@Param("code") String code,int step);

}