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

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官网介绍了。
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

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