MySQL做数据批量更新之case-when和replace-into

    之前在做数据迁移项目的时候遇到过某些字段在原表中没有，需要在数据迁移完成之后做数据补充工作。这个时候需要对数据库做批量更新操作。我之前用到过”case-when”方式的批量更新，这种方式的sql语句直观上看起来不太易懂，趁着这个契机，在网络上查找前人分享，发现另一种更”优雅怡人”的方式—REPLACE-INTO。在介绍REPLACE-INTO之前，先看一眼CASE-WHEN方式：

CASE-WHEN:

Code

<update id="updateReturnQuantity" parameterType="java.util.Map">
    update item
    set quantity_return = quantity_return + case id
    <foreach collection="map.entrySet()" index="key" item="value">
      when #{key} then #{value}
    </foreach>
    end
    where id in
    <foreach collection="map.entrySet()" index="key" separator="," open="(" close=")">
      #{key}
    </foreach>
  </update>

    从上述sql语句中可以看到case-when方式的sql较为不易理解，新手容易出错，小编刚开始在这里踩了很多坑，至今心有余悸。而且case-when方式的大批量执行效率也比较低，它在更新的时候没有使用到索引；

REPLACE-INTO

    replace-into语句看起来就很容易理解了，基本上和INSERT-INTO一致；其实它和INSERT-INTO的差别也不大，理解它们之间的差别只需一句话：replace-into在数据库表中没有记录时执行insert-into操作；反之则先执行delete操作，然后再执行insert-into操作；也即有数据时更新，没数据时插入；

Code

<insert id="batchUpdateItem">
  REPLACE INTO item 
  (
    id, item_id, sku_id,creed_id, `name`, tags,specifications, 
    unit_price, `type`,category, quantity, quantity_send,
    quantity_recept, own_amount, parent_id,repository_id, 
    repository_name, image,icon, units, manufacturer,
    place_of_origin, creator, modifier,gmt_created, gmt_modified, 
    is_del,is_history, quantity_return
  )
  values
  <foreach collection="records" index="index" item="item" separator=",">
    (
    #{item.id,jdbcType=BIGINT},#{item.itemId,jdbcType=BIGINT},
    #{item.skuId,jdbcType=BIGINT},#{item.creedId,jdbcType=BIGINT},
    #{item.name,jdbcType=VARCHAR},#{item.tags,jdbcType=VARCHAR},
    #{item.specifications,jdbcType=VARCHAR},#{item.unitPrice,jdbcType=BIGINT},
    #{item.type,jdbcType=CHAR},#{item.category,jdbcType=VARCHAR},
    #{item.quantity,jdbcType=BIGINT},#{item.quantitySend,jdbcType=BIGINT},
    #{item.quantityRecept,jdbcType=BIGINT},#{item.ownAmount,jdbcType=BIGINT},
    #{item.parentId,jdbcType=BIGINT},#{item.repositoryId,jdbcType=BIGINT},
    #{item.repositoryName,jdbcType=VARCHAR},#{item.image,jdbcType=VARCHAR},
    #{item.icon,jdbcType=VARCHAR},#{item.units,jdbcType=VARCHAR},
    #{item.manufacturer,jdbcType=VARCHAR},#{item.placeOfOrigin,jdbcType=VARCHAR},
    #{item.creator,jdbcType=BIGINT},#{item.modifier,jdbcType=BIGINT},
    #{item.gmtCreated,jdbcType=TIMESTAMP},#{item.gmtModified,jdbcType=TIMESTAMP},
    #{item.isDel,jdbcType=BIT},#{item.isHistory,jdbcType=TINYINT},
    #{item.quantityReturn,jdbcType=BIGINT}
    )
  </foreach>
</insert>

    replace-into在那种”首先判断数据是否存在；如果不存在，则插入；如果存在，则更新“的场景下用起来很清爽，但是这种”清爽“是有风险伴随的。如果更新的字段不全会将缺失的字段置为缺省值，同时如果没有主键ID时，会新建一条记录；这种sql有可能会造成数据丢失和主从上表的自增id值不一致。