MySQL使用on duplicate key update
on duplicate key update进行自动判断是更新还是新增(MySQL判断记录是否存在的依据是主键或者唯一索引,
insert在主键或者唯一索引已经存在的情况下会插入失败,而InsertOrUpdate在主键或者唯一索引已经存在的
情况下就变成了根据主键或唯一索引update的操作)。一段时间后发现该表的主键id(已设置为连续自增),
不是连续的自增,总是跳跃的增加,这样就造成id自增过快,已经快超过最大值了,
通过查找资料发现on duplicate key update有一个特性就是每次是更新的情况下id也是会自增加1的,
比如说现在id最大值的5,然后进行了一次更新操作再进行一次插入操作时,id的值就变成了7而不是6。
<insert id="batchInsertOrUpdate" parameterType="java.util.List">
insert into material_inventory (warehouse_code, warehouse_name,
material_code, material_name, material_type_code, material_type_name,
current_stock, in_stock, out_stock, unit,
purchase_unit_price, sale_unit_price, purchase_min_num,
stock_warn_num, status, gift_type,
remark, create_user, create_user_name, update_user, update_user_name)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.warehouseCode,jdbcType=VARCHAR}, #{item.warehouseName,jdbcType=VARCHAR},#{item.materialCode,jdbcType=VARCHAR},
#{item.materialName,jdbcType=VARCHAR}, #{item.materialTypeCode,jdbcType=VARCHAR}, #{item.materialTypeName,jdbcType=VARCHAR},
#{item.currentStock,jdbcType=INTEGER}, #{item.inStock,jdbcType=INTEGER}, #{item.outStock,jdbcType=INTEGER}, #{item.unit,jdbcType=VARCHAR},
#{item.purchaseUnitPrice,jdbcType=DECIMAL}, #{item.saleUnitPrice,jdbcType=DECIMAL}, #{item.purchaseMinNum,jdbcType=INTEGER},
#{item.stockWarnNum,jdbcType=INTEGER}, #{item.status,jdbcType=TINYINT}, #{item.giftType,jdbcType=TINYINT},
#{item.remark,jdbcType=VARCHAR},#{item.createUser,jdbcType=VARCHAR},#{item.createUserName,jdbcType=VARCHAR},
#{item.updateUser,jdbcType=VARCHAR}, #{item.updateUserName,jdbcType=VARCHAR})
</foreach>
ON DUPLICATE KEY UPDATE current_stock = values(current_stock) + current_stock, in_stock = values(in_stock) + in_stock;
</insert>
ON DUPLICATE KEY UPDATE后面的values(current_stock)为参数中的current_stock后面加的current_stock为数据库原值
update后面写了什么参数就只更新什么参数本例只更新current_stock和in_stock
如果插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复,那么就会认为该条记录存在,则执行update语句而不是insert语句,
反之,则执行insert语句而不是更新语句。
用于实时写入数据库中时数据量比较大,每次有10W次的访问次数,有update,也有insert。
ON DUPLICATE KEY UPDATE可以自己去处理是新增还是修改。
但是每次访问无论是新增还是修改都会导致主键增加1需要在mysql配置文件中增加
innodb_autoinc_lock_mode = 0
注意点:
①一个唯一键,或者主键(insert into列中包含该键)
②不能跟where条件
③表中有多个唯一键时可能造成死锁,使用时注意
强调一点:
on duplicate key update后面跟全部更新的字段=值,也就是说insert into填写values()中的值,全部以key=value的形式填写在update后面,否则会出现不更新,或者更新某些字段的情况!!!