MySQL 插入更新 ON DUPLICATE KEY UPDATE

本文介绍MySQL中ON DUPLICATE KEY UPDATE的用法,通过示例演示如何在数据已存在时进行更新,否则插入新记录的方法,提高了操作的原子性和数据完整性。

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

INSERT INTO hal(`url`,`time`) VALUES('www.baidu.com',3) ON duplicate KEY UPDATE `url`='www.baidu.com'

此时,time字段须有索引,即,如果有`time`=3,则更新`url`、`time`;没有则插入数据。


转载:

平时我们在设计数据库表的时候总会设计 unique  或者 给表加上 primary key 的限制条件.

此时 插入数据的时候,经常会有这样的情况:我们想向数据库插入一条记录:若数据表中存在以相同主键的记录,我们就更新该条记录。否则就插入一条新的记录。

逻辑上我们需要怎么写:

$result = mysqli_query('select * from xxx where id = 1');
$row = mysqli_fetch_assoc($result);
if($row){
    mysqli_query('update ...');
}else{
    mysqli_query('insert ...');
}

但是这样写有两个问题

1、效率太差,每次执行都要执行2个sql
2、高并发的情况下数据会出问题,不能保证原子性 

还好MySQL 为我们解决了这个问题:我们可以通过 ON DUPLICATE KEY UPDATE  达到以上目的, 且能保证操作的原子性和数据的完整性。

ON DUPLICATE KEY UPDATE 可以达到以下目的:

向数据库中插入一条记录:

若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE 后面的操作。

否则插入一条新的记录。

示例:

Step1 . 创建表,插入测试数据

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `mRowUpdate`;
CREATE TABLE `mRowUpdate` (
    `id` int(11) NOT NULL,
    `value` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='例子';
INSERT INTO `mRowUpdate` VALUES ('1', 'sss'),('2', 'szh'),('3', '9999');

Step2 .测试 ON DUPLICATE KEY UPDATE 的使用方法:

INSERT INTO mRowUpdate(id,`value`) VALUES(3, 'SuperMan') ON DUPLICATE KEY UPDATE `value`='SuperMan';


注意事项:

注意 1:

ON DUPLICATE KEY UPDATE 特别适用于多行插入.

如:INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`);

Tips: VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。

注意 2:

若多个索引都冲突,则只有一条记录被修改。

create table test(
    id int not null primary key,
    num int not null UNIQUE key,
    tid int not null
)

为了测试两个唯一索引都冲突的情况,然后插入下面的数据

insert into test values(1,1,1), (2,2,2);

然后执行:

insert into test values(1,2,3) on duplicate key update tid = tid + 1;
因为a和b都是唯一索引,插入的数据在两条记录上产生了冲突,然而执行后只有第一条记录被修改.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值