HSQLDB中的原子INSERT/SELECT [英] Atomic INSERT/SELECT in HSQLDB

问题描述

我有以下hsqldb表，其中将UUID映射到自动递增的ID:

I have the following hsqldb table, in which I map UUIDs to auto incremented IDs:

SHORT_ID (BIG INT, PK, auto incremented) | UUID (VARCHAR, unique)

创建命令:

CREATE TABLE mytable (SHORT_ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, UUID VARCHAR(36) UNIQUE)

为了同时添加新对，我想使用原子MERGE INTO语句.所以我的(准备好的)语句看起来像这样:

In order to add new pairs concurrently, I want to use the atomic MERGE INTO statement. So my (prepared) statement looks like this:

MERGE INTO mytable USING (VALUES(CAST(? AS VARCHAR(36)))) AS v(x) ON mytable.UUID = v.x WHEN NOT MATCHED THEN INSERT VALUES v.x

当我执行该语句(正确设置占位符)时，我总是得到

When I execute the statement (setting the placeholder correctly), I always get a

Caused by: org.hsqldb.HsqlException: row column count mismatch

能不能给我一个提示，这是怎么回事?

Could you please give me a hint, what is going wrong here?

谢谢.

推荐答案

结语

我将此行为报告为一个错误，并且今天(2010-05-25)已根据

I reported this behavior as a bug, and it is today (2010-05-25) fixed in the hsqldb SVN repository, per hsqldb-Bugs-2989597. (Thanks, hsqldb!)

更新后的答案

整齐！这是我在HSQLDB 2.0.0rc9下的工作方式，它支持您发布的语法和错误消息:

Neat one! Here's what I got to work under HSQLDB 2.0.0rc9, which supports the syntax and the error message you posted:

MERGE INTO mytable
   USING (SELECT 'a uuid' FROM dual) AS v(x) -- my own "DUAL" table
   ON (mytable.UUID = v.x)
   WHEN NOT MATCHED THEN INSERT
     VALUES (NULL, x)                        -- explicit NULL for "SHORT_ID" :(

注意，我不能不说服2.0.0rc9接受... THEN INSERT (UUID) VALUES (x)，这是IIUC完全可以接受的，比上述更清晰的规范. (我的SQL知识很少，但这对我来说似乎是个错误.)

Note, I could not convince 2.0.0rc9 to accept ... THEN INSERT (UUID) VALUES (x), which is IIUC a perfectly acceptable and clearer specification than the above. (My SQL knowledge is hardly compendious, but this looks like a bug to me.)

原始答案

您似乎正在将单个值(一个1元组)插入到具有多列的表中.也许您可以将语句结尾修改为:

You appear to be INSERTing a single value (a 1-tuple) into a table with more than one column. Perhaps you can modify the end of your statement to read:

...未匹配时插入("UUID")值(v.x)

... WHEN NOT MATCHED INSERT ("UUID") VALUES (v.x)

这篇关于HSQLDB中的原子INSERT/SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！