使H2处理引用的名称和未引用的名称相同

H2 seems to make a difference between name with quote and name without quote. Is there a way to make it treat them the same way?

H2似乎在名字和引号之间做了不同的区分。有没有一种方法可以让他们以同样的方式对待他们?

Here's the tests that I've done :

这是我做过的测试:

CREATE TABLE test (dummy INT);
CREATE TABLE "testquote" (dummy INT, "quotedDummy" INT);

Here are the queries :

以下是查询:

SELECT * FROM test; --work
SELECT * FROM "test"; -- doesn't work
SELECT * FROM "testquote"; --work
SELECT * FROM testquote; --doesn't work
SELECT dummy FROM "testquote"; --work
SELECT quotedDummy FROM "testquote"; --doesn't work
SELECT "quotedDummy" FROM "testquote"; --work

What can I do to make those queries work with H2?

如何使这些查询与H2一起工作?

1 个解决方案

#1

Quotes names in H2 are case sensitive, as required by the SQL specification. That means this will work:

H2中的引号名称是区分大小写的，这是SQL规范所要求的。这意味着这将奏效:

CREATE TABLE "testquote" (dummy INT, "quotedDummy" INT); 
SELECT * FROM "testquote";

but this will not:

但这不会:

SELECT * FROM "TestQuote";
SELECT * FROM "TESTQuote";
SELECT * FROM "TESTQUOTE";

Unquotes names are not case sensitive in H2. They are normally converted to uppercase (as in Oracle and other databases). That means the statements

在H2中，不带引号的名称不区分大小写。它们通常被转换为大写(如Oracle和其他数据库)。这意味着语句

CREATE TABLE test (dummy INT);
SELECT * FROM test;

are the same as

是一样的

CREATE TABLE "TEST" ("DUMMY" INT);
SELECT * FROM "TEST";

In that H2 behaves in the same way as Oracle. This is a bit different on how other databases like MySQL and PostgreSQL deal with identifier names. H2 has a compatibility feature: If you append ;DATABASE_TO_UPPER=FALSE to the database URL, unquotes identifiers are not converted to uppercase, that means they are case sensitive as well. But you need append this when creating the database, and each time you use it (if you append the setting for existing databases, the identifiers of existing objects are already converted to uppercase).

在这种情况下，H2的行为与Oracle相同。这与其他数据库(如MySQL和PostgreSQL)处理标识符名称的方式有点不同。H2有一个兼容性特性:如果将DATABASE_TO_UPPER=FALSE添加到数据库URL，则不加引号的标识符不会转换为大写，这意味着它们也区分大小写。但是在创建数据库时，您需要附加它，并且每次使用它时(如果您附加现有数据库的设置，则现有对象的标识符已经转换为大写)。

By the way, this has nothing to do with the function UPPER, which is meant for data. Your question is about identifiers, not data.

顺便说一下，这和函数UPPER没有关系，它是用来表示数据的。你的问题是关于标识符，而不是数据。

#1