mysql插入或更新

115 阅读 0 评论 76 点赞

我是靠谱客的博主暴躁蜗牛，最近开发中收集的这篇文章主要介绍mysql插入或更新，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1. 数据插入或更新，既可以在java业务层进行，也可以在mysql数据库层处理。

2. 业务层一般做法是先查询，如果不存在在插入，如果存在则更新，但是查询和插入不是原子性操作，在并发量比较高的时候，可能两个线程都查询某个记录不存在，所以会执行两次插入，然后其中一条必然会因为主键（这里说的主键不是递增主键）冲突而失败。

而数据库层mysql中 insert ... on duplicate key update 是原子性操作，高效又安全。

3. 但是使用数据库层处理的前提是：on duplicate key update 的 key必须是表的主键或者是由某个或多个字段构成的唯一索引。

4. on duplicate key update 可以更新单个或多个字段：附录

-- 更新单个字段
<insert id="saveOrUpdate">
  	insert into 
  		t_model(user_prod_id,user_id,prod_id,num)
  	values
	  	(
	  		#{userProdId},
	  		#{userId},
	  		#{prodId},
	  		#{num}
	  	)
	on duplicate key update
  	num = num+#{num}
  </insert>


-- 批量插入或更新
<insert id="batchInsert" keyProperty="file">
  	insert into 
  		t_model(union_id,mer_id,file,create_time,goods_name)
  	values
  		<foreach collection="list" separator="," item="item">
  			(
  				#{item.unionId},
  				#{item.merId},
  				#{item.file},
  				#{item.createTime},
  				#{item.goodsName}
  			)
  		</foreach>
  	on duplicate key update file = file
  </insert>


-- 更新多个字段
<insert id="saveOrUpdateStatus">
  	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>

5. 批量，同时避免重复插入：

<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>