表结构：
drop table if exists `t_user`;
create table `t_user`(
`id` bigint(20) not null auto_increment comment '主键id',
`name` varchar(32) not null default '' comment '姓名',
`phone` varchar(32) not null default '' comment '手机',
`create_time` datetime not null default current_timestamp comment '创建时间',
`update_time` datetime not null default current_timestamp on update current_timestamp comment '更新时间',
primary key (`id`),
unique key `phone` (`phone`),
key `name` (`name`)
) engine=innodb auto_increment=1000 default charset=utf8mb4 comment='用户表';
一、多条update语句模式
注意需要开启批量执行sql参数allowMultiQueries，参考MySQL中jdbc驱动开启批量执行sql参数allowMultiQueries
XML文件：
<update id="batchUpdate">
<foreach collection="list" item="item">
update t_user
set
`name` = #{item.name},
`phone` = #{item.phone},
`create_time` = #{item.createTime},
`update_time` = #{item.updateTime}
where id = #{item.id};
</foreach>
</update>
执行样例SQL：
update t_user set `name` = 'a8adecfd-f050-4c23-ad97-11c037b1', `phone` = 'b2802f1f-2119-49b3-a867-b3f71672', `create_time` = '2023-12-04 16:08:22.291', `update_time` = '2023-12-04 16:08:22.291' where id = 1000;
update t_user set `name` = '063911e5-951c-4f14-8729-75b91d23', `phone` = '5e305d67-9061-42ad-b9bf-13ba8fed', `create_time` = '2023-12-04 16:08:22.291', `update_time` = '2023-12-04 16:08:22.291' where id = 1001;
update t_user set `name` = 'ac3e69c6-def8-4e5c-9b99-6e4bbd13', `phone` = 'a665aec2-876f-42cd-a64a-02d89cc8', `create_time` = '2023-12-04 16:08:22.291', `update_time` = '2023-12-04 16:08:22.291' where id = 1002;
二、on duplicate key update模式
即插入数据，插入失败时（主键或唯一键冲突）则进行更新，实现有则更新无则插入，参考MySQL下使用on duplicate key update实现“有则更新，无则新增”操作
XML文件：
<insert id="batchUpdateOnDuplicateKey">
insert into t_user (id, `name`, `phone`, `create_time`, `update_time`)
values
<foreach collection="list" item="item" separator=",">
(
#{item.id},
#{item.name},
#{item.phone},
#{item.createTime},
#{item.updateTime}
)
</foreach>
on duplicate key update
`name` = values(`name`),
`phone` = values(`phone`),
`create_time` = values(`create_time`),
`update_time` = values(`update_time`)
</insert>
执行样例SQL：
insert into t_user (id, `name`, `phone`, `create_time`, `update_time`)
values
( 1000, 'f6be36f4-6ef1-4b66-b0a7-e1ea1330', '65b22575-2e89-4df3-bd8b-c8ea2ace', '2023-12-04 16:10:43.529', '2023-12-04 16:10:43.529' ) ,
( 1001, 'b82083d8-b2ca-4a6f-b49d-b6c024b5', '99359904-8f42-4337-a319-0c1deb0c', '2023-12-04 16:10:43.529', '2023-12-04 16:10:43.529' ) ,
( 1002, '667a371d-00aa-4146-b342-5e9300d3', 'd15aeda4-4fb4-472d-9903-a020a61f', '2023-12-04 16:10:43.529', '2023-12-04 16:10:43.529' )
on duplicate key update
`name` = values(`name`),
`phone` = values(`phone`),
`create_time` = values(`create_time`),
`update_time` = values(`update_time`)
三、case when模式
相当于在sql中写if语句，缺点是sql会比较长。
XML文件：
<update id="batchUpdateCaseWhen">
update t_user
set
`name` = case
<foreach collection="list" item="item">
when id = #{item.id} then #{item.name}
</foreach>
end,
`phone` = case
<foreach collection="list" item="item">
when id = #{item.id} then #{item.phone}
</foreach>
end,
`create_time` = case
<foreach collection="list" item="item">
when id = #{item.id} then #{item.createTime}
</foreach>
end,
`update_time` = case
<foreach collection="list" item="item">
when id = #{item.id} then #{item.updateTime}
</foreach>
end
where id in
<foreach collection="list" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
执行样例SQL：
update
t_user
set
`name` = case
when id = 1000 then 'd7242b1f-3539-49e8-96c8-1cf4c365'
when id = 1001 then 'e84725b8-a221-4509-b892-e5ed7f9f'
when id = 1002 then '60cce924-448e-4225-807f-970d6517'
end,
`phone` = case
when id = 1000 then '52ac7cf4-6ca5-455f-b982-b05c571e'
when id = 1001 then 'b9cac4e2-0be9-4252-9cfb-d23da75e'
when id = 1002 then 'eb7ca69d-be5b-4212-b290-58e62eec'
end,
`create_time` = case
when id = 1000 then '2023-12-04 16:12:21.881'
when id = 1001 then '2023-12-04 16:12:21.881'
when id = 1002 then '2023-12-04 16:12:21.881'
end,
`update_time` = case
when id = 1000 then '2023-12-04 16:12:21.881'
when id = 1001 then '2023-12-04 16:12:21.881'
when id = 1002 then '2023-12-04 16:12:21.881'
end
where
id in ( 1000 , 1001 , 1002 )
四、join模式
相当于查一个临时表出来去join欲更新的表，然后映射更新数据。
XML文件：
<update id="batchUpdateJoin">
update t_user m join (
<foreach collection="list" item="item" separator="union all">
select #{item.id} as id, #{item.name} as `name`, #{item.phone} as `phone`, #{item.createTime} as `create_time`, #{item.updateTime} as `update_time`
</foreach>
) t using(id)
set
m.`name` = t.`name`,
m.`phone` = t.`phone`,
m.`create_time` = t.`create_time`,
m.`update_time` = t.`update_time`
where m.id in
<foreach collection="list" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
执行样例SQL：
update t_user m join
(
select 1000 as id, '7854c1e4-7f22-4de3-9304-825e4ab7' as `name`, 'b9ce7be2-8c43-4ee2-ab6b-0b26833c' as `phone`, '2023-12-04 16:16:41.127' as `create_time`, '2023-12-04 16:16:41.127' as `update_time`
union all
select 1001 as id, '93334332-6197-4820-a1ab-932bd066' as `name`, 'e2baef75-6818-498f-bf62-a8780e9a' as `phone`, '2023-12-04 16:16:41.127' as `create_time`, '2023-12-04 16:16:41.127' as `update_time`
union all
select 1002 as id, 'b20d40b5-e719-4b81-b453-943aad82' as `name`, '701a6a51-b7e4-4314-8345-2661e7e0' as `phone`, '2023-12-04 16:16:41.127' as `create_time`, '2023-12-04 16:16:41.127' as `update_time`
) t using(id)
set
m.`name` = t.`name`,
m.`phone` = t.`phone`,
m.`create_time` = t.`create_time`,
m.`update_time` = t.`update_time`
where m.id in ( 1000 , 1001 , 1002 )
