在向一个表中插入数据的时候,有一种常见的需求:判断插入的值是否在表中已经存在,如果是则执行update操作,否则执行insert。在Oracle里可以使用merge into来实现,MySQL也对标准SQL进行了扩展来实现此功能。
1. replace into
replace类似于insert,区别在于如果新插入的行的主键或唯一索引值已存在,则先删除相应的行在插入新行。除非表有主键或唯一索引,否则replace不会执行判重,从而等同于insert。如果表中有多个唯一索引,则replace into一行前可能会先删除多行。例子:
- create table t1 ( a int not null primary key);
- replace into t1 values (1);
- select row_count();
- replace into t1 values (1);
- select row_count();
- replace into t1 values (1);
- select row_count();
- select * from t1;
- drop table t1;
- create table t1 ( a int not null unique key, b int not null unique key);
- insert into t1 values (1,2);
- insert into t1 values (2,3);
- select * from t1;
- replace into t1 values (1,3);
- select row_count();
- select * from t1;
2. insert...on duplicate key update
与replace into类似,也是在insert时执行主键或唯一索引判重,如果重复则执行相应的update操作。如果存在多个唯一索引且重复的行多于一行,则只修改一行。例子:
- create table t1 ( a int not null unique key, b int not null unique key, c int not null);
- insert into t1 values (1,1,1);
- insert into t1 values (2,2,1);
- select * from t1;
- insert into t1 values (1,2,3) on duplicate key update c=c+1;
- select row_count();
- select * from t1;
- insert into t1 values (1,2,3) on duplicate key update c=c+1;
- insert into t1 values (1,2,3) on duplicate key update c=c+1;
- select * from t1;
可以使用values函数在update子句里引用新插入的值,这在同时插入多个值时很有用:
- create table t1 ( a int not null unique key, b int not null unique key, c int not null);
- insert into t1 values (1,2,3),(4,5,6) on duplicate key update c=values(a)+values(b);
- select * from t1;
- insert into t1 values (1,2,3),(4,5,6) on duplicate key update c=values(a)+values(b);
- select * from t1;