各数据库insert or update

各数据库insert or update

最进使用beego时需要使用insertorupdate功能，但是beego1.6中并没有实现。顾想自己实现一个，查了下各数据库insetorupdate语句，总结在这里。

- mysql:

• INSERT INTO tablename () VALUES () ON DUPLICATE KEY UPDATE ...
• 列如:INSERT INTO tablename (id,name) VALUES (1,'ooo') ON DUPLICATE KEY UPDATE id=1,name='qqqq'

- posetgres:

• INSERT INTO tablename () VALUES () ON CONFLIT (冲突列名) KEY UPDATE SET ...
  -列如:INSERT INTO tablename (id,name) VALUES (1,'ooo') ON CONFLIT (id) UPDATE SET id=1,name='qqqq'

- ORACLE:

• 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 T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);

oracle merge into为全表扫描查询 on中的条件然后进行相关操作，使用时应慎重。