MySQL 插入替换语句（replace into statement）

我们日常使用 insert into 语句向表中插入数据时，一定遇到过主键或唯一索引冲突的情况，MySQL的反应是报错并停止执行后续的语句，而replace into语句可以实现强制插入。

一、replace into 语句简介

insert into语句在插入数据时如果遇到主键或唯一键冲突时，其反应是报错并返回。而如果你想强制插入，即遇到键值冲突时使用最新的数据替换原数据，则可以用replace into来代替insert into语句。

1.1 基本用法

先创建一张测试表，并插入2条数据：

create table mytab(
id int not null auto_increment primary key,
source varchar(16),
value int default 999);

insert into mytab values(1, 'insert', 100),(2,'insert', 200);
table mytab;

下面用insert into语句插入2条数据，这里主键id=2出现了冲突，语句会执行失败：

insert into mytab values(2, 'insert', 200), (3, 'insert', 300);
table mytab;

将insert关键字替换为replace，当出现主键或唯一键冲突时，MySQL会先将表中冲突的数据删除，然后再插入，即隐式的帮你做了一个delete操作：

replace into mytab values(2, 'replace', 200), (3, 'replace', 300);
table mytab;

这里插入了2行数据，但是返回结果显示 3 rows affected，即是删除了1行，插入2行，共影响3行数据。同时Duplicates：1，显示有1行数据出现了重复。

1.2 使用set语句

replace into 还可以set语句用来指定每个列的值，这里更新id为1的行：

replace into mytab set id=1, source='set', value=101;
table mytab;

对于set中未指定的列，会被替换为默认值（这里不指定value的值，则其被设置成默认值999）：

replace into mytab set id=1, source='set';
table mytab;

二、注意事项

2.1 替换时必须要有主键或唯一索引

replace into 发生替换时必须要检测到主键或唯一索引冲突，否则它的行为就和普通的insert into相同，我想把id为1的行value值设置为100，但仅引用source列，而通过source列无法判定冲突：

replace into mytab set source='set', value=100;
table mytab;

所以最终语句并没有像我预想的那样发生替换，而是插入了一行新数据。

2.2 替换时无法引用旧行的数据

如果我想在发生冲突时在原数据的基础上进行更新，这种场景replace into语句是无法做到的，因为在插入发生之前旧数据已经被删除了，所以是无法引用旧数据的，这里尝试将id=2的数据value值+1：

replace into mytab set id=2, source='plus 1', value=value+1;
table mytab;

可以看到最终结果并不是预想的201，而是1000。这是因为value=value+1，运算时无法引用旧值，而是引用了默认值。如果想实现冲突时在原数据上更新的效果，可以通过insert into … on duplcate key update语句实现。

最后提一句，replace into语句是MySQL对标准SQL的扩展，在其他RDBMS中可能并不适用。