这里一个非常常见的问题是如何执行upsert,MySQL称之为insert...ON DUPLICATE update
,标准支持将其作为merge
操作的一部分。
鉴于PostgreSQL不直接支持它(在PG9.5之前),您如何做到这一点?考虑以下几点:
CREATE TABLE testtable (
id integer PRIMARY KEY,
somedata text NOT NULL
);
INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');
现在假设您要“upsert”元组(2,'Joe')
,(3,'Alan')
,那么新的表内容将是:
(1, 'fred'),
(2, 'Joe'), -- Changed value of existing tuple
(3, 'Alan') -- Added new tuple
这就是人们在讨论upsert
时所谈论的。至关重要的是,在同一表上存在多个事务的情况下,任何方法都必须是安全的--要么使用显式锁定,要么以其他方式防止产生的竞争条件。
在Insert中,关于PostgreSQL中的重复更新?详细讨论了这个主题,但这是关于MySQL语法的替代方法,随着时间的推移,它增加了一些无关的细节。我在寻找明确的答案。
这些技术对于“如果不存在就插入,否则什么都不做”也很有用,即“插入...对重复键忽略”。
PostgreSQL 9.5和更新版本支持insert...在冲突(key)上做更新
(和在冲突(key)上做任何事情
),即upsert。
与重复密钥更新的比较
。
快速解释。
PostgreSQL没有任何内置的upsert
(或merge
)功能,并且在并发使用时很难高效地执行。
本文详细讨论了这个问题。
通常,您必须在以下两个选项中进行选择:
许多尝试解决此问题的方案都没有考虑回滚,因此导致更新不完整。两个交易相互竞争;其中一个成功地插入
;另一个则获得重复键错误,并执行更新
。update
块等待insert
回滚或提交。当它回滚时,update
条件重新检查匹配零行,因此即使update
提交,它实际上也没有完成预期的upsert。您必须检查结果行计数,并在必要的地方重新尝试。
一些尝试的解决方案也没有考虑选择的种族。如果你尝试简单明了的:
-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.
BEGIN;
UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;
-- Remember, this is WRONG. Do NOT COPY IT.
INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);
COMMIT;
然后,当两个同时运行时,有几种故障模式。一个是已经讨论过的与更新重新检查有关的问题。另一种情况是同时更新
,匹配零行并继续。然后它们都执行exists
测试,该测试发生在insert
之前。两者都获得零行,因此都执行insert
。一个失败,出现重复的密钥错误。
有时您希望进行批量更新,其中您有一个新的数据集,您希望将其合并到旧的现有数据集中。这比单独的行upsert效率高得多,在实际情况下应该优先使用。
在这种情况下,您通常遵循以下过程:
>
创建
临时表
提交
,释放锁。
例如,对于问题中给出的示例,使用多值insert
填充临时表:
BEGIN;
CREATE TEMPORARY TABLE newvals(id integer, somedata text);
INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');
LOCK TABLE testtable IN EXCLUSIVE MODE;
UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;
INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;
COMMIT;
SQL-standardmerge
实际上定义的并发语义很差,不适合在不首先锁定表的情况下进行upsert。
对于数据合并来说,这是一个非常有用的OLAP语句,但对于并发安全的upsert来说,它实际上并不是一个有用的解决方案。对于使用其他DBMSE的人来说,有很多建议可以将merge
用于upserts,但这实际上是错误的。
在MySQL中插入...重复键更新
从MS SQL Server合并
(但请参见上面关于合并
问题)从Oracle合并
(但请参阅上面关于合并
问题)这里一个非常常见的问题是如何执行upsert,MySQL称之为,标准支持将其作为操作的一部分。 鉴于PostgreSQL不直接支持它(在PG9.5之前),您如何做到这一点?考虑以下几点: 现在假设您要“upsert”元组,,那么新的表内容将是: 在Insert中,关于PostgreSQL中的重复更新?详细讨论了这个主题,但这是关于MySQL语法的替代方法,随着时间的推移,它增加了一些无关的细节。我
问题内容: 几个月前,我从关于Stack Overflow的答案中学到了如何使用以下语法在MySQL中一次执行多个更新: 我现在已经切换到PostgreSQL,显然这是不正确的。它指的是所有正确的表,因此我认为这是使用不同关键字的问题,但是我不确定在PostgreSQL文档的哪个地方覆盖了这个问题。 为了澄清,我想插入几件事,如果它们已经存在,请对其进行更新。 问题答案: 自9.5版起的Postg
问题内容: 我正在尝试使用SQLAlchemy模块(而不是SQL!)在python中编写大量upsert。 我在SQLAlchemy添加上遇到以下错误: 我有一个称为列的主键的表。 在此示例中,我已经在数据库中使用了一行。当我尝试将新对象设置为时,出现上述错误。我的印象是,如果主键已经存在,记录将得到更新。 我如何仅基于主键就可以对Flask-SQLAlchemy进行增补? 有没有简单的解决方案?
问题内容: 我正在执行插入查询,其中如果已经存在唯一键,则许多列中的大多数都需要更新为新值。它是这样的: 我不确定该子句的语法应该是什么。如何从子句引用当前行? 问题答案: MySQL将假定等号之前的部分引用INSERT INTO子句中命名的列,而第二部分引用SELECT列。
问题内容: MySQL有这样的东西: 据我所知,SQLite中不存在此功能,我想知道的是,是否有任何方法可以实现相同的效果而不必执行两个查询。另外,如果这不可能,那么您更喜欢什么: SELECT +(插入或更新) 或 UPDATE( 如果UPDATE失败,则 + INSERT ) 问题答案: 因为3.24.0 SQLite还支持upsert ,所以现在您可以简单地编写以下内容