SQL标准UPSERT呼叫 [英] SQL standard UPSERT call

问题描述

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

I'm looking for a standard SQL "UPSERT" statement. A one call for insert and update if exists.

我正在寻找一个有效，跨平台的呼叫.

I'm looking for a working, efficient and cross platform call.

我见过MERGE，UPSERT，REPLACE，INSERT .. ON DUPLICATE UPDATE，但是没有满足要求的语句.

I've seen MERGE, UPSERT, REPLACE, INSERT .. ON DUPLICATE UPDATE but no statement meets the needs.

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

BTW I use MYSQL and HSQLDB for unitests. I understand that HSQLDB is limited and may not cover what I need, but I couldn't find a standard way even without it. A statement that only MYSQL and HSQLDB will also be enough for now.

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

I've been looking around for a while and couldn't get an answer.

我的桌子:

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)
);

有什么主意吗?

推荐答案

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

The only solution that is supported by both MySQL and HSQLDB is to query the rows you intend to replace, and conditionally either INSERT or UPDATE. This means you have to write more application code to compensate for the differences between RDBMS implementations.

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

MySQL不支持ANSI SQL MERGE语句.它支持REPLACE和INSERT ... ON DUPLICATE KEY UPDATE.请参阅我对"INSERT IGNORE"的回答.与"INSERT ... ON DUPLICATE KEY UPDATE"相对应的.

MySQL doesn't support the ANSI SQL MERGE statement. It supports REPLACE and INSERT...ON DUPLICATE KEY UPDATE. See my answer to "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE" for more on that.

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

Re comments: Yes, another approach is to just try the INSERT and see if it succeeds. Otherwise, do an UPDATE. If you attempt the INSERT and it hits a duplicate key, it'll generate an error, which turns into an exception in some client interfaces. The disadvantage of doing this in MySQL is that it generates a new auto-increment ID even if the INSERT fails. So you end up with gaps. I know gaps in auto-increment sequence are not ordinarily something to worry about, but I helped a customer last year who had gaps of 1000-1500 in between successful inserts because of this effect, and the result was that they exhausted the range of an INT in their primary key.

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

As @baraky says, one could instead attempt the UPDATE first, and if that affects zero rows, then do the INSERT instead. My comment on this strategy is that UPDATEing zero rows is not an exception -- you'll have to check for "number of rows affected" after the UPDATE to know whether it "succeeded" or not.

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

But querying the number of rows affected returns you to the original problem: you have to use different queries in MySQL versus HSQLDB.

HSQLDB:

CALL DIAGNOSTICS(ROW_COUNT);

MySQL:

SELECT ROW_COUNT();

这篇关于SQL标准UPSERT呼叫的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！