mysql批量更新 bad SQL grammar [] 错误

最新推荐文章于 2024-12-09 12:42:37 发布

可爱组长

最新推荐文章于 2024-12-09 12:42:37 发布

阅读量3k

点赞数 5

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/qq_20667511/article/details/119674331

版权

mysql 专栏收录该内容

9 篇文章

订阅专栏

出现的问题

### The error occurred while setting parameters
### SQL: update crawler_ip_all                  SET auto_table = ?,                       put_reason = ?,                       put_user = ?,                       put_way = ?,                       createtime = ?,                       updtime = ?                   WHERE ip = ?               ;                  update crawler_ip_all                  SET auto_table = ?,                       put_reason = ?,                       put_user = ?,                       put_way = ?,                       createtime = ?,                       updtime = ?                   WHERE ip = ?
### 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 crawler_ip_all
                 SET auto_table = '1', 
                  ' at line 10
; bad SQL grammar []; nested exception is 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 crawler_ip_all
                 SET auto_table = '1', 
                  ' at line 10

mybatis 批量更新数据库的mapper

 <update id="updateBath" parameterType="java.util.List">
        <if test="list!=null">
            <foreach collection="list" item="item" index= "index" open="" close="" separator =";">
                update crawler_ip_all
                <set>
                    <if test ='null != item.autoTable'> auto_table = #{item.autoTable}, </if>
                    <if test ='null != item.putReason'> put_reason = #{item.putReason}, </if>
                    <if test ='null != item.putUser'> put_user = #{item.putUser}, </if>
                    <if test ='null != item.putWay'> put_way = #{item.putWay}, </if>
                    <if test ='null != item.createtime'> createtime = #{item.createtime}, </if>
                    <if test ='null != item.updtime'> updtime = #{item.updtime} </if>
                </set>
                <where>
                    ip = #{item.ip}
                </where>
            </foreach>
        </if>
    </update>

dao封装的接口

    int updateBath(@Param("list") List<CrawlerIPAllDetailBean> crawlerIPAllDetailBeanList);

最后的解决方案是：

的 url上增加 allowMultiQueries=true 表示允许批量操作