<update id="batchUpdate" parameterType="java.util.List"> UPDATE batchs <set> batch_name = CASE batch_id <foreach collection="list" item="item"> <if test="item.batchName != null">WHEN #{item.batchId} THEN #{item.batchName}</if> </foreach> ELSE batch_name END, model_id = CASE batch_id <foreach collection="list" item="item"> <if test="item.modelId != null and item.modelId != ''">WHEN #{item.batchId} THEN #{item.modelId}</if> </foreach> ELSE model_id END, production_id = CASE batch_id <foreach collection="list" item="item"> <if test="item.productionId != null and item.productionId != ''">WHEN #{item.batchId} THEN #{item.productionId}</if> </foreach> ELSE production_id END, bom_id = CASE batch_id <foreach collection="list" item="item"> <if test="item.bomId != null and item.bomId != ''">WHEN #{item.batchId} THEN #{item.bomId}</if> </foreach> ELSE bom_id END, amounts = CASE batch_id <foreach collection="list" item="item"> <if test="item.amounts != null">WHEN #{item.batchId} THEN #{item.amounts}</if> </foreach> ELSE amounts END, complete_amount = CASE batch_id <foreach collection="list" item="item"> <if test="item.completeAmount != null">WHEN #{item.batchId} THEN #{item.completeAmount}</if> </foreach> ELSE complete_amount END, update_time = CASE batch_id <foreach collection="list" item="item"> <if test="item.updateTime != null">WHEN #{item.batchId} THEN #{item.updateTime}</if> </foreach> ELSE update_time END, update_by = CASE batch_id <foreach collection="list" item="item"> <if test="item.updateBy != null">WHEN #{item.batchId} THEN #{item.updateBy}</if> </foreach> ELSE update_by END, del_flag = CASE batch_id <foreach collection="list" item="item"> <if test="item.delFlag != null">WHEN #{item.batchId} THEN #{item.delFlag}</if> </foreach> ELSE del_flag END, factory_id = CASE batch_id <foreach collection="list" item="item"> <if test="item.factoryId != null">WHEN #{item.batchId} THEN #{item.factoryId}</if> </foreach> ELSE factory_id END </set> WHERE batch_id IN ( <foreach collection="list" item="item" separator=","> #{item.batchId} </foreach> ) </update>怎么修改
时间: 2025-09-12 09:46:30 AIGC 浏览: 25
你的 MyBatis XML 中的 SQL 语句逻辑是正确的,但存在一个 **结构性问题** —— `CASE` 表达式被放在了 `<set>` 标签中,而 `<set>` 是用于自动处理逗号的,这会导致 SQL 语法错误。
---
## ✅ 错误原因分析
### 问题出在 `<set>` 标签的使用方式
你将多个 `CASE ... END` 表达式放在 `<set>` 标签内,但 `<set>` 的作用是**自动去除末尾的逗号**,而你的每个 `CASE` 语句之间都以逗号分隔,这会导致:
- `<set>` 认为这些是多个字段赋值语句
- 但 `CASE ... END` 是一个完整的赋值表达式,不能被拆分成字段
- 最终导致 SQL 语法错误,尤其是 `ELSE del_flag END,` 后面多了一个逗号,破坏了语法结构
---
## ✅ 解决方案
### ✅ 方法一:移除 `<set>`,手动控制逗号
直接使用原生 SQL 的方式,不使用 `<set>`,手动控制字段更新之间的逗号。
### ✅ 修改后的 MyBatis XML 示例:
```xml
<update id="batchUpdate" parameterType="java.util.List">
UPDATE batchs
SET
batch_name = CASE batch_id
<foreach collection="list" item="item">
<if test="item.batchName != null">
WHEN #{item.batchId} THEN #{item.batchName}
</if>
</foreach>
ELSE batch_name
END,
model_id = CASE batch_id
<foreach collection="list" item="item">
<if test="item.modelId != null and item.modelId != ''">
WHEN #{item.batchId} THEN #{item.modelId}
</if>
</foreach>
ELSE model_id
END,
production_id = CASE batch_id
<foreach collection="list" item="item">
<if test="item.productionId != null and item.productionId != ''">
WHEN #{item.batchId} THEN #{item.productionId}
</if>
</foreach>
ELSE production_id
END,
bom_id = CASE batch_id
<foreach collection="list" item="item">
<if test="item.bomId != null and item.bomId != ''">
WHEN #{item.batchId} THEN #{item.bomId}
</if>
</foreach>
ELSE bom_id
END,
amounts = CASE batch_id
<foreach collection="list" item="item">
<if test="item.amounts != null">
WHEN #{item.batchId} THEN #{item.amounts}
</if>
</foreach>
ELSE amounts
END,
complete_amount = CASE batch_id
<foreach collection="list" item="item">
<if test="item.completeAmount != null">
WHEN #{item.batchId} THEN #{item.completeAmount}
</if>
</foreach>
ELSE complete_amount
END,
update_time = CASE batch_id
<foreach collection="list" item="item">
<if test="item.updateTime != null">
WHEN #{item.batchId} THEN #{item.updateTime}
</if>
</foreach>
ELSE update_time
END,
update_by = CASE batch_id
<foreach collection="list" item="item">
<if test="item.updateBy != null">
WHEN #{item.batchId} THEN #{item.updateBy}
</if>
</foreach>
ELSE update_by
END,
del_flag = CASE batch_id
<foreach collection="list" item="item">
<if test="item.delFlag != null">
WHEN #{item.batchId} THEN #{item.delFlag}
</if>
</foreach>
ELSE del_flag
END,
factory_id = CASE batch_id
<foreach collection="list" item="item">
<if test="item.factoryId != null">
WHEN #{item.batchId} THEN #{item.factoryId}
</if>
</foreach>
ELSE factory_id
END
WHERE batch_id IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.batchId}
</foreach>
</update>
```
---
### ✅ 方法二(可选):确保每个 `CASE` 都至少有一个 `WHEN` 子句
如果 `<foreach>` 遍历的集合为空,那么 `CASE` 中就没有 `WHEN` 子句,也会导致 SQL 语法错误。
你可以加入一个判断,防止 `<foreach>` 为空时生成无效的 `CASE` 语句。例如:
```xml
<if test="list != null and !list.isEmpty()">
batch_name = CASE batch_id
<foreach collection="list" item="item">
<if test="item.batchName != null">
WHEN #{item.batchId} THEN #{item.batchName}
</if>
</foreach>
ELSE batch_name
END,
</if>
```
---
## ✅ 总结
你遇到的问题是因为 `<set>` 和 `CASE ... END` 混用导致 SQL 语法错误。推荐方案是:
- ✅ 移除 `<set>` 标签
- ✅ 手动控制 SQL 字段更新结构
- ✅ 确保每个 `CASE` 至少有一个 `WHEN` 子句
- ✅ 对空集合进行判断,避免生成非法 SQL
---
阅读全文
