merge into和on duplicate key

经常遇到这样的需求，存在这条纪录则更新，不存在则插入。

1.Oracle中使用merge into函数.

MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

例如：

 merge into test t
 using (select 1 as id from dual) d
 on (t.id = d.id)
 when matched then
   update set t.name = 'modify',t.work = 'mwork'
 when not matched then
   insert (id, name, work) values (d.id, 'insert', 'iwork')

2.Mysql中on duplicate key，Mysql根据主键或者唯一索引判断是否存在这条纪录，如果没有的话，会继续插入进去而不会更改

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

       [INTO] tbl_name [(col_name,...)]

       VALUES ({expr | DEFAULT},...),(...),...

       [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

例如：

INSERT INTO test (id, NAME, WORK)
VALUES
	(1, 'insert', 'iwork') 
ON DUPLICATE KEY 
UPDATE 
  NAME = 'modify',
  WORK = 'mwork'