MySql中4种批量更新的方法

1、replace into 批量更新

replace into test_tbl (id,name) values (1,'a'),(2,'b'),(x,'y');

示例：

replace into book (
    'Id',
    'Author',
    'CreatedTime',
    'UpdatedTime'
)
values 
(1,'张飞','2016-12-12 12:20','2016-12-12 12:20'),
(2,'关羽','2016-12-12 12:20','2016-12-12 12:20');

2、insert into on duplicate key update批量更新

表中需要存在唯一索引

insert into test_tbl (id,name) values (1,'a'),(2,'b'),(x,'y') on duplicate key update name=values(name);

示例：

insert into book 
(Id,Author,CreatedTime,UpdatedTime) 
values 
(1,'张飞2','2017-12-12 12:20','2017-12-12 12:21'),
(2,'关羽2','2017-12-12 12:20','2017-12-12 12:21'),
(3,'刘备','2017-12-12 12:21','2017-12-12 12:21')
on duplicate key update 
Author=values(Author),
CreatedTime=values(CreatedTime),
UpdatedTime=values(UpdatedTime);

replace into 和 insert into on duplicate key update的不同在于：

replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值，用这个要悠着点否则不小心清空大量数据可不是闹着玩的。
insert into 则是只update重复记录，不会改变其它字段。

3、创建临时表，先更新临时表，然后从临时表中update

create temporary table tmp(id int(4) primary key, name varchar(50));
insert into tmp values (0,'gone'), (1,'xx'),(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

注意：这种方法需要用户有temporary 表的create 权限。

4、使用mysql 自带的语句构建批量更新

注：mysql 实现批量可以用点小技巧来实现

UPDATE t_user
SET 
age = CASE id 
    WHEN 1 THEN 23 
    WHEN 2 THEN 24 
    WHEN 3 THEN 25 
END,
name = CASE id 
    WHEN 1 THEN '张飞2' 
    WHEN 2 THEN '关羽2' 
    WHEN 3 THEN '刘备2' 
END
WHERE id IN (1,2,3)

mybatis xml示例：


<update id="update" parameterType="java.util.List">
    UPDATE dic_col_display
	SET column_code = CASE id
	<foreach collection="list" item="item" index="index" open="" separator="" close="">
		WHEN #{item.id} THEN #{item.column_code} 
	</foreach>
	END,
	modify_user_name = CASE id
	<foreach collection="list" item="item" index="index" open="" separator="" close="">
		WHEN #{item.id} THEN #{item.modify_user_name} 
	</foreach>
	END,
	modify_date = NOW()
	WHERE
	  id in
	  <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
		  #{item.id} 
	  </foreach>
</update>