结论

• on DUPLICATE key UPDATE 唯一键冲突的时候，执行更新
• replace INTO 唯一键冲突的时候，先删后增

实践

1. 数据库版本

select version();

5.7.20-log

1. 假设有这么一张表，auto_increment 默认从 1 开始

CREATE TABLE `user_for_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `corp_id` varchar(64) NOT NULL COMMENT '企业id',
  `user_id` varchar(64) NOT NULL COMMENT '员工id',
  `name` varchar(128) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_corp_user` (`corp_id`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试人员';

2. 查询下一条插入使用的id，这里结果是 1

SELECT auto_increment FROM information_schema.tables 
where table_schema="dev_salary" and table_name="user_for_test";

image.png

3. 插入一条数据，这条数据的id是 1

insert into user_for_test(gmt_create, gmt_modified, corp_id, user_id, name) 
values (now(), now(), '1101', '1101370130', '南岩飞雪');

image.png

4. on DUPLICATE key UPDATE 唯一键冲突的时候，执行更新

insert INTO user_for_test (corp_id, user_id, name) 
VALUES ('1101', '1101370130', "南岩飞雪2") 
on DUPLICATE key UPDATE name = "南岩飞雪2"

Affected rows: 2, Time: 0.015000s
当前记录的id还是 1，但是下一条插入使用的id变成了3，2 被这次insert INTO 用掉了

image.png
image.png

5. replace INTO 唯一键冲突的时候，先删后增

replace into user_for_test (corp_id, user_id, name) 
VALUES ('1101', '1101370130', "南岩飞雪3")

Affected rows: 2, Time: 0.013000s
当前记录的id变成了 3，下一条插入使用的id变成了4，3 被这次replace into 用掉了

image.png
image.png

6. 额外发现，update 没有修改原数据的话，Affected rows 是 0，但是之前看到文章验证也是更新的，原来是有参数控制的，看下文参考

update user set name = "南岩飞雪3" 
where corp_id = '1101' and user_id = '1101370130';

Affected rows: 0, Time: 0.022000s

参考

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

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