INSERT ON DUPLICATE KEY UPDATE 用法

插入数据的时候 ，经常会有这样的情况：

我们想向数据库插入一条记录：

  若数据表中存在以相同主键的记录，我们就更新该条记录。

  否则就插入一条新的记录。

 

逻辑上我们需要怎么写：

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

 

但是这样写有两个问题

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

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

 

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

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

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

 

1.表结构

CREATE TABLE IF NOT EXISTS `mysql_realtime_log_pageview_spu_index`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `act_name` VARCHAR(20) NOT NULL,
   `spu_id` VARCHAR(30) NOT NULL,
   `uv`  BIGINT NOT NULL,
   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
   PRIMARY KEY ( `id` ),
   unique key idx_act_name_spu_id (act_name,spu_id),
   KEY idx_act_name(act_name),
   KEY idx_spu_id(spu_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

#插入测试数据
insert into mysql_realtime_log_pageview_spu_index(act_name,spu_id,uv) values('beijing','001',1),('shanghai','002',2);

2.更新

INSERT INTO `mysql_realtime_log_pageview_spu_index`(`act_name`, `spu_id`, `uv`) 
VALUES ('shanghai', '002', 20) ON DUPLICATE KEY UPDATE `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`);

3.查看

select * from mysql_realtime_log_pageview_spu_index;
+----+----------+--------+----+---------------------+
| id | act_name | spu_id | uv | update_time         |
+----+----------+--------+----+---------------------+
|  1 | beijing  | 001    |  1 | 2020-11-27 11:11:56 |
|  2 | shanghai | 002    | 20 | 2020-11-27 11:13:01 |
+----+----------+--------+----+---------------------+
2 rows in set (0.00 sec)

4.插入不存在的行

INSERT INTO `mysql_realtime_log_pageview_spu_index`(`act_name`, `spu_id`, `uv`)  VALUES ('guangzhou', '003', 3) ON DUPLICATE KEY UPDATE `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`);      

select * from mysql_realtime_log_pageview_spu_index;
+----+-----------+--------+----+---------------------+
| id | act_name  | spu_id | uv | update_time         |
+----+-----------+--------+----+---------------------+
|  1 | beijing   | 001    |  1 | 2020-11-27 11:11:56 |
|  2 | shanghai  | 002    | 20 | 2020-11-27 11:13:01 |
|  4 | guangzhou | 003    |  3 | 2020-11-27 11:14:44 |
+----+-----------+--------+----+---------------------+
3 rows in set (0.00 sec)