SQL标准UPSERT呼叫

Question

我在寻找标准的SQL“ UPSERT”语句。如果存在，则要求一次插入和更新。

我正在寻找一个工作，高效和跨平台的电话。

我见过MERGE，UPSERT，REPLACE，INSERT .. ON DUPLICATE UPDATE但没有声明满足需求。

顺便说一句，我使用MYSQL和HSQLDB作为unitest。我知道HSQLDB是有限的，可能无法满足我的需求，但是即使没有它，我也找不到标准的方法。声明只有MYSQL和HSQLDB现在也足够了。

我已经逛了一会儿了，找不到答案。

我的桌子：

CREATE TABLE MY_TABLE (
  MY_KEY varchar(50) NOT NULL ,
  MY_VALUE varchar(50) DEFAULT NULL,
  TIME_STAMP bigint NOT NULL,
  PRIMARY KEY (MY_KEY)
);

任何的想法？

Answer 1

MySQL和HSQLDB均支持的唯一解决方案是查询要替换的行，并有条件地查询INSERT或UPDATE。这意味着您必须编写更多的应用程序代码来补偿RDBMS实现之间的差异。

1. 开始交易。
2. 选择…进行更新。
3. 如果SELECT找到行，则进行UPDATE。
4. 否则，请插入。
5. 犯罪。

MySQL不支持ANSI SQL MERGE语句。它支持REPLACE和INSERT … ON DUPLICATE KEY
UPDATE。有关更多信息，请参见我对“ INSERT IGNORE”和“ INSERT … ON DUPLICATE KEY
UPDATE”的
回答。

关于评论：是的，另一种方法是只尝试INSERT并查看它是否成功。否则，请执行UPDATE。如果您尝试执行INSERT并按下重复键，则会生成错误，该错误在某些客户端接口中变成异常。在MySQL中执行此操作的缺点是，即使INSERT失败，它也会生成一个新的自动增量ID。因此，您最终会遇到空白。我知道通常不需要担心自动增量序列中的间隙，但是去年我帮助了一位成功插入之间的间隙为1000-1500的客户，由于这种影响，结果是他们用尽了一个INT在其主键中。

正如@baraky所说，可以先尝试执行UPDATE，如果影响到零行，则改为执行INSERT。我对此策略的评论是，更新零行不是一个例外-
您必须在UPDATE之后检查“受影响的行数”，才能知道它是否“成功”。

但是查询受影响的行数会使您回到原来的问题：您必须在MySQL和HSQLDB中使用不同的查询。

HSQLDB：

CALL DIAGNOSTICS(ROW_COUNT);

MySQL：

SELECT ROW_COUNT();