MySQL的ON DUPLICATE KEY UPDATE用法

 在MySQL数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的惟一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作。

        比如有这样一张表:

create table daily_hit_counter (

day date not null,

slot tinyint unsigned not null,

cnt int unsigned not null,

primary key(day, slot)

) engine = InnoDB;

daily_hit_counter 表的惟一索引是由 day+slot 两个字段组成。

现有数据:

day    slot    cnt
2017-11-19    1    10
2017-11-20    2    10

1. 执行:

insert into daily_hit_counter (day, slot, cnt) values ('2017-11-19', 1, 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

由于插入的数据中 day+slot 与现有的记录的唯一索引重复了,所以执行旧行的更新,执行之后,最终结果是这样的:

day    slot    cnt
2017-11-19    1    11
2017-11-20    2    10

2. 执行:

insert into daily_hit_counter (day, slot, cnt) values ('2017-11-19', 2, 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

由于插入的数据中 day+slot 与现有的记录的唯一索引或者主键都没有重复了,所以执行新纪录插入,执行之后,最终结果是这样的:

day    slot    cnt
2017-11-19    1    11
2017-11-19    2    1
2017-11-20    2    10

--------------------- 
作者:plg17 
来源:优快云 
原文:https://blog.youkuaiyun.com/plg17/article/details/78583692 
版权声明:本文为博主原创文章,转载请附上博文链接!

 

 

VALUES修改
那么问题又来了,有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值,如果我想要分别给不同的记录插入不同的值怎么办呢?

INSERT INTO user_admin_t (_id,password) 
VALUES 
('1','多条插入1') ,
('UpId','多条插入2')
ON DUPLICATE KEY UPDATE 
password =  VALUES(password);
--------------------- 
作者:TransientBa 
来源:优快云 
原文:https://blog.youkuaiyun.com/zyb2017/article/details/78449910 
版权声明:本文为博主原创文章,转载请附上博文链接!

注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!

### SQL Server 中实现类似 MySQL `ON DUPLICATE KEY UPDATE` 的方法 在 SQL Server 中,可以使用 `MERGE` 语句来实现类似于 MySQL 的 `ON DUPLICATE KEY UPDATE` 功能。`MERGE` 语句允许在一个原子操作中同时执行插入、更新或删除操作,具体取决于目标表和源数据之间的匹配情况。 以下是一个基于 SQL Server 的 `MERGE` 语句示例,展示如何实现类似功能: ```sql -- 创建一个示例表 CREATE TABLE TargetTable ( ID INT PRIMARY KEY, Name NVARCHAR(50), UpdateTime DATETIME ); -- 插入初始数据 INSERT INTO TargetTable (ID, Name, UpdateTime) VALUES (1, '张三', GETDATE()); -- 使用 MERGE 实现类似 ON DUPLICATE KEY UPDATE 的功能 MERGE INTO TargetTable AS Target USING (SELECT 1 AS ID, '李四' AS Name, GETDATE() AS UpdateTime) AS Source ON Target.ID = Source.ID WHEN MATCHED THEN UPDATE SET Target.Name = Source.Name, Target.UpdateTime = Source.UpdateTime WHEN NOT MATCHED THEN INSERT (ID, Name, UpdateTime) VALUES (Source.ID, Source.Name, Source.UpdateTime); ``` #### 解释 - `MERGE INTO TargetTable AS Target` 指定目标表为 `TargetTable`。 - `USING (...) AS Source` 定义源数据集,这里是一个内联查询结果。 - `ON Target.ID = Source.ID` 定义匹配条件,类似于 MySQL 中的主键或唯一键判断[^4]。 - `WHEN MATCHED THEN UPDATE ...` 表示当目标表中存在匹配记录时,执行更新操作。 - `WHEN NOT MATCHED THEN INSERT ...` 表示当目标表中存在匹配记录时,执行插入操作。 这种语法与 MySQL 的 `ON DUPLICATE KEY UPDATE` 功能非常相似,但更灵活,支持更多复杂的场景[^5]。 --- ### 示例代码扩展 如果需要更新多个字段或处理复杂逻辑,可以扩展 `MERGE` 语句。例如: ```sql MERGE INTO TargetTable AS Target USING (SELECT 2 AS ID, '王五' AS Name, GETDATE() AS UpdateTime) AS Source ON Target.ID = Source.ID WHEN MATCHED THEN UPDATE SET Target.Name = CASE WHEN Target.Name <> Source.Name THEN Source.Name ELSE Target.Name END, Target.UpdateTime = GETDATE() WHEN NOT MATCHED THEN INSERT (ID, Name, UpdateTime) VALUES (Source.ID, Source.Name, Source.UpdateTime); ``` 上述代码中,`CASE` 语句用于确保仅在必要时更新某些字段[^3]。 --- ### 注意事项 - 在 SQL Server 中,`MERGE` 语句要求目标表和源数据集之间有明确的匹配条件。 - 如果需要根据多个唯一键进行判断,可以在 `ON` 子句中添加更多条件。 - 确保目标表的主键或唯一约束定义正确,否则可能导致意外行为[^4]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值