mysql中on duplicate key update用法

1、应用场景

日常开发中,对于一个数据想做到存在即更新,不存在则新增,通常的做法是先查询数据库中是否存在对应的数据,如果存在就使用更新的方法,不存在就使用新增的方法
如果是单个数据,倒也没什么问题,但如果是批量数据的话,会消耗大量的资源来进行查询操作,这样就得不偿失了。
这种情况我们可以使用mysql提供的 on duplicate key update 来进行操作。


2、基础使用语法

2.1  假设此时我们表中没有数据

执行语句(为了展示效果使用用法2)

#用法1:使用values来获取值(推荐,因为插入多个的时候可以用)
INSERT INTO `test` ( id, name )
VALUES( 1, '晓明' ) 
ON DUPLICATE KEY UPDATE
id = VALUES(id),
name = VALUES(name)

#用法2:直接使用值
INSERT INTO `user` ( id, name )
VALUES( 1, '晓明') 
ON DUPLICATE KEY UPDATE 
id = '123',
name = 'xiaoming'

执行结果

 2.2 有数据后再次执行

执行相同sql语句,执行结果

这里就是关键:我们可以看到变动为2,此时就是说明原数据进行了更新,更新内容为下面UPDATE中设置的字段值 ,id也可变化,数据库中数据如下

Q:我们再次执行一次这个语句,结果是什么样子呢?

A:自然是判断出该表中无此数据,新增一条额外的新数据

sql结果

验证得到结论:其实就是会自动检测是否存在Duplicate entry,如果存在values后面的值就会自动更改,不存在则插入 

3、批量插入

执行语句

INSERT INTO test(`id`,`name`,`address`)
VALUES('4','修改10','北京'),
	  ('1', '晓明',1) 
ON DUPLICATE KEY UPDATE
name=VALUES(name),address=VALUES(address);

sql结果

 id为1和id为4的分别被修改和新增,可以同时进行两种操作类型

4、Mybatis中的写法

单独插入

<insert id="insertUser" parameterType="com.test.User">
    INSERT INTO user(
    id,
    name,
    gender,
    birthday,
    address)
    VALUES
    (#{id},
    #{name},
    #{gender},
    #{birthday},
    #{address})
    ON DUPLICATE KEY UPDATE
    id = VALUES(id),
    name = VALUES(name),
    gender = VALUES(gender)
    birthday = VALUES(birthday),
    address = VALUES(address)
</insert>

批量插入

<insert id="insertUser" parameterType="java.util.List">
    INSERT INTO user(
    id,
    name,
    gender,
    birthday,
    address)
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id},
        #{item.name},
        #{item.gender},
        #{item.birthday},
        #{item.address})
    </foreach>
    ON DUPLICATE KEY UPDATE
    id = VALUES(id),
    name = VALUES(name),
    gender = VALUES(gender)
    birthday = VALUES(birthday),
    address = VALUES(address)
</insert>

5、情景模拟

插入失败提示如下

ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

如果数据库中已有某条数据,以下的两条语句可等同:

INSERT INTO tablename (id, data) VALUES (1, 10)
ON DUPLICATE KEY UPDATE data=data+10;
UPDATE tablename SET data=data+10 WHERE id=1;

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值