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