Spanner 提供了 NUMERIC
类型,该类型可以精确存储小数精度数字。Spanner 中 NUMERIC
类型的语义因其两个 SQL 方言(GoogleSQL 和 PostgreSQL)而异,尤其是在精度和精确度限制方面:
PostgreSQL 方言中的
NUMERIC
是一种任意小数精度数值类型(标度或精度可以是支持范围内的任何数字),因此是存储任意精度数值数据的理想之选。GoogleSQL 中的
NUMERIC
是一种固定精度数值类型(精度为 38,标度为 9),无法用于存储任意精度数值数据。如果您需要在 GoogleSQL 方言数据库中存储任意精度数字,我们建议您将这些数值存储为字符串。
Spanner 数值类型的精度
精度是指数字位数。标度是数字中小数点右边的位数。例如,数字 123.456 的精度为 6,标度为 3。Spanner 有三种数字类型:
- 64 位有符号整数类型,在 GoogleSQL 方言中称为
INT64
,在 PostgreSQL 方言中称为INT8
。 - IEEE 64 位(双)二进制精度浮点类型,在 GoogleSQL 方言中称为
FLOAT64
,在 PostgreSQL 方言中称为FLOAT8
。 - 小数精度
NUMERIC
类型。
我们分别来看看精度和标度。
INT64
/ INT8
表示不含小数部分的数值。此数据类型提供 18 位精度,标度为零。
FLOAT64
/ FLOAT8
只能表示包含小数部分的近似十进制数值,并提供 15 到 17 位有效数字(去除所有尾随零后的数字中的数字数量)的十进制精度。我们认为这种类型表示近似小数值,因为 Spanner 使用的 IEEE 64 位浮点二进制表示法不能精确表示小数(以 10 为底数)部分(它只能精确表示以 2 为底数的小数部分)。这种精度损失会造成一些小数部分出现舍入误差。
例如,使用 FLOAT64
/ FLOAT8
数据类型存储小数值 0.2 时,二进制表示法将转换为小数值 0.20000000000000001(精度为 18 位)。类似地,(1.4 * 165) 将转换为 230.999999999999971,(0.1 + 0.2) 将转换为0.30000000000000004。这就是为什么我们会说 64 位浮点数仅具有 15-17 位有效数字的精度的原因(只有一些小数位数超过 15 位的数字才能表示为 64 位浮点数,而无需舍入)。如需详细了解如何计算浮点精度,请参阅双精度浮点格式。
对于通常需要 30 位或更高精度的金融、科学或工程计算来说,INT64
/ INT8
和 FLOAT64
/ FLOAT8
精度均不理想。
NUMERIC
数据类型适用于这些应用,因为它可以表示精度超过 30 位小数的精确小数精度数值。
GoogleSQL NUMERIC
数据类型可以表示小数精度固定为 38、标度固定为 9 的数字。GoogleSQL NUMERIC
的范围为 -99999999999999999999999999999.999999999 到 99999999999999999999999999999.999999999。
PostgreSQL 方言 NUMERIC
类型可以表示小数精度最高为 147,455,标度最高为 16,383 的数字。
如果您需要存储大于 NUMERIC
提供的精度和标度的数字,以下部分介绍了一些推荐的解决方案。
建议:将任意精度数字存储为字符串
如果您需要在 Spanner 数据库中存储任意精度数字,并且您所需的精度比 NUMERIC
提供的更加精准,我们建议您将这些数值以十进制表示法存储在 STRING
/ VARCHAR
列中。例如,数字 123.4
存储为字符串 "123.4"
。
如果使用此方法,您的应用必须针对数据库读写在数值的应用内部表示法与 STRING
/ VARCHAR
列值之间执行无损转换。
大多数任意精度库都有内置方法来执行这种无损转换。例如,在 Java 中,您可以使用 BigDecimal.toPlainString()
方法和 BigDecimal(String)
构造函数。
将数字存储为字符串的优点是,数值以精确的精度(取决于 STRING
/ VARCHAR
列的长度限制)存储,并且数值仍然是直观易懂的。
执行精确的聚合和计算
要对任意精度数字的字符串表示执行精确的聚合和计算,必须由您的应用执行这些计算。 而不能使用 SQL 聚合函数。
例如,要获得对一系列行执行 SQL SUM(value)
的同样的效果,应用必须查询行的字符串值,然后在应用内部进行转换和求和。
执行近似聚合、排序和计算
您可以使用 SQL 查询,通过将值转换为 FLOAT64
/ FLOAT8
来执行近似聚合计算:
SELECT SUM(CAST(value AS FLOAT64)) FROM my_table
SELECT SUM(value::FLOAT8) FROM my_table
同样,您可以按数值排序,或者转换后按范围限制值:
SELECT value FROM my_table ORDER BY CAST(value AS FLOAT64);
SELECT value FROM my_table WHERE CAST(value AS FLOAT64) > 100.0;
SELECT value FROM my_table ORDER BY value::FLOAT8;
SELECT value FROM my_table WHERE value::FLOAT8 > 100.0;
这些计算近似于 FLOAT64
/ FLOAT8
数据类型的限制。
替代方案
您还可以通过其他方法在 Spanner 中存储任意精度数字。如果将任意精度数字存储为字符串不适合您的应用,请考虑以下替代方案:
存储应用扩缩的整数值
要存储任意精度数字,您可以在写入之前预先扩大值,使数字始终存储为整数,并在读取后重新缩小值。您的应用存储固定的比例因数,精度限制为 INT64
/ INT8
数据类型提供的 18 位数。
例如,假设有一个数字需要以 5 位小数的精度存储。应用会将该值乘以 100000(将小数点向左移 5 位),使其转换为整数,因此值 12.54321 会存储为 1254321
。
从货币角度来看,这种方法就像将美元金额存储为千分之一美分的倍数,类似于以毫秒为单位存储时间单位。
应用会确定固定的比例因素。如果您要更改比例因数,必须转换数据库中所有先前扩缩的值。
此方法可存储直观易懂的值(假设您知道比例因数)。此外,只要结果正确扩缩且不溢出,您就可以使用 SQL 查询直接对存储在数据库中的值执行计算。
将未扩缩的整数值和比例存储在不同的列中
您还可以使用两个元素在 Spanner 中存储任意精度数字:
- 存储在字节数组中的未扩缩整数值。
- 一个指定了比例因数的整数。
首先,您的应用将任意精度小数转换为未扩缩的整数值。例如,应用将 12.54321
转换为 1254321
。此示例的小数位为 5
。
然后,应用使用标准的可移植二进制表示法(例如,big-endian 二进制补码 )将未扩缩的整数值转换为字节数组。
然后,数据库将字节数组 (BYTES
/ BYTEA
) 和整数比例 (INT64
/ INT8
) 存储在两个单独的列中,并在读取时将它们转换回来。
在 Java 中,您可以使用 BigDecimal
和 BigInteger
执行以下计算:
byte[] storedUnscaledBytes = bigDecimal.unscaledValue().toByteArray();
int storedScale = bigDecimal.scale();
您可以使用以下代码读回为 Java BigDecimal
:
BigDecimal bigDecimal = new BigDecimal(
new BigInteger(storedUnscaledBytes),
storedScale);
此方法以任意精度和可移植表示法存储值,但这些值在数据库中不是直观易懂的,并且所有计算必须由应用执行。
将应用内部表示存储为字节
您还可以使用应用的内部表示将任意精度小数值序列化为字节数组,然后将它们直接存储在数据库中。
存储的数据库值不是直观易懂的,并且需要由应用执行所有计算。
这种方法具有可移植性问题。如果您尝试使用与最初编写它的编程语言或库不同的编程语言或库来读取值,可能无法正常读取。重新读取回值可能失败,因为不同的任意精度库的字节数组的序列化表示法可能有所不同。
后续步骤
- 了解 Spanner 可用的其他数据类型。
- 了解如何正确设置 Spanner 架构设计和数据模型。
- 了解如何优化 Spanner 的架构设计。