Mysql的 replace into 就是说在对表进行insert的操作的时候,如果表中存在此数据,那么执行update操作(如果设置的是主键为自动增长那么会增加),反之执行insert操作,建表语句如下:
create table test(
id int,
name varchar(22)
);
create table test_id(
id int primary key,
name varchar(22)
);
然后我们执行如下操作:
replace into test set id=2,name='abcd';
replace into test set id=2,name='abcd';
replace into test set id=2,name='abcd';
replace into test_id set id=2,name='abcd';
replace into test_id set id=2,name='abcd';
replace into test_id set id=2,name='abcd';
然后查询看数据:
select count(1) from test ;
+----------+
| count(1) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
select count(1) from test _id;
+----------+
| count(1) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
判断数据是否相同的标准就是根据唯一索引来判断,那么在oracle中有没有替代品?
merge into
merge into
test_id a
using
(select id , name from test_id ) b
on (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.name = b.name
WHEN NOT MATCHED THEN
INSERT
(a.id, a.name)
VALUES
(b.id, b.name);