java - 对于插入的 123，NUMERIC(5,2) 应该返回 123 还是 123.00？

Question

假设我们有一个包含 NUMERIC(5,2) 的表，我们将 123 作为 BigDecimal 插入。

当我们选择返回值时 - 它应该是 123(正如我们插入的那样)还是 123.00？

---

我在其中一个项目中从 HSQLDB 1.8.0.10 迁移到 2.3.3 时遇到了这个问题。

在 1.8.0.10 中，我得到了插入值的小数位数(123 代表插入的 123)。

在 2.3.3 中，无论如何我都会得到列的比例(123.00 对于插入的 123)。

我已经 reported this as a bug但得到以下回应:

But NUMERIC(5,2) means there is always 2 fractional digits no matter what you insert. Version 1.8 wasn't following the SQL Standard definition.

我不知道这是否正确，但我觉得这不符合逻辑。

我们真的应该“无论如何”在小数部分得到这些零吗？

这是我的测试用例:

    Connection conn = DriverManager.getConnection(...);
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("create table test (value NUMERIC(5,2));");
    String sql = "INSERT INTO test (value) VALUES(?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setBigDecimal(1, BigDecimal.ONE);
    pstmt.executeUpdate();
    ResultSet rs = stmt.executeQuery("SELECT * FROM test");
    while (rs.next()) {
        Assert.assertEquals(BigDecimal.ONE, rs.getBigDecimal(1));
    }
    rs.close();
    stmt.close();
    conn.close();

Answer 1

当您将 123 插入 NUMERIC(5,2) 列时，正确的值为 123.00。这是真的，因为对于数字，值总是被转换为具有目标数据类型的精度。在您的情况下，指定的精度为 5，您的小数位数为 2。只有小数位数为 0 的数字才是整数。

From SQL-1992 standard: 4.4.1 Characteristics of numbers

The scale is a non-negative integer. A scale of 0 indicates that the number is an integer.

[...]

Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an exact numeric value, an approximation of its value that preserves leading significant dig- its after rounding or truncating is represented in the data type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined.

[...]

Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an approximate numeric value, an approximation of its value is represented in the data type of the target. The value is converted to have the precision of the target.

想象一个 VARCHAR(50) 列，它将返回 VARCHAR(3) 类型的字符串，该字符串存储在其中，仅包含 3 个字符。这不会很奇怪吗？

以上示例是有效的，因为如果我们将值 123.00 修剪为 123，它将不再是有效的 numeric(5,2) 数据类型。转换已就位，可让您插入该值而不会引发错误。

此外，在这种特殊情况下，2005.07.11 的旧版 HSQL 1.8.0.1 显然不遵循 SQL 标准。从他们的 site 上可以读到的内容:

HyperSQL 2 supports the dialect of SQL defined by SQL standards 92, 1999, 2003, 2008 and 2011. This means where a feature of the standard is supported, e.g. left outer join, the syntax is that specified by the standard text. Almost all syntactic features of SQL-92 up to Advanced Level are supported, as well as SQL:2011 core and many optional features of this standard.