mysql on duplicate key 新增和更新的使用
mybatis 写法
新增或修改更新一条数据
insert into nft_nftgoal_user_goods (user_id,good_id,number)
values(#{po.userId},#{po.goodId},#{po.number})
on duplicate key
update user_id=#{po.userId},good_id=#{po.goodId},number=number+#{po.number}
新增或修改更新带有条件的一条数据
insert into nft_nftgoal_auxiliary_user_physical_power (card_id,physical_power_value)
values (#{cardId},100-#{reducePower})
on duplicate key
update card_id=#{cardId},physical_power_value=
case when physical_power_value-#{reducePower} <![CDATA[ <= ]]> 1 then 1
else physical_power_value-#{reducePower} end
新增或修改更新带有条件的多条数据,请注意 update = VALUES,多条语句的话,必须这样写
insert into nft_nftgoal_auxiliary_user_physical_power (card_id,physical_power_value)
values (#{cardId},100-#{reducePower}),(#{cardId},100-#{reducePower}),(#{cardId},100-#{reducePower}),(#{cardId},100-#{reducePower})
on duplicate key
update card_id=VALUES(card_id),physical_power_value=
case when physical_power_value-#{reducePower} <![CDATA[ <= ]]> 1 then 1
else physical_power_value-#{reducePower} end
更新带有条件的数据
update nft_nftgoal_auxiliary_user_physical_power
set physical_power_value =
case when physical_power_value + #{addPower} <![CDATA[ >= ]]> 100 then 100 else physical_power_value + #{addPower} end
where physical_power_value <![CDATA[ < ]]> 100 and update_time <![CDATA[ < ]]> DATE_ADD(now(),INTERVAL -1 HOUR)
警告:on duplicate key update 需要存在主键或者唯一键,但这种写法会出现死锁问题,请注意
若要避免死锁,则需要在java代码中进行控制
这种写法的好处就是减少数据库的交互,不用去查询数据是否存在