mysql - SQL 标准 UPSERT 调用

Question

我正在寻找标准的 SQL“UPSERT”语句。如果存在，则插入和更新一个调用。

我正在寻找一个工作、高效和跨平台的调用。

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

顺便说一句，我使用 MYSQL 和 HSQLDB 进行单元测试。我知道 HSQLDB 是有限的，可能无法满足我的需要，但即使没有它我也找不到标准方法。声明目前只有 MYSQL 和 HSQLDB 也足够了。

我已经四处寻找了一段时间，没有得到答案。

我的 table :

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 都支持的唯一解决方案是查询您打算替换的行，并有条件地插入或更新。这意味着您必须编写更多的应用程序代码来弥补 RDBMS 实现之间的差异。

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

MySQL 不支持 ANSI SQL MERGE 语句。它支持 REPLACE 和 INSERT...ON DUPLICATE KEY UPDATE。请参阅我对 "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" 的回答有关更多信息。

---

回复评论:是的，另一种方法是只尝试 INSERT 并查看它是否成功。否则，进行更新。如果您尝试 INSERT 并且它命中了一个重复的键，它会产生一个错误，这在某些客户端界面中会变成一个异常。在 MySQL 中这样做的缺点是即使 INSERT 失败，它也会生成一个新的自增 ID。所以你最终会遇到差距。我知道自动递增序列中的间隙通常不需要担心，但我去年帮助过一位客户，由于这种影响，成功插入之间有 1000-1500 的间隙，结果是他们用尽了一个范围INT 在他们的主键中。

正如@baraky 所说，可以改为首先尝试更新，如果这影响了零行，则改为执行插入。我对此策略的评论是更新零行也不异常(exception)——您必须在更新后检查“受影响的行数”才能知道它是否“成功”。

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

数据库:

CALL DIAGNOSTICS(ROW_COUNT);

MySQL:

SELECT ROW_COUNT();