SQLite中文网
浮点数字
▼ 目录

1. SQLite如何存储数字

SQLite 以 64 位 二进制补码 格式存储整数值。这给出了 -9223372036854775808 到 +9223372036854775807（含）的存储范围。此范围内的整数是精确的。

所谓的“REAL”或浮点值以 IEEE 754 Binary-64 格式存储。这给出了大约在 1.7976931348623157e+308 和 4.9406564584124654e-324 之间的正值范围以及等效的负值范围。binary64 也可以是 0.0（和 -0.0）、正无穷大和负无穷大以及“NaN”或“非数字”。浮点值是近似值。

密切注意上一段中的最后一句话：

浮点值是近似值。

如果你需要一个确切的答案，你不应该在 SQLite 或任何其他产品中使用 binary64 浮点值。这不是 SQLite 限制。这是浮点数设计中固有的数学限制。

1.1. 浮点精度

SQLite 承诺保留浮点值的 15 个最重要的数字。但是，它不保证浮点值计算的准确性，因为这样的保证是不可能的。对浮点值进行数学运算会引入错误。例如，考虑一下如果您尝试减去两个大小相似的浮点数会发生什么：

1152693165.1106291898
-1152693165.1106280772
0.0000011126

上面显示的结果 (0.0000011126) 是正确答案。但是，如果您使用 binary64 浮点数进行此计算，您得到的答案是 0.00000095367431640625——大约 14% 的误差。如果您在程序中执行许多类似的计算，错误就会累积起来，最终结果可能完全没有意义。

错误的产生是因为每个数字只有大约前 15 位有效数字被准确存储，并且被减去的两个数字之间的第一个差值在第 16 位。

1.2. 浮点数字

binary64 浮点格式每个数字使用 64 位。因此有 1.845e+19 个不同的可能浮点值。另一方面，在 1.7977e+308 和 4.9407e-324 范围内有无限多个实数。那么 binary64 不可能表示该范围内所有可能的实数。需要近似值。

IEEE 754 浮点值是整数乘以 2 的幂：

M × 2E

M 值是“尾数”，E 是“指数”。M和E都是整数。

对于 Binary64，M 是一个 53 位整数，E 是一个偏移的 11 位整数，因此表示介于 -1074 和 +972（含）之间的值范围。

（注意：IEEE 754 的通常描述更为复杂，如果您真的想了解 IEEE 754 的细节、优点和局限性，了解增加的复杂性很重要。但是，此处显示的整数描述虽然不完全对，更容易理解并且足以满足本文的目的。）

1.2.1. 无法表示的数字

并非每个有效数字少于 16 位的十进制数都可以精确表示为 binary64 数。事实上，大多数小数点右边有数字的十进制数都缺少精确的 binary64 等价物。例如，如果您有一个旨在以美元和美分保存商品价格的数据库列，则唯一可以精确表示的美分值是 0.00、0.25、0.50 和 0.75。小数点右边的任何其他数字都会产生近似值。如果您提供 47.49 的“价格”值，则该数字将以 binary64 表示为：

6683623321994527 × 2 -47

结果是：

47.49000000000000198951966012828052043914794921875

这个数字非常接近 47.49，但并不准确。它有点太大了。如果我们将 M 减一到 6683623321994526 以便我们有下一个更小的可能的 binary64 值，我们得到：

47.4899999999999948840923025272786617279052734375

第二个数字太小了。第一个数字更接近 47.49 的期望值，因此被使用。但这并不准确。大多数十进制值在 IEEE 754 中都是这样工作的。请记住我们上面提出的关键点：

浮点值是近似值。

如果您对浮点值一无所知，请不要忘记这个关键思想。

1.2.2. 够近了吗？

IEEE 754 Binary64 提供的精度足以满足大多数计算。例如，如果“47.49”代表价格，而通货膨胀率每年为 2%，则价格每秒上涨约 0.0000000301 美元。记录值 47.49 的误差代表大约 66 纳秒的通货膨胀。因此，如果输入时 47.49 的价格是准确的，那么通货膨胀的影响将导致真实值在不到百万分之一秒内正好等于实际存储的值 (47.4900000000000019895196601282805204391479492187)。对于大多数目的来说，这种精度水平肯定足够了吗？

2.处理浮点数的扩展

2.1. ieee754.c 扩展

ieee754 扩展在其 binary64 表示和 M×2 E格式之间转换浮点数。换句话说，在表达式中：

F = M × 2 E

ieee754 扩展在 F 和 (M,E) 之间来回转换。

