Use batch update sql has an Error in Mysql Database.

前言

    前些时间开发一个功能模块，使用的是Mysql5.7数据库，用MyBatis注解方式批量更新数据。结果遇到一个坑，这里记录一下，给同样遇到类似问题的人一个参考。

问题

    本人使用注解方式开发模块，而注解方式有两种：

• 批量更新数据方法（1）

  1 2 3 4 5 6 7 8 9

  @Update( "<script>" + "<foreach collection = 'obj' item ='item' open='' close='' separator=';'>" + "UPDATE table_name SET db_filed_name =#{item.entity_name} " + "WHERE db_filed_name =#{item.entity_name} " + "AND db_filed_name=#{item.entity_name}" + "</foreach>" + "</script>") int updateBatch(@Param("obj") List<Object> objs);

• 批量更新数据方法（2）

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  @Update({ "<script> UPDATE table_name " + "<trim prefix ='set' prefixOverrides=',' > " + "<trim prefix ='db_filed_name = case' suffix='end'>" + "<foreach collection ='objs' item ='item' index = 'index'> " + "WHEN db_filed_name = #{item.entity_name} THEN #{item.entity_name} " + "</foreach>" + "</trim> " + "</trim> " + "WHERE db_filed_name IN " + "<foreach collection ='objs' item ='items' index ='index' separator=',' open='(' close=')' > " + "#{items.entity_name} " + "</foreach> " + "</script>"}) int updateBatchName(@Param("objs") List<Object> objs);

这次我使用的是第一种方式，结果报了如下错误：

1

Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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

解决

    当时以为是sql错误，直接把sql贴到Navicat直接运行，发现sql没有问题。继续查询解决方法，结果发现原来mysql的批量更新是要我们主动去设置的。而之前一直用Oracle 11g。但是一直没有类似的问题。mysql的批量更新设置，在url末尾加&allowMultiQueries=true即可。

1
2
3
4

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mytest_db?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=123456

延伸

    mybatis批量更新踩坑
    Mysql批量更新的一个坑-&allowMultiQueries=true允许批量更新
    Jdbc Url 设置allowMultiQueries为true和false时底层处理机制研究