on duplicate key update简单使用

MySQL的ON DUPLICATE KEY UPDATE语句用于在导入数据时,数据不存在则插入,已存在则更新。该特性简化了开发,提高了效率,但作为非SQL92标准语法,可能造成数据迁移问题。在并发和大量数据场景下需谨慎使用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在实际应用中,经常碰到导入数据的功能,当导入的数据不存在时则进行添加,有修改时则进行更新,
在刚碰到的时候,第一反应是将其实现分为两块,分别是判断增加,判断更新,后来发现在mysql中有
ON DUPLICATE KEY UPDATE一步就可以完成,感觉实在是太方便了。
但是ON DUPLICATE KEY UPDATE为Mysql特有语法,这是个坑
语句的作用,当insert已经存在的记录时,执行Update

在mybatis中进行批量增加或修改的sql为:

<insert id="insertBatch" parameterType="java.util.List">
    insert into tabalname(
    doctor_id,
    total_given_num,
    used_num,
    avilable_sms_num,
    delete_flag,
    created_id,
    created_time,
    modified_id,
    modified_time
    )
    values
    <foreach collection="list" item="item" index="index" separator=",">
      (
      #{item.doctorId},
      #{item.totalGivenNum},
      #{item.usedNum},
      #{item.avilableSmsNum},
      1,
      #{item.createdId},
      now(),
      #{item.modifiedId},
      now()
      )
    </foreach>
    on DUPLICATE KEY
    update
    total_given_num = total_given_num + values(total_given_num),
    used_num = used_num + values(used_num),
    avilable_sms_num = avilable_sms_num + values(avilable_sms_num),
    modified_time = now()
</insert>

数据表DDL如下:

CREATE TABLE `tabalname` (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`doctor_id` int(11) DEFAULT '0' COMMENT '',
	`total_given_num` int(11) DEFAULT '0' COMMENT '',
	`used_num` int(11) DEFAULT '0' COMMENT '',
	`avilable_sms_num` int(11) DEFAULT '0' COMMENT '',
	`watched_revision_page` tinyint(1) DEFAULT '0' COMMENT '',
	`delete_flag` int(11) NOT NULL COMMENT '',
	`created_id` int(11) NOT NULL COMMENT '',
	`created_time` datetime NOT NULL COMMENT '',
	`modified_id` int(11) NOT NULL COMMENT '',
	`modified_time` datetime NOT NULL COMMENT '',
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_doctor_send_doctor_id` (`doctor_id`) USING BTREE,
	KEY `index_doctor_send_doctor_id` (`doctor_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1843313 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='xxx';

优点:

  1. 开发简单,对已有表批量插入新数据时尤其方便(特别是做表数据同步脚本,真香!)
  2. 可以减少网络连接开销(减少了数据查询、操作次数),在一定量的数据操作时,效率上也提高。

缺点:

  1. MySQL私有语法,非SQL92标准语法,当迁移数据时会造成麻烦,需要改写代码。例如MySQL迁移PgSQL。
  2. 当环境复杂时,数据量大的情况下,会出现意想不到的问题。(数据量大、产生并发,建议还是用原子操作)
  3. 业务逻辑分散在应用逻辑层和数据层,会对项目维护留下隐患。

建议:

  1. 不对存在多个唯一键的数据表使用此语句。
  2. 在有可能有并发事务执行的insert 语句情况下不使用该语句。
  3. 使用此语句要考虑以后是否会做数据迁移,数据量是否大,考虑后再使用!!!
在 MySQL 中,使用 `ON DUPLICATE KEY UPDATE` 可以实现在插入数据时,如果遇到主键或唯一索引冲突,则自动执行更新操作。当需要更新多个字段时,可以在 `UPDATE` 子句中列出多个字段的赋值语句,每个字段之间使用逗号分隔。 ### 基本语法 ```sql INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...) ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...; ``` ### 示例:更新多个字段 假设有一个名为 `users` 的表,定义如下: ```sql CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE, email VARCHAR(100), age INT ); ``` 当插入一条记录时,如果 `username` 已经存在,则更新 `email` 和 `age` 字段: ```sql INSERT INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 30) ON DUPLICATE KEY UPDATE email = 'john@example.com', age = 30; ``` 如果 `username = 'john_doe'` 的记录已经存在,则该语句会更新该记录的 `email` 和 `age` 字段;如果不存在,则插入新记录。 ### 使用 `VALUES()` 函数 在批量插入时,可以使用 `VALUES(column)` 函数来引用插入时的值,从而简化更新语句: ```sql INSERT INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 30), ('jane_doe', 'jane@example.com', 25) ON DUPLICATE KEY UPDATE email = VALUES(email), age = VALUES(age); ``` 在这个例子中,如果 `username` 冲突,`ON DUPLICATE KEY UPDATE` 子句会使用当前插入的值来更新对应的 `email` 和 `age` 字段[^1]。 ### 注意事项 - `ON DUPLICATE KEY UPDATE` 需要表中存在主键或唯一索引,否则不会触发更新操作[^2]。 - 如果更新的字段包含主键或唯一索引列,并且更新后的值与其他记录冲突,会导致插入和更新都失败。 - 使用 `ON DUPLICATE KEY UPDATE` 时,自增字段(`AUTO_INCREMENT`)可能会跳过某些值,因为每次插入操作都会尝试分配一个新的自增值,即使最终执行的是更新操作[^3]。 ### 总结 通过 `ON DUPLICATE KEY UPDATE`,可以非常方便地实现“存在则更新,不存在则插入”的逻辑。在更新多个字段时,只需在 `UPDATE` 子句中列出多个字段的赋值即可,并且可以结合 `VALUES()` 函数进行动态赋值。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值