insert IGNORE 和 insert 和 insert into on duplicate key update 更新数据实例

前置情况测试:

存在某一个主键:

进行批量插入:

-- 测试数据  测试插入
INSERT INTO goods_spu_sort(channel,sku_code,sku_role)
        VALUES(1,'10001','skuRole');
                INSERT INTO goods_spu_sort(channel,sku_code,sku_role)
        VALUES(2,'10001','skuRole');
                INSERT INTO goods_spu_sort(channel,sku_code,sku_role)
        VALUES(2,'10002','skuRole');

-- 批量插入-------->结果:最后一条重复,全部无效
insert   into `goods_spu_sort`(channel,sku_code,sku_role,spu_code) values
(9,'10002','skuRole','0000'),(1,'10001','skuRole','000');

-- 忽略重复数据-------->结果:最后一条重复，忽略掉重复数据
insert  IGNORE into `goods_spu_sort`(channel,sku_code,sku_role,spu_code) values
(9,'10001','skuRole','666'),(1,'10001','skuRole','999');

===========================查找解决方案 replace into 和 insert into on duplicate key update ===============

两者的区别:

-- replace into  和insert into on duplicate key update的不同在于：
-- replace into　操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值
-- insert into on duplicate key update 则是只update重复记录，不会改变其它字段。
-- 最终解决方案如下:

根据unique index  使用   insert into on duplicate key update
insert into goods_spu_sort(channel,sku_code,sku_role,spu_code) values (9,'10002','skuRole','修改后的值V1') on duplicate key  update spu_code = ('2019/3/6');
insert into goods_spu_sort(channel,sku_code,sku_role,spu_code) values (9,'10002','skuRole','修改后的值V1') on duplicate key  update spu_code = (null);

//能够有效避免 replace into 更新的时候有效字段被清空 

<insert id="insertOnDuplicateKey" parameterType="XX.GoodsSpuSort" >
    insert into goods_spu_sort
    (id, store, channel,
    sku_status, sku_code, sku_visiable,
    sku_role, spu_status, spu_type,
    spu_code, sold_out, total_point,
    recent_shelf_time, sell_out_time, unique_key
    )
    values (#{id,jdbcType=INTEGER},#{store,jdbcType=VARCHAR},#{channel,jdbcType=INTEGER},#{skuStatus,jdbcType=INTEGER},#{skuCode,jdbcType=VARCHAR},#{skuVisiable,jdbcType=INTEGER},
      #{skuRole,jdbcType=VARCHAR},#{spuStatus,jdbcType=INTEGER},#{spuType,jdbcType=VARCHAR},#{spuCode,jdbcType=VARCHAR},#{soldOut,jdbcType=INTEGER},#{totalPoint,jdbcType=INTEGER},
      #{recentShelfTime,jdbcType=BIGINT},#{sellOutTime,jdbcType=BIGINT},#{uniqueKey,jdbcType=VARCHAR}
      )
        ON DUPLICATE KEY
        <trim prefix="UPDATE" suffixOverrides="," >
            <if test="skuRole != null" >
            sku_role =#{skuRole,jdbcType=VARCHAR},
            </if>
            <if test="skuCode != null" >
                sku_code = #{skuCode,jdbcType=VARCHAR},
            </if>
            <if test="channel != null" >
                channel = #{channel,jdbcType=INTEGER},
            </if>
            <if test="store != null" >
                store =#{store,jdbcType=VARCHAR},
            </if>
        <if test="skuStatus != null" >
            sku_status =#{skuStatus,jdbcType=INTEGER},
        </if>
        <if test="skuVisiable != null" >
            sku_visiable =#{skuVisiable,jdbcType=INTEGER},
        </if>
        <if test="spuStatus != null" >
            spu_status =#{spuStatus,jdbcType=INTEGER},
        </if>
        <if test="spuType != null" >
            spu_type =#{spuType,jdbcType=VARCHAR},
        </if>
        <if test="spuCode != null" >
            spu_code =#{spuCode,jdbcType=VARCHAR},
        </if>
        <if test="soldOut != null" >
            sold_out =#{soldOut,jdbcType=INTEGER},
        </if>
        <if test="totalPoint != null" >
            total_point =#{totalPoint,jdbcType=INTEGER},
        </if>
        <if test="recentShelfTime != null" >
            recent_shelf_time =#{recentShelfTime,jdbcType=BIGINT},
        </if>
        <if test="sellOutTime != null" >
            sell_out_time =#{sellOutTime,jdbcType=BIGINT},
        </if>
        </trim>
  </insert>