通过MyBatis执行批量更新操作时的错误

使用动态sql标签，执行多条update语句

<update id="updateBatch">
        <foreach collection="flavors" item="dishFlavor" separator=";">
            update dish_flavor
            set
            name = #{dishFlavor.name},
            value = #{dishFlavor.value}
            where id = #{dishFlavor.id}
        </foreach>
</update>

报错信息

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update dish_flavor
            set
            name = '忌口',
            valu' at line 7
; bad SQL grammar []

原因

“

因为我们设置了每条更新语句以";"结尾，

但是MySQL数据库，默认不支持一次执行多条语句

所以在执行时，会导致报错

”

解决

我们需要开启MySQL的多条语句执行的权限

指定参数allowMultiQueries=true

以SpringBoot为例

需要在application.yml配置文件中，设置MySQL的url数据源的参数

datasource:  
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://${host}:${port}/${database}?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&allowMultiQueries=true
    username: root
    password: root