mybatis批量插入异常“ bad SQL grammar []，MySQLSyntaxErrorException”的解决

我会带着你远行 2023-02-19 09:23 2阅读 0赞

**一、问题表现：**

批量插入时报错，错误如下：

### 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 ''2020-06-02 00:00:00',
                '2020-06',
                4,
                '1111',' at line 2
    ; bad SQL grammar [];

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plcm92cHA_size_16_color_FFFFFF_t_70][]

**二、分析过程**

数据无问题，单条数据插入也正常，就是在多行insert时报错，xml的脚本如下：

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    
       INSERT INTO `xxxx`
            (<include refid="Insert_Column_List"></include>)
            VALUES
            <foreach collection="list" separator=";" item="p">
                (
                #{p.date},
                #{p.accountPeriod}
                )
            </foreach>
     </insert>

既然单行数据正常，而多行才报错，那着重看报错的换行处，

注意换行策略是“separator=";"，也就是说用分号来区分数据的，但实际list体是用","逗号来分隔数据，用括号和分号分隔不同对象。

问题应该出在separator上，将其改为逗号即解决。

**三、解决**

将“separator=";"改为逗号","即解决。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plcm92cHA_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plcm92cHA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200619185633262.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plcm92cHA=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plcm92cHA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200619185841904.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3plcm92cHA=,size_16,color_FFFFFF,t_70