需要便携式SQL Upsert(插入+更新)解决方案 [英] Portable SQL upsert (insert+update) solution needed

问题描述

我需要对数据库执行一个非常简单的操作-如果行存在，则通过键将一些值放入表中-更新它，如果不存在-创建一个新值.问题是我需要以一种可移植的方式进行操作-即同一查询必须在MySQL，SQL Server，Oracle上运行，最好在DB2，Postgres等上运行.因此，尽管我可以使用 REPLACE 或在MySQL中 INSERT ... ON DUPLICATE KEY UPDATE 上，我不确定它们是否支持这种语法.而且，我真的想避免数据库类型的if，因为这是无法维护的.我也不想在更新之前实际查询该值，因为我怀疑它会大大减慢该过程(我需要多次执行).

I need to perform a very simple operation on the database - put some values into the table by key, if the row exists - update it, if not - create a new one. The problem is that I need to do it in a portable way - i.e. the same query must work on MySQL, SQL Server, Oracle and preferably also DB2, Postgres, etc. So while I could use REPLACE or INSERT ... ON DUPLICATE KEY UPDATE in MySQL, I'm not sure that these would support such syntax. And I really want to avoid if's by database type, because this would be unmaintainable. I also don't want to actually query the value before updating, because I suspect it would slow down the process significantly (I need to do it multiple times).

到目前为止，我想出的最好的方法就是:

So far the best I came up with is just doing:

  UPDATE table SET data='data' WHERE key='key';
  INSERT INTO table(key, data) VALUES ('key', 'data');

其中之一总是会成功，而另一个则会失败，但是我不在乎其中一个查询会失败.虽然看起来有点不雅致.有什么建议可以使它更好吗?

one of those would always succeed and another fail, but I don't care that one of the queries fails. It looks kind of inelegant though. Any suggestions how to make it better?

推荐答案

使用某种现代数据库的唯一完全不依赖数据库的解决方案是在两个操作中调用Update，然后调用Insert.一些数据库不允许在一次操作中发送多个语句，而某些数据库可能不返回受Update影响的行数，因此我将不依赖于此.

The only completely database-agnostic solution, using somewhat modern databases, is to call the Update then the Insert in two operations. Some databases do not allow for multiple statements sent in a single operation and some databases might not return the number of rows affected by the Update so I would not rely upon that.

Update MyTable
Set Data = 'Data'
Where KeyCol = 'key';

(单独通话)

Insert Into MyTable(KeyCol, Data)
Select 'key', 'Data'
From ( Select 1 As Value ) As Z
Where Not Exists    (
                    Select 1
                    From MyTable As T1
                    Where T1.KeyCol = 'key'
                    );

这篇关于需要便携式SQL Upsert(插入+更新)解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！