mybatis+mysql 批量新增，存在则更新的写法（包含判断每个字段是否为空）

网上查阅了其他的文章，均报错SQL错误，试了一下只需要简单的修改即可，直接上代码：

 <insert id="insertOrUpdateListSelective" parameterType="java.util.List">
    <foreach collection="deviceList" item="item" index="index" open="" close="" separator=";">
      insert into device
      <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="item.id != null">
          id,
        </if>
        <if test="item.deviceid != null">
          deviceid,
        </if>
        <if test="item.deviceName != null">
          device_name,
        </if>
        <if test="item.dtId != null">
          dt_id,
        </if>
        <if test="item.xmId != null">
          xm_id,
        </if>
        <if test="item.caId != null">
          ca_id,
        </if>
        <if test="item.fId != null">
          f_id,
        </if>
        <if test="item.cpId != null">
          cp_id,
        </if>
        <if test="item.grId != null">
          gr_id,
        </if>
        <if test="item.status != null">
          status,
        </if>
        <if test="item.realTimeUuid != null">
          real_time_uuid,
        </if>
        <if test="item.createTime != null">
          create_time,
        </if>
        <if test="item.updateTime != null">
          update_time,
        </if>
        <if test="item.deviceComment != null">
          device_comment,
        </if>
        <if test="item.deviceComment1 != null">
          device_comment1,
        </if>
        <if test="item.deviceComment2 != null">
          device_comment2,
        </if>
        <if test="item.deviceComment3 != null">
          device_comment3,
        </if>
        <if test="item.deviceComment4 != null">
          device_comment4,
        </if>
        <if test="item.deviceComment5 != null">
          device_comment5,
        </if>
        <if test="item.deviceComment6 != null">
          device_comment6,
        </if>
        <if test="item.deviceComment7 != null">
          device_comment7,
        </if>
        <if test="item.deviceComment8 != null">
          device_comment8,
        </if>
        <if test="item.deviceComment9 != null">
          device_comment9,
        </if>
        <if test="item.deviceComment10 != null">
          device_comment10,
        </if>
      </trim>
      <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="item.id != null">
          #{item.id,jdbcType=INTEGER},
        </if>
        <if test="item.deviceid != null">
          #{item.deviceid,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceName != null">
          #{item.deviceName,jdbcType=VARCHAR},
        </if>
        <if test="item.dtId != null">
          #{item.dtId,jdbcType=INTEGER},
        </if>
        <if test="item.xmId != null">
          #{item.xmId,jdbcType=INTEGER},
        </if>
        <if test="item.caId != null">
          #{item.caId,jdbcType=INTEGER},
        </if>
        <if test="item.fId != null">
          #{item.fId,jdbcType=INTEGER},
        </if>
        <if test="item.cpId != null">
          #{item.cpId,jdbcType=INTEGER},
        </if>
        <if test="item.grId != null">
          #{item.grId,jdbcType=INTEGER},
        </if>
        <if test="item.status != null">
          #{item.status,jdbcType=INTEGER},
        </if>
        <if test="item.realTimeUuid != null">
          #{item.realTimeUuid,jdbcType=VARCHAR},
        </if>
<!--        <if test="createTime != null">-->
<!--          #{createTime,jdbcType=TIMESTAMP},-->
<!--        </if>-->
<!--        <if test="updateTime != null">-->
<!--          #{updateTime,jdbcType=TIMESTAMP},-->
<!--        </if>-->
        <if test="item.deviceComment != null">
          #{item.deviceComment,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment1 != null">
          #{item.deviceComment1,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment2 != null">
          #{item.deviceComment2,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment3 != null">
          #{item.deviceComment3,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment4 != null">
          #{item.deviceComment4,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment5 != null">
          #{item.deviceComment5,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment6 != null">
          #{item.deviceComment6,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment7 != null">
          #{item.deviceComment7,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment8 != null">
          #{item.deviceComment8,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment9 != null">
          #{item.deviceComment9,jdbcType=VARCHAR},
        </if>
        <if test="item.deviceComment10 != null">
          #{item.deviceComment10,jdbcType=VARCHAR},
        </if>
      </trim>
      <!-- 区别在于这里，只需要将判断重复主键方法写在<foreach>标签内即可实现 -->
      ON DUPLICATE KEY UPDATE
      device_name = VALUES(device_name),
      dt_id = VALUES(dt_id),
      xm_id = VALUES(xm_id),
      gr_id = VALUES(gr_id),
      status = VALUES(status),
      real_time_uuid = VALUES(real_time_uuid),
      device_comment = VALUES(device_comment),
      device_comment1 = VALUES(device_comment1),
      device_comment2 = VALUES(device_comment2),
      device_comment3 = VALUES(device_comment3),
      device_comment4 = VALUES(device_comment4),
      device_comment5 = VALUES(device_comment5),
      device_comment6 = VALUES(device_comment6),
      device_comment7 = VALUES(device_comment7),
      device_comment8 = VALUES(device_comment8),
      device_comment9 = VALUES(device_comment9),
      device_comment10 = VALUES(device_comment10)
      <!-- 区别结束 -->
    </foreach>
    <!-- 原判断写在这里，报错！ -->
  </insert>