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