使H2处理带引号的名称和不带引号的名称相同 [英] Make H2 treat quoted name and unquoted name as the same

问题描述

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?

这里是我做过的测试：

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

以下是查询：

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

我可以如何使这些查询与H2结合使用？

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

推荐答案

引号名称H2是区分大小写的，如SQL规范所要求的。这意味着这将工作：

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

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

但不会：

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

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

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

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

与

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

因为H2的行为方式与Oracle相同。这与其他数据库如MySQL和PostgreSQL如何处理标识符名称有点不同。 H2具有兼容性功能：如果您附加 ; DATABASE_TO_UPPER = FALSE 到数据库URL，非引号标识符不会转换为大写，这意味着它们也区分大小写。但是，在创建数据库时，您需要附加此属性，并且每次使用它时（如果您添加现有数据库的设置，现有对象的标识符已经转换为大写）。

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).

顺便说一句，这与功能UPPER 无关，后者是用于数据。您的问题是关于标识符，而不是数据。

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

这篇关于使H2处理带引号的名称和不带引号的名称相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！