ieee754 扩展不是合并的一部分，但它默认包含在CLI中。如果你想在你的应用程序中包含 ieee754 扩展，你将需要单独编译和加载它。

2.1.1. ieee754() 函数

ieee754(F) SQL 函数采用单个浮点参数作为其输入并返回如下所示的字符串：

'ieee754(M,E)'

除了M和E被浮点数的尾数和指数代替。例如： 

sqlite> .mode box
sqlite> SELECT ieee754(47.49) AS x;
┌───────────────────────────────┐
│               x               │
├───────────────────────────────┤
│ ieee754(6683623321994527,-47) │
└───────────────────────────────┘

从另一个方向来看，ieee754() 的双参数版本采用 M 和 E 值并将它们转换为相应的 F 值： 

sqlite> select ieee754(6683623321994527,-47) as x;
┌───────┐
│   x   │
├───────┤
│ 47.49 │
└───────┘

2.1.2. ieee754_mantissa() 和 ieee754_exponent() 函数

ieee754() 的单参数形式的文本输出非常适合人类阅读，但用作较大表达式的一部分时却很尴尬。因此，添加了 ieee754_mantissa() 和 ieee754_exponent() 例程以返回与其单个参数 F 值相对应的 M 和 E 值。例如： 

sqlite> .mode box
sqlite> SELECT ieee754_mantissa(47.49) AS M, ieee754_exponent(47.49) AS E;
┌──────────────────┬─────┐
│        M         │  E  │
├──────────────────┼─────┤
│ 6683623321994527 │ -47 │
└──────────────────┴─────┘

2.1.3. ieee754_from_blob() 和 ieee754_to_blob() 函数

ieee754_to_blob(F) SQL 函数将浮点数 F 转换为 8 字节 BLOB，即该数字的 big-endian binary64 编码。ieee754_from_blob(B) 函数采用另一种方式，将 8 字节的 blob 转换为 binary64 编码表示的浮点值。

因此，例如，如果您 在维基百科上读到最小正 binary64 值的编码是 0x0000000000000001，那么您可以像这样找到相应的浮点值： 

sqlite> .mode box
sqlite> SELECT ieee754_from_blob(x'0000000000000001') AS F;
┌───────────────────────┐
│           F           │
├───────────────────────┤
│ 4.94065645841247e-324 │
└───────────────────────┘

或者走另一条路： 

sqlite> .mode box
sqlite> SELECT quote(ieee754_to_blob(4.94065645841247e-324)) AS binary64;
┌─────────────────────┐
│      binary64       │
├─────────────────────┤
│ X'0000000000000001' │
└─────────────────────┘

2.2. decimal.c 扩展

decimal 扩展为存储为文本字符串的数字提供任意精度的十进制算术。由于数字以任意精度存储并作为文本存储，因此不需要近似值。可以精确地进行计算。

十进制扩展不是（当前）SQLite合并的一部分。但是，它包含在CLI中。

提供三种数学函数：

  • decimal_add(A,B)
  • decimal_sub(A,B)
  • decimal_mul(A,B)

这些函数分别对其参数进行加、减和乘，并返回一个新的文本字符串，该文本字符串是结果的十进制表示形式。此时没有除法运算符。

使用 decimal_cmp(A,B) 比较两个十进制值。如果 A 小于、等于或大于 B，则结果分别为负、零或正。

decimal_sum(X) 函数是一个聚合函数，类似于内置的 sum() 聚合函数，除了 decimal_sum() 以任意精度计算其结果，因此是精确的。

最后，decimal 扩展提供了“decimal”整理序列，它按数字顺序比较十进制文本字符串。

三、技巧

以下 SQL 说明了如何使用 ieee754 和 decimal 扩展来计算 binary64 浮点数的精确十进制等效值。 

-- The pow2 table will hold all the necessary powers of two.
CREATE TABLE pow2(x INTEGER PRIMARY KEY, v TEXT);
WITH RECURSIVE c(x,v) AS (
  VALUES(0,'1')
  UNION ALL
  SELECT x+1, decimal_mul(v,'2') FROM c WHERE x+1<=971
) INSERT INTO pow2(x,v) SELECT x, v FROM c;
WITH RECURSIVE c(x,v) AS (
  VALUES(-1,'0.5')
  UNION ALL
  SELECT x-1, decimal_mul(v,'0.5') FROM c WHERE x-1>=-1075
) INSERT INTO pow2(x,v) SELECT x, v FROM c;

-- This query finds the decimal representation of each value in the "c" table.
WITH c(n) AS (VALUES(47.49))
                 ----XXXXX----------- Replace with whatever you want
SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v)
  FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n);