记录一次踩坑记录（Druid+MyBatis+MySQL）使用UNION关键字

场景，批量插入，如果存在相同的记录则不插入，否则就新增一条记录

Mapper以上的逻辑忽略，重点看Mapper.xml

<insert id="batchInsertIfNotExist" parameterType="java.util.List">
    INSERT INTO logistics_orders_state (id,order_num,accept_time,accept_station,spare1)
    <foreach item="item" collection="list" index="index" separator="UNION">
        (SELECT #{item.id},#{item.orderNum},#{item.acceptTime},#{item.acceptStation},#{item.spare1} FROM DUAL WHERE
        NOT EXISTS (SELECT order_num,accept_time FROM logistics_orders_state WHERE order_num = #{item.orderNum} AND
        accept_time = #{item.acceptTime}))
    </foreach>
</insert>

解析完以后的语句

INSERT INTO logistics_orders_state (id,order_num,accept_time,accept_station,spare1) (SELECT ?,?,?,?,? FROM DUAL WHERE NOT EXISTS (SELECT order_num,accept_time FROM logistics_orders_state WHERE order_num = ? AND accept_time = ?)) UNION (SELECT ?,?,?,?,? FROM DUAL WHERE NOT EXISTS (SELECT order_num,accept_time FROM logistics_orders_state WHERE order_num = ? AND accept_time = ?))

然后控制台报错

记录一次踩坑记录（Druid+MyBatis+MySQL）使用UNION关键字

上面的解析完的语句在 navicat上是可以执行的！

找了半下午问题的原因，最后

NSERT INTO logistics_orders_state (id,order_num,accept_time,accept_station,spare1) SELECT ?,?,?,?,? FROM DUAL WHERE NOT EXISTS (SELECT order_num,accept_time FROM logistics_orders_state WHERE order_num = ? AND accept_time = ?)) UNION (SELECT ?,?,?,?,? FROM DUAL WHERE NOT EXISTS (SELECT order_num,accept_time FROM logistics_orders_state WHERE order_num = ? AND accept_time = ?)

好了，去掉了一对括号！就可以了。。。。

记录一次踩坑记录（Druid+MyBatis+MySQL）使用UNION关键字

相关推荐