# 创建表
CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键(自增id)'
`name` varchar(20) DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='测试';
# 插入测试数据
INSERT INTO test (name) VALUES ('a');
INSERT INTO test (name) VALUES ('b');
INSERT INTO test (name) VALUES ('c');
INSERT INTO test (name) VALUES ('d');
INSERT INTO test (name) VALUES ('e');
INSERT INTO test (name) VALUES ('f');
INSERT INTO test (name) VALUES ('g');
# 查询
select * from test;
# 批量更新
update
test
set
name = case
id WHEN 1 then 'a2'
WHEN 2 then 'b2'
WHEN 3 then 'c2'
WHEN 4 then 'd2'
else '-'
end
where
id in( 1,2,3,4);
# mybatis mapper.xml
<update id="updateAllJobJson" parameterType="com.xxx.entity.testDO">
UPDATE test SET name=
<foreach collection="list" item="item" index="index" separator=" " open="case id" close="end">
when #{ item.id} then #{ item.name}
</foreach>
where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{ item.id}
</foreach>
</update>
# mybatis mapper.java
@Select("<script>" +
"\t\tUPDATE test SET name=\n" +
"\t\t<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\" \" open=\"case id\" close=\"end\">\n" +
"\t\t\twhen #{ item.id} then #{ item.name}\n" +
"\t\t</foreach>\n" +
"\t\twhere id in\n" +
"\t\t<foreach collection=\"list\" index=\"index\" item=\"item\" separator=\",\" open=\"(\" close=\")\">\n" +
"\t\t\t#{ item.id}\n" +
"\t\t</foreach>" +
"</script>")
int t1(List<TestDO> list);
mysql(54) : 根据id列表批量更新不通内容
最新推荐文章于 2024-10-22 12:02:03 发布