mysql(54) : 根据id列表批量更新不通内容

# 创建表
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);