【MYSQL】replace into 和INSERT INTO … ON DPLICATE KEY

Dear 丶 2022-05-29 00:55 255阅读 0赞

replace into 可能更适合某些场景的实现，但是在生产环境，最好使用INSERT INTO … ON DPLICATE KEY，可以减少风险。

CREATE TABLE `test_table` ( `id` bigint(15) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID', `stub` varchar(32) NOT NULL, `addTime` datetime NOT NULL COMMENT '增加时间', `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `stub` (`stub`), KEY `IX_addtime` (`addTime`), KEY `IX_updatetime` (`updateTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test信息'

初始化数据

insert into test_table (stub, addTime,updateTime) values('a', now(), now());
    
    insert into test_table (stub, addTime,updateTime) values('b', now(), now());
    
    insert into test_table (stub, addTime,updateTime) values('c', now(), now());

使用replace into

replace into test_table (stub, addTime,updateTime) values('c', now(), now());

10:01:04 replace into test\_table (stub, addTime,updateTime) values(‘c’, now(), now()) 2 row(s) affected 0.0077 sec

可以看到，影响了2条记录。再看看数据

select * from test_table ;

id, stub, addTime, updateTime  
‘1’, ‘a’, ‘2018-03-14 09:59:52’, ‘2018-03-14 09:59:52’  
‘2’, ‘b’, ‘2018-03-14 10:00:02’, ‘2018-03-14 10:00:02’  
‘4’, ‘c’, ‘2018-03-14 10:01:04’, ‘2018-03-14 10:01:04’

如果有重复key（PRIMARY KEY or UNIQUE index）  
replace into = delete + insert  
如果没有重复key  
replace into = insert

在生产环境，最好使用INSERT INTO … ON DPLICATE KEY

insert into test_table (stub, addTime,updateTime) values('c', now(), now()) on duplicate key update addTime = now() ,updateTime = now();

10:39:54 insert into test\_table (stub, addTime,updateTime) values(‘c’, now(), now()) on duplicate key update addTime = now() ,updateTime = now() 2 row(s) affected 0.0062 sec

结果:

select * from test_table ;

id, stub, addTime, updateTime  
1, a, 2018-03-14 09:59:52, 2018-03-14 09:59:52  
2, b, 2018-03-14 10:00:02, 2018-03-14 10:00:02  
4, c, 2018-03-14 10:39:54, 2018-03-14 10:39:54

你可能注意到刚才INSERT INTO … ON DPLICATE KEY影响了2行。为什么会这样呢？  
[mysql官网][mysql]介绍了。  
1.如果插入的新行，影响是1 row；  
2.如果存在的行做了update，则影响2 rows；  
3.如果存在的行set的是原来的值，则影响0 row（如果设置了CLIENT\_FOUND\_ROWS,则影响1 row）。

--------------------

生产环境最好用INSERT INTO … ON DPLICATE KEY  
[https://stackoverflow.com/questions/19820724/replace-into-query-syntax][https_stackoverflow.com_questions_19820724_replace-into-query-syntax]

mysql官网介绍  
[https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html][mysql]

[mysql]: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
[https_stackoverflow.com_questions_19820724_replace-into-query-syntax]: https://stackoverflow.com/questions/19820724/replace-into-query-syntax