2021-06-24

最新推荐文章于 2022-04-15 17:45:27 发布

博客的博博客的客

最新推荐文章于 2022-04-15 17:45:27 发布

阅读量126

点赞数

分类专栏：报错处理

本文链接：https://blog.csdn.net/weixin_44001878/article/details/118190426

版权

报错处理专栏收录该内容

9 篇文章 0 订阅

订阅专栏

mysql 批量更新 update foreach 报错

<!--    通过商品ID和仓库编码更新仓库多条商品库存量    -->
    <update id="updateStockGoodsList">
        <foreach collection="list" item="StockGoods" index="index">
            update stock_goods
            <set>
                <if test="StockGoods.procurePricing != null and StockGoods.procurePricing != ''">
                    procure_pricing = #{StockGoods.procurePricing},
                </if>
                <if test="StockGoods.goodsPricing != null and StockGoods.goodsPricing != ''">
                    goods_pricing = #{StockGoods.goodsPricing},
                </if>
                goods_quantity = #{StockGoods.goodsQuantity}, update_time = #{StockGoods.updateTime}, operator_id = #{StockGoods.operatorId}
            </set>
            where goods_id = #{StockGoods.goodsId} and stock_code = #{StockGoods.stockCode};
        </foreach>
    </update>

报错信息：

Error updating database. 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 stock_goods\n SET goods_quantity = 100, update_time = ‘2021-06’ at line 5\r\n### The error may exist in file [D:\Java\JavaProjects\RuoYi-Vue\ruoyi-system\target\classes\mapper\system\StockGoodsMapper.xml]\r\n### The error may involve defaultParameterMap\r\n### The error occurred while setting parameters\r\n### SQL: update stock_goods SET goods_quantity = ?, update_time = ?, operator_id = ? where goods_id = ? and stock_code = ?; update stock_goods SET goods_quantity = ?, update_time = ?, operator_id = ? where goods_id = ? and stock_code = ?; update stock_goods SET goods_quantity = ?, update_time = ?, operator_id = ? where goods_id = ? and stock_code = ?;\r\n### 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 stock_goods\n SET goods_quantity = 100, update_time = ‘2021-06’ at line 5\n; 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 stock_goods\n SET goods_quantity = 100, update_time = ‘2021-06’ at line 5"

解决方案：&allowMultiQueries=true

url: jdbc:mysql://localhost:3306/workorderDemo?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true