java - DatabaseMetaData.getColumns 对于 int 值有奇怪的列大小

Question

我有一个 PostgreSQL 表

create table test (
id int,
name varchar(2),
active boolean,
long_id bigint,
created timestamptz);
)

我想获取此表中每列的名称、类型和大小。这是我的代码:

public static void main(final String[] args) throws SQLException {
        final Connection connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/test", "postgres", "123");
        final DatabaseMetaData metaData = connection.getMetaData();
        try (final ResultSet resultSet = metaData.getColumns("public", null, "test", null)) {
            while (resultSet.next()) {
                System.out.println(resultSet.getString("COLUMN_NAME") + " " + resultSet.getInt("DATA_TYPE") + " " + resultSet.getInt("COLUMN_SIZE"));
            }
        }
    }

这是输出

id 4 10
name 12 2
active -7 1
long_id -5 19
created 93 35

第三列显示列大小，例如对于 boolean 值active，它是 1 个字节。但是为什么 id 值(在 PostgreSQL 中是 int)显示 10 个字节而不是 4 个字节？

Answer 1

如 DatabaseMetaData.getColumns 中所述(强调我的):

The COLUMN_SIZE column specifies the column size for the given column. For numeric data, this is the maximum precision. For character data, this is the length in characters. For datetime datatypes, this is the length in characters of the String representation (assuming the maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data types where the column size is not applicable.

32 位整数的最大精度为 10，64 位整数(长整型)的最大精度为 19。

换句话说，您假设 COLUMN_SIZE 包含以字节为单位的大小是错误的。 getColumns 结果不提供该信息，CHAR_OCTET_LENGTH 列中的字符类型除外。

COLUMN_SIZE 的定义源自 X/Open SQL CLI，其修改后的形式存在于调用级接口(interface) (SQL/CLI) 标准 (ISO-9075-3) 中，具体来自 DescribeCol 函数(引用自 ISO-9075-3:2003 草案):

b) Case:
  i) If the data type of C is character string, then ColumnSize is set to the maximum length in octets of C.
  ii) If the data type of C is exact numeric or approximate numeric, then ColumnSize is set to the maximum length of C in decimal digits.
  iii) If the data type of C is datetime or interval, then ColumnSize is set to the length in positions of C.
  iv) If the data type of C is a reference type, then ColumnSize is set to the length in octets of that reference type.
  v) Otherwise, ColumnSize is set to an implementation-dependent value.