on duplicate key update的用法

1.基础用法

test表有如下数据

执行如下sql会报主键冲突

INSERT INTO test ( id, NAME, age )
VALUES ( 1, '张三', 13 )

增加on duplicate key update后执行成功

INSERT INTO test ( id, NAME, age )
VALUES ( 1, '张三', 13 ) 
	ON DUPLICATE KEY UPDATE id = 1,
	NAME = '张三',
	age = 13

执行结果为

2.代码中批量插入时

dao层

void inserts(@Param("list") List<Entity> list);

xml文件

insert into entity (id,ip,label_id,label_name,visit_count) values
<foreach collection="list" item="list" index="index" separator=",">
    (#{list.id},#{list.ip},#{list.labelId},#{list.labelName},#{list.visitCount})
</foreach>
on duplicate key update id = values(id)

3.某个字段的值需要累加

xml文件

insert into ip_data_label (id,ip,label_id,label_name,visit_count) values
<foreach collection="list" item="list" index="index" separator=",">
    (#{list.id},#{list.ip},#{list.labelId},#{list.labelName},#{list.visitCount})
</foreach>
on duplicate key update visit_count= visit_count+values(visit_count)