包含AUTO_INCREMENT列的MERGE INTO表 [英] MERGE INTO table containing AUTO_INCREMENT columns

问题描述

我已声明下表供审计触发器使用:

I've declared the following table for use by audit triggers:

CREATE TABLE audit_transaction_ids (id IDENTITY PRIMARY KEY, uuid VARCHAR UNIQUE NOT NULL, `time` TIMESTAMP NOT NULL);

1. 触发器将在同一事务中被多次调用.

1. The trigger will get invoked multiple times in the same transaction.

第一次调用触发器时，我希望它插入一个新的 具有当前TRANSACTION_ID()和时间的行.

The first time the trigger is invoked, I want it to insert a new row with the current TRANSACTION_ID() and time.

随后调用触发器，我希望它返回 现有的"id"(为此我调用Statement.getGeneratedKeys()) 而不更改"uuid"或时间".

The subsequent times the trigger is invoked, I want it to return the existing "id" (I invoke Statement.getGeneratedKeys() to that end) without altering "uuid" or "time".

当前的架构似乎有两个问题.

The current schema seems to have two problems.

1. 当我调用MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES(TRANSACTION_ID(), NOW())时，我得到:org.h2.jdbc.JdbcSQLException: Column "ID" contains null values; SQL statement: MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES (TRANSACTION_ID(), NOW()) [90081-155]

1. When I invoke MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES(TRANSACTION_ID(), NOW()) I get: org.h2.jdbc.JdbcSQLException: Column "ID" contains null values; SQL statement: MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES (TRANSACTION_ID(), NOW()) [90081-155]

我怀疑在现有行上调用MERGE会改变时间".

I suspect that invoking MERGE on an existing row will alter "time".

如何解决这两个问题?

推荐答案

MERGE与java.util.Map.put(key, value)类似:如果不存在该行，则将插入该行；如果存在该行，则将更新该行.话虽如此，只要您使用另一列作为键，您仍然可以合并到包含AUTO_INCREMENT列的表中.

MERGE is analogous to java.util.Map.put(key, value): it will insert the row if it doesn't exist, and update the row if it does. That being said, you can still merge into a table containing AUTO_INCREMENT columns so long as you use another column as the key.

给出customer[id identity, email varchar(30), count int]，您可以merge into customer(id, email, count) key(email) values((select max(id) from customer c2 where c2.email='[e-mail]'), '[e-mail]', 10).意思是，如果记录存在，请重新使用id，否则，请使用null.

Given customer[id identity, email varchar(30), count int] you could merge into customer(id, email, count) key(email) values((select max(id) from customer c2 where c2.email='[e-mail]'), '[e-mail]', 10). Meaning, re-use the id if a record exists, use null otherwise.

另请参阅 https://stackoverflow.com/a/18819879/14731 ，了解可移植的插入或操作方式-根据行是否已经存在进行更新.

See also https://stackoverflow.com/a/18819879/14731 for a portable way to insert-or-update depending on whether a row already exists.

1.合并到audit_transaction_ids(uuid，时间)KEY(id)值(TRANSACTION_ID()，NOW())

如果您只想插入新行，请使用: INSERT INTO audit_transaction_ids (uuid, time) VALUES(TRANSACTION_ID(), NOW())

If you just want to insert a new row, use: INSERT INTO audit_transaction_ids (uuid, time) VALUES(TRANSACTION_ID(), NOW())

MERGE没有任何意义，因为那样就不可能(即使从理论上来说)也无法更新现有行.您可以做的是使用另一个键列(在上述情况下，没有可以使用的列).有关详细信息，请参见MERGE的文档.

MERGE without setting the value for the column ID doesn't make sense if ID is used as the key, because that way it could never (even in theory) update an existing rows. What you could do is using another key column (in the case above there is no column that could be used). See the documentation for MERGE for details.

2.在现有行上调用MERGE会更改时间"

我不确定您是否谈论时间"列的值已更改的事实.如果使用MERGE ... VALUES(.., NOW())，这是预期的行为，因为MERGE语句应该更新该列.

I'm not sure if you talk about the fact that the value of the column 'time' is altered. This is the expected behavior if you use MERGE ... VALUES(.., NOW()), because the MERGE statement is supposed to update that column.

或者您的意思是，较旧的H2版本在同一事务中返回不同的值(与大多数其他数据库不同，该数据库在同一事务中返回相同的值).的确如此，但是在H2版本1.3.155(2011-05-27)及更高版本中，此不兼容已得到修复.另请参见更改日志:"CURRENT_TIMESTAMP()等，现在返回相同的值在交易中".看来这不是您的问题，因为您似乎确实使用的是1.3.155版(错误消息[90081-155]包含内部版本号/版本号).

Or maybe you mean that older versions of H2 returned different values within the same transaction (unlike most other databases, which return the same value within the same transaction). This is true, however with H2 version 1.3.155 (2011-05-27) and later, this incompatibility is fixed. See also the change log: "CURRENT_TIMESTAMP() and so on now return the same value within a transaction." It looks like this is not the problem in your case, because you do seem to use version 1.3.155 (the error message [90081-155] includes the build / version number).

这篇关于包含AUTO_INCREMENT列的MERGE INTO表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！