H2-CREATE TABLE创建错误的数据类型 [英] H2 - CREATE TABLE creates wrong data type

问题描述

使用H2内存数据库测试我的DAL当前不起作用，因为数据类型BINARY被转换为VARBINARY:

Testing my DAL with H2 in-memory database currently doesn't work because the data tye BINARY gets converted to VARBINARY:

CREATE TABLE test (
  pk_user_id INT AUTO_INCREMENT(1, 1) PRIMARY KEY,
  uuid BINARY(16) UNIQUE NOT NULL
);

如果我检查是否存在预期数据类型的列，则会导致数据类型错误:

which results in a wrong data type if I check if the columns with the expected data types exists:

2017-03-20 16:24:48 persistence.database.Table检查意外列 (UUID)或类型(-3，VARBINARY)

2017-03-20 16:24:48 persistence.database.Table check Unexpected column (UUID) or type (-3, VARBINARY)

推荐答案

tl; dr

这会导致错误的数据类型

which results in a wrong data type

否，不是错误类型，只是同一类型的另一个 label .

No, not the wrong type, just another label for the same type.

二进制类型具有五个同义词:{ BINARY | VARBINARY | LONGVARBINARY | RAW | BYTEA }

The binary type has five synonyms: { BINARY | VARBINARY | LONGVARBINARY | RAW | BYTEA }

所有五个名称均表示相同类型，并且都映射到byte[]在Java中.

All five names mean the same type, and all map to byte[] in Java.

SQL世界中没有严格定义数据类型. SQL规范仅定义了几种类型.许多数据库系统通过许多名称定义了许多类型.为了使客户更容易地从一个数据库系统移植到他们的数据库系统，数据库供应商通常为数据类型实现同义词，以匹配其类型兼容的竞争对手的数据类型.

Data types are not strictly defined in the SQL world. The SQL spec defines only a few types. Many database systems define many types by many names. To make it easier for a customer to port from one database system to theirs, the database vendors commonly implement synonyms for data types to match those of their competitors where the types are compatible.

H2对于一种数据类型具有多个名称.对于将整个值都加载到内存中的二进制类型， H2为相同的单个数据类型定义五个名称:

H2 like many other databases systems have more than one name for a datatype. For a binary type where the entire value is loaded into memory, H2 defines five names for the same single data type:

{二进制| VARBINARY | LONGVARBINARY | RAW | BYTEA}

{ BINARY | VARBINARY | LONGVARBINARY | RAW | BYTEA }

类似地，H2通过五个同义词中的任何一个提供了一个带符号的32位整数数据类型:

Similarly, H2 provides for a signed 32-bit integer datatype by any of five synonyms:

{INT |整数| MEDIUMINT | INT4 |签名}

{ INT | INTEGER | MEDIUMINT | INT4 | SIGNED }

因此，您可以指定这五个名称中的任何一个，但将获得相同的效果，并且使用H2提供的相同基础数据类型.

So you can specify any of these five names but you will get the same effect, the same underlying datatype provided by H2.

实际上，我本人运行代码以使用二进制类型的这五个名称中的每个名称来创建该列.在每种情况下，列名称的元数据都将数据类型报告为VARBINARY.

Indeed, I myself ran code to create the column using each of those five names for the binary type. In each case, the metadata for the column name reports the datatype as VARBINARY.

尽管在内部使用哪个五个来跟踪列的数据类型并不重要，但对于VARBINARY的使用我还是有些惊讶，因为

While it does not really matter which of the five is used internally to track the column’s datatype, I am a bit surprised as to the use of VARBINARY because the H2 datatype documentation page heading advertises this type as BINARY. So I would expect BINARY to be used by default in the metadata. You might want to log a bug/issue for this if you really care, as it seems either the doc heading should be changed to VARBINARY or H2’s internal labelling for the datatype should be changed to BINARY.

下面是一些示例Java JDBC代码，用于确认您在问题"中报告的行为.

Below is some example Java JDBC code confirming the behavior you report in your Question.

我建议您更改数据类型检查代码，以查找该数据类型的五个可能名称中的任何，而不是仅检查一个特定名称.

I suggest you change your datatype-checking code to look for any of the five possible names for this datatype rather than check for only one specific name.

try {
    Class.forName ( "org.h2.Driver" );
} catch ( ClassNotFoundException e ) {
    e.printStackTrace ( );
}
try ( Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:" ) ;
      Statement stmt = conn.createStatement ( ) ; ) {
    String tableName = "test_";
    String sql = "CREATE TABLE " + tableName + " (\n" +
            "  pk_user_id_ INT AUTO_INCREMENT(1, 1) PRIMARY KEY,\n" +
            "  uuid_ BINARY(16) UNIQUE NOT NULL\n" +
            ");";
//            String sql = "CREATE TABLE " + tableName +
//                    "(" +
//                    " id_ INT AUTO_INCREMENT(1, 1) PRIMARY KEY, " +
//                    " binary_id_ BINARY(16) UNIQUE NOT NULL, " +
//                    " uuid_id_ UUID, " +
//                    " age_ INTEGER " + ")";
    stmt.execute ( sql );

    // List tables
    DatabaseMetaData md = conn.getMetaData ( );
    try ( ResultSet rs = md.getTables ( null, null, null, null ) ) {
        while ( rs.next ( ) ) {
            System.out.println ( rs.getString ( 3 ) );
        }
    }

    // List columns of our table.
    try ( ResultSet rs = md.getColumns ( null, null, tableName.toUpperCase ( Locale.US ), null ) ) {
        System.out.println ( "Columns of table: " + tableName );
        while ( rs.next ( ) ) {
            System.out.println ( rs.getString ( 4 ) + " | " + rs.getString ( 5 ) + " | " + rs.getString ( 6 ) ); // COLUMN_NAME, DATA_TYPE , TYPE_NAME.
        }
    }
} catch ( SQLException e ) {
    e.printStackTrace ( );
}

目录

收藏

...

用户

观看次数

TEST _

表中的列:test _

Columns of table: test_

PK_USER_ID_ | 4 |整数

PK_USER_ID_ | 4 | INTEGER

UUID_ | -3 | VARBINARY

UUID_ | -3 | VARBINARY

提示:

• 在所有SQL名称后面加上下划线，可以避免与SQL世界中超过一千个保留字的任何冲突. SQL规范承诺，SQL系统绝不会使用结尾的下划线.例如，您使用列名称uuid可能与H2的UUID数据类型冲突.
• 您的代码uuid BINARY(16)建议您尝试存储 UUID (128-位值，其中某些位定义了语义).请注意， H2本身支持UUID 作为数据类型 Postgres 和其他一些数据库系统也是如此.因此，将uuid_ BINARY(16)更改为uuid_ UUID.

• Adding a trailing underscore to all your SQL names avoids collisions with any of the over one thousand reserved words found in the SQL world. The SQL spec promises a trailing underscore will never be used by a SQL system. For example, your use of the column name uuid could conflict with H2’s UUID datatype.
• Your code uuid BINARY(16) suggests you are trying to store a UUID (a 128-bit value where some bits have defined semantics). Note that H2 supports UUID natively as a data type as does Postgres and some other database systems. So change uuid_ BINARY(16) to uuid_ UUID.

这篇关于H2-CREATE TABLE创建错误的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！