在MySQL中使用MyBatis <foreach>批量更新报错的解决咨询

在Spring Boot + MyBatis + MySQL的应用中，尝试通过MyBatis执行批量UPDATE操作时触发java.sql.SQLSyntaxErrorException语法错误，单条记录更新正常，列表包含多条记录则报错。

相关代码 #

MyBatis XML更新配置：

<update id="updateAnglersTrackPositions" parameterType="java.util.List">
    <foreach collection="updateTrackPositionsList" item="item" separator=";">
        UPDATE angler
        SET trackNumber = #{item.trackPosition.trackNumber},
        sector = #{item.trackPosition.sector}
        WHERE id = #{item.anglerId}
    </foreach>;
</update>

Mapper接口方法：

int updateAnglersTrackPositions(@Param("updateTrackPositionsList") List<UpdateAnglersTrackPositionRequest> updateTrackPositionsRequest);

环境版本 #

[INFO] +- org.springframework.boot:spring-boot-starter-web:jar:3.2.2:compile
[INFO] +- org.mybatis.spring.boot:mybatis-spring-boot-starter:jar:3.0.3:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-jdbc:jar:3.2.2:compile
[INFO] |  |  +- com.zaxxer:HikariCP:jar:5.0.1:compile
[INFO] |  |  \- org.springframework:spring-jdbc:jar:6.1.3:compile
[INFO] |  |     \- org.springframework:spring-tx:jar:6.1.3:compile
[INFO] |  +- org.mybatis.spring.boot:mybatis-spring-boot-autoconfigure:jar:3.0.3:compile
[INFO] |  \- org.mybatis:mybatis-spring:jar:3.0.3:compile
[INFO] +- com.mysql:mysql-connector-j:jar:8.3.0:compile
[INFO] +- org.mybatis:mybatis:jar:3.5.16:compile

已尝试方案 #

• 在foreach标签中添加open="(" close=")"
• 在foreach闭合标签后追加额外分号
• 在foreach前添加BEGIN ，闭合后添加END;

1. 开启MySQL多语句支持 #

MySQL默认禁止在单个SQL请求中执行多条语句，需在JDBC连接URL中添加allowMultiQueries=true参数。
修改配置文件（application.properties或application.yml）中的数据库连接地址：

spring.datasource.url=jdbc:mysql://localhost:3306/your_db?allowMultiQueries=true&useSSL=false&serverTimezone=UTC

2. 修正MyBatis XML配置 #

移除foreach闭合标签后的多余分号，避免生成重复分号导致语法错误：

<update id="updateAnglersTrackPositions" parameterType="java.util.List">
    <foreach collection="updateTrackPositionsList" item="item" separator=";">
        UPDATE angler
        SET trackNumber = #{item.trackPosition.trackNumber},
        sector = #{item.trackPosition.sector}
        WHERE id = #{item.anglerId}
    </foreach>
</update>

是否可行？ #

完全可行，通过上述配置调整后，MyBatis的foreach标签可以正常生成多条UPDATE语句并批量执行。

是否属于最佳实践？ #

需结合场景判断：

• 适用场景：待更新记录逻辑独立（每条UPDATE的条件和更新值无关联）、批量规模较小（几百条以内）。
• 更优方案：当批量规模较大时，建议使用CASE WHEN拼接成单条UPDATE语句，减少数据库交互次数，性能与安全性更优。示例如下：

<update id="updateAnglersTrackPositions" parameterType="java.util.List">
    UPDATE angler
    SET 
        trackNumber = CASE id
            <foreach collection="updateTrackPositionsList" item="item">
                WHEN #{item.anglerId} THEN #{item.trackPosition.trackNumber}
            </foreach>
            ELSE trackNumber
        END,
        sector = CASE id
            <foreach collection="updateTrackPositionsList" item="item">
                WHEN #{item.anglerId} THEN #{item.trackPosition.sector}
            </foreach>
            ELSE sector
        END
    WHERE id IN
    <foreach collection="updateTrackPositionsList" item="item" open="(" separator="," close=")">
        #{item.anglerId}
    </foreach>
</update>

该方式仅发送一次SQL请求，无需开启allowMultiQueries，避免了多语句执行的额外开销，同时降低SQL注入风险。

内容的提问来源于stack exchange，提问作者Nemanja Novakovic