MySql 添加或更新数据（如果数据符合某条件，则更新数据）

1、设置主键或唯一索引，但主键或唯一索引重复时，就更新数据

<insert id="addOrUpdate">

insert into t_model(order_id,consumer_status,success_status,send_status,create_time)

values (#{orderId}, #{consumerStatus}, #{successStatus}, #{sendStatus}, #{createTime})

on duplicate key update

success_status = #{successStatus},

consumer_status = #{consumerStatus},

update_time = #{updateTime}

</insert>

2、批量插入，同时避免重复插入

<insert id="moveShopProduct">

insert into t_model(cloud_shop_product_id, cloud_shop_id, cloud_category_id, cloud_product_square_id)

<foreach collection="cloudProductSquareIds" item="cloudProductSquareId" separator="union all">

select md5(uuid()), #{cloudShopId}, #{cloudCategoryId}, #{cloudProductSquareId} FROM DUAL

where not exists (

select * from t_modelwhere status = true

and cloud_shop_id=#{cloudShopId}

and cloud_category_id=#{cloudCategoryId}

and cloud_product_square_id=#{cloudProductSquareId}

)
</foreach>
</insert>

3、当满足多个字段同时重复时，才视为重复数据，这时需要添加联合索引

INSERT date_info (cid, date,send_num) VALUES (“C20190705180949872”,“2019-07-01”,500) ON DUPLICATE KEY UPDATE send_num = 500