本文共 5832 字，大约阅读时间需要 19 分钟。
本文翻译自：
Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: 几个月前，我从关于堆栈溢出的答案中学到了如何使用以下语法在MySQL中一次执行多个更新：
INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);
I've now switched over to PostgreSQL and apparently this is not correct. 我现在已经切换到PostgreSQL，显然这是不正确的。 It's referring to all the correct tables so I assume it's a matter of different keywords being used but I'm not sure where in the PostgreSQL documentation this is covered. 它指的是所有正确的表，因此我假设使用的是不同的关键字，但是我不确定在PostgreSQL文档的哪个地方覆盖。
To clarify, I want to insert several things and if they already exist to update them. 为了澄清，我想插入几件事，如果它们已经存在，请对其进行更新。
参考：
Personally, I've set up a "rule" attached to the insert statement. 就个人而言，我已经在插入语句上设置了一个“规则”。 Say you had a "dns" table that recorded dns hits per customer on a per-time basis: 假设您有一个“ dns”表，该表按时间记录了每个客户的dns命中：
CREATE TABLE dns ( "time" timestamp without time zone NOT NULL, customer_id integer NOT NULL, hits integer);
You wanted to be able to re-insert rows with updated values, or create them if they didn't exist already. 您希望能够重新插入具有更新值的行，或者如果还不存在则创建它们。 Keyed on the customer_id and the time. 键入customer_id和时间。 Something like this: 像这样：
CREATE RULE replace_dns AS ON INSERT TO dns WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id)))) DO INSTEAD UPDATE dns SET hits = new.hits WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));
Update: This has the potential to fail if simultaneous inserts are happening, as it will generate unique_violation exceptions. 更新：如果同时进行插入，则有可能失败，因为它将生成unique_violation异常。 However, the non-terminated transaction will continue and succeed, and you just need to repeat the terminated transaction. 但是，未终止的事务将继续并成功，您只需要重复终止的事务即可。
However, if there are tons of inserts happening all the time, you will want to put a table lock around the insert statements: SHARE ROW EXCLUSIVE locking will prevent any operations that could insert, delete or update rows in your target table. 但是，如果一直有大量的插入操作，您将需要在insert语句周围放置一个表锁定：SHARE ROW EXCLUSIVE锁定将阻止任何可能在目标表中插入，删除或更新行的操作。 However, updates that do not update the unique key are safe, so if you no operation will do this, use advisory locks instead. 但是，不更新唯一密钥的更新是安全的，因此，如果您没有任何操作将执行此操作，请改用咨询锁。
Also, the COPY command does not use RULES, so if you're inserting with COPY, you'll need to use triggers instead. 另外，COPY命令不使用RULES，因此，如果要使用COPY插入，则需要使用触发器。
According the , handling the ON DUPLICATE KEY case is not supported. 根据的不支持处理ON DUPLICATE KEY大小写。 That part of the syntax is a proprietary MySQL extension. 语法的该部分是专有的MySQL扩展。
There is no simple command to do it. 没有简单的命令可以执行此操作。
The most correct approach is to use function, like the one from . 最正确的方法是使用功能，例如 。
Another solution (although not that safe) is to do update with returning, check which rows were updates, and insert the rest of them 另一个解决方案（尽管不是很安全）是通过返回进行更新，检查哪些行已更新，然后插入其余行
Something along the lines of: 类似于以下内容：
update tableset column = x.columnfrom (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)where table.id = x.idreturning id;
assuming id:2 was returned: 假设返回id：2：
insert into table (id, column) values (1, 'aa'), (3, 'cc');
Of course it will bail out sooner or later (in concurrent environment), as there is clear race condition in here, but usually it will work. 当然，它会早晚（在并发环境中）纾困，因为这里有明确的竞争条件，但通常它会起作用。
Here's a . 这是的 。
PostgreSQL since version 9.5 has syntax, with clause. 自9.5版起的PostgreSQL具有语法，带有子句。 with the following syntax (similar to MySQL) 使用以下语法（类似于MySQL）
INSERT INTO the_table (id, column_1, column_2) VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2;
Searching postgresql's email group archives for "upsert" leads to finding : 在postgresql的电子邮件组档案中搜索“ upsert”，会在手册中找到 ：
Example 38-2. 示例38-2 Exceptions with UPDATE/INSERT UPDATE / INSERT的例外
This example uses exception handling to perform either UPDATE or INSERT, as appropriate: 本示例根据需要使用异常处理来执行UPDATE或INSERT：
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS$$BEGIN LOOP -- first try to update the key -- note that "a" must be unique UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP;END;$$LANGUAGE plpgsql;SELECT merge_db(1, 'david');SELECT merge_db(1, 'dennis');
There's possibly an example of how to do this in bulk, using CTEs in 9.1 and above, in the : 可能有一个示例，说明如何使用9.1及更高版本中的CTE批量执行此操作：
WITH foos AS (SELECT (UNNEST(%foo[])).*)updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)WHERE updated.id IS NULL;
See for a clearer example. 有关更清楚的示例，请参见 。
For merging small sets, using the above function is fine. 对于合并小集合，使用上面的功能很好。 However, if you are merging large amounts of data, I'd suggest looking into 但是，如果要合并大量数据，建议您浏览
The current best practice that I'm aware of is: 我知道的当前最佳实践是：
转载地址：http://cncnb.baihongyu.com/