mysql中使用ON DUPLICATE KEY UPDATE 示例

本文介绍了一种MySQL中利用ON DUPLICATE KEY UPDATE实现的插入或更新操作的方法,通过示例展示了如何在表字段定义为主键或唯一键的情况下,实现数据的无重复插入与更新。

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

对于表字段定义为主键或者唯一键,当使用ON DUPLICATE KEY UPDATE(update后紧跟着可修改的其他字段即可) 可以起到如下的效果:

1.没有则插入.

2.存在则更新.

示例:

a.t_book表建语句

CREATE TABLE `t_book` (
  `id` varchar(36) NOT NULL,
  `book_name` varchar(25) DEFAULT NULL,
  `author` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `book_name` (`book_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

b.第一条插入语句

INSERT INTO t_book(id,book_name,author)VALUES(UUID(),'西游记','张1') ON DUPLICATE KEY UPDATE author='张1';

c.结果分析:

a.执行b的插入语句,表中生成西游记,作者叫张1的一条记录.

b.修改update后的 author=‘张2’,执行语句

INSERT INTO t_book(id,book_name,author)VALUES(UUID(),'西游记','张1') ON DUPLICATE KEY UPDATE author='张2';

由于book_name是唯一键,所以库中的一条记录会变更为西游记,作者叫张2的记录.


<think>嗯,用户想了解MySQL中INSERT INTO ON DUPLICATE KEY UPDATE的具体使用案例和详细说明。首先,我需要回忆一下这个语句的作用。它主要用于插入数据时,如果遇到唯一键冲突,就执行更新操作,而不是报错。这在处理重复数据时非常有用,比如统计计数或者更新状态等场景。 根据用户提供的引用内容,特别是引用[3]中的例子,这个语句可以处理多行插入的情况,并且使用VALUES()函数来引用插入的值。比如当插入多行数据时,如果有重复的唯一键,就会更新对应的字段。例如,当插入(2,5,7)时,如果a字段是唯一索引,原有的(2,2,9)会被更新为(2,5,9),而其他字段如c可能保持不变,除非在UPDATE子句中指定。 接下来,我需要考虑用户可能需要哪些具体的案例。基础用法肯定是插入单行数据,存在冲突时更新某些字段。然后是批量操作,处理多行数据的情况。还有自动增量字段的处理,比如引用[2]提到的LAST_INSERT_ID()函数,在插入或更新后获取自增ID的值。此外,可能还需要解释如何引用原字段的值,比如在更新时使用c=c+1这样的表达式。 另外,需要注意这个语句是MySQL特有的,不是标准SQL,这一点在引用[3]中提到过,用户可能需要了解这一点以避免跨数据库兼容性问题。同时,使用时的注意事项,比如必须存在唯一索引,否则就是单纯的插入,不会触发更新。 用户可能的应用场景包括数据去重、实时统计、状态更新等,比如统计网页访问次数,每次访问时插入或更新计数。这些例子可以帮助用户更好地理解如何在实际项目中应用这个语句。 最后,要确保回答结构清晰,分点说明,包括语法结构、案例、注意事项和应用场景,这样用户能够逐步理解和应用。同时,生成的相关问题需要覆盖常见疑问,比如性能影响、与REPLACE INTO的区别,以及批量操作时的行为等。</think>### MySQL `INSERT INTO ... ON DUPLICATE KEY UPDATE` 使用详解 #### 一、基础语法与作用 此语句用于向表中插入数据时,若发生**唯一键(UNIQUE或PRIMARY KEY)冲突**,则执行更新操作而非报错。其核心语法为: ```sql INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...) ON DUPLICATE KEY UPDATE 字段A=值A, 字段B=值B; ``` #### 二、使用案例 ##### 1. 基础场景:单行插入与更新 假设表 `user_stats` 包含唯一索引字段 `user_id`,若插入重复的 `user_id` 则更新访问次数: ```sql INSERT INTO user_stats (user_id, visit_count) VALUES (1001, 1) ON DUPLICATE KEY UPDATE visit_count = visit_count + 1; ``` - **新插入**:`visit_count=1` - **已存在**:`visit_count` 自增1[^3] ##### 2. 批量操作:多行插入与选择性更新 插入多行数据,冲突时仅更新部分字段: ```sql INSERT INTO products (sku, stock, price) VALUES ('A100', 50, 99.9), ('B200', 30, 199.9) ON DUPLICATE KEY UPDATE stock = VALUES(stock), price = VALUES(price); ``` - 若 `sku` 冲突,则用新插入的 `stock` 和 `price` 覆盖原值[^3] ##### 3. 引用原字段值 在更新时结合原字段值与新值: ```sql INSERT INTO orders (order_id, total_amount) VALUES ('O20230701', 100) ON DUPLICATE KEY UPDATE total_amount = total_amount + VALUES(total_amount); ``` - 若订单已存在,总金额累加 ##### 4. 自增主键处理 若表含 `AUTO_INCREMENT` 主键: ```sql INSERT INTO logs (id, content) VALUES (NULL, 'error log') ON DUPLICATE KEY UPDATE content = 'updated log'; ``` - **插入成功**:返回新生成的自增ID(通过 `LAST_INSERT_ID()`) - **更新成功**:返回原有自增ID[^2] #### 三、注意事项 1. **必须存在唯一约束**:若无唯一索引,仅执行插入操作 2. **字段优先级**:若多个唯一键冲突,仅触发第一个匹配的约束 3. **性能影响**:批量操作时建议按主键顺序插入以减少锁竞争 4. **与 `REPLACE INTO` 区别**:`REPLACE` 会先删除旧记录再插入,可能影响自增ID和触发器 #### 四、典型应用场景 1. **实时统计**:如用户访问计数器 2. **数据同步**:避免重复导入数据 3. **状态更新**:如订单状态覆盖 4. **排行榜维护**:积分累加场景 #### 五、完整示例 表结构: ```sql CREATE TABLE inventory ( item_id INT PRIMARY KEY, item_name VARCHAR(50) UNIQUE, quantity INT ); ``` 操作语句: ```sql INSERT INTO inventory (item_id, item_name, quantity) VALUES (1, 'Phone', 100), (2, 'Laptop', 50) ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity); ``` - 若 `item_id` 或 `item_name` 冲突,库存量相加 - 无冲突则插入新记录 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值