### 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 tbl_ad_ticket tb SET tb.group_id=5 WHERE tb.group_id IS NULL AND tb.id IN' at line 7
; bad SQL grammar []; nested exception is 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
同事上线了一个功能,在测试环境和预发环境都没有问题,一到线上,就出现了以上的报错,通过排查发现可能出现的问题如下:
1,这个是造成我们的问题 ,sql 如下
<update id="dealTicketGroup">
<choose>
<when test="xx!=null and xx.size!=0">
UPDATE xx tb SET tb.x=NULL WHERE tb.x=#{groupId} AND tb.id NOT IN
<foreach collection="xx" index="index" item="x" open="(" separator="," close=")">
#{x,jdbcType=BIGINT}
</foreach> ;
UPDATE xxtb SET tb.x=#{x} WHERE tb.xIS NULL AND tb.id IN
<foreach collection="xx" index="index" item="x" open="(" separator="," close=")">
#{x,jdbcType=BIGINT}
</foreach>;
</when>
<otherwise>
UPDATE xx tb SET tb.group_id=NULL WHERE tb.group_id=#{groupId}
</otherwise>
</choose>
</update>
问题原因是,因为加了分号,批量修改,需要配置文件增加allowMultiQueries=true 线上的配置文件少了这个造成的
2,最大可能是sql参数的问题