SQLITE3 浮点值类型精度问题

作者: Jim Wang 公众号: 巴博萨船长

摘要：当前项目遇到的问题：SQLITE3 数据库表在创建时，表中字段类型被设置成FLOAT，该字段的写入与读取时，小数点的的精度不一致。

Abstract: A problem encountered in the current project: When a SQLITE3 database table is created, the field type in the table is set to FLOAT, and the precision of the decimal point is not consistent when the field is written and read.

项目中，数据库表在创建时，某一字段类型被设置成FLOAT类型用于保存文件的时间戳，但这是历史代码无法轻易改变。实践中遇到的问题是，存储数据时调试器显示的数据位1698925725.0340421，但是读取该条目该字段时，调试器显示1698925725.034042，那丢失的最后一个小数点1的原因是什么?

SQLite 中 `FLOAT`和`REAL`数据类型

在SQLite中，FLOAT和REAL两种数据类型实际上是等效的。SQLite使用REAL数据类型来表示浮点数，并且FLOAT是REAL的一个别名（亲和）SQLite.org文档，数据类型，标题3.1 列亲和力判定。这意味着在SQLite中，你可以使用FLOAT或REAL来定义一个浮点数字段，它们的行为和存储方式都是相同的。因此，无论你使用FLOAT还是REAL，都将得到相同的浮点数存储和处理效果。这两种类型在SQLite中是完全等价的，没有区别。

SQLite 存储类

每个存储在 SQLite 数据库中的值都具有以下存储类之一：

存储类	描述
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

关于SQLite 类型亲和(Affinity)类型（部分）

SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型：

数据类型·	亲和类型	描述
REAL DOUBLE DOUBLE PRECISIONF LOAT	REAL	其规则基本等同于NUMERIC，唯一的差别是不会将”30000.0”这样的文本数据转换为INTEGER存储方式。
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如”30000.0”，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。

根据官方文档SQLite.org文档，数据类型，标题3.1 列亲和力判定，

对于未声明为STRICT的表，列的关联性由声明的列类型决定，按照以下规则按所示顺序：

如果声明的类型包含字符串“INT”，则为它分配 INTEGER 关联性。
如果列的声明类型包含任何字符串“CHAR”、“CLOB”或“TEXT”，则该列具有 TEXT 关联性。请注意，类型 VARCHAR 包含字符串“CHAR”，因此被分配了 TEXT 关联性。
如果列的声明类型包含字符串“BLOB”或者未指定类型，则该列具有亲和性 BLOB。
如果列的声明类型包含任何字符串“REAL”、“FLOA”或“DOUB”，则该列具有 REAL 关联性。
否则，关联性为 NUMERIC。

请注意，确定列亲和力的规则顺序很重要，一些注意事项：

声明类型为“CHARINT”的列将匹配规则 1 和 2，但第一个规则优先，因此列亲和力将为 INTEGER。
类型名称后面的括号中的数字参数（例如：“VARCHAR(255)”）会被 SQLite 忽略 - SQLite 不会对字符串、BLOB 或 BLOB 的长度施加任何长度限制（除了大型全局SQLITE_MAX_LENGTH限制）数值。
由于描述的词尾位“INT“，声明的“FLOATING POINT”类型将给出 INTEGER 关联性，而不是 REAL 关联性。并且声明的“STRING”类型具有 NUMERIC 亲和力，而不是 TEXT。

SQLITE的字段中如何保存浮点数

SQLITE的字段中如何保存浮点数的，例如1698925725.0340421，REAL数据类型用于存储浮点数。当你在SQLite数据库中定义一个REAL字段并存储一个浮点数值，如1698925725.0340421，SQLite会将这个浮点数转换为其内部二进制表示形式并存储它。

SQLite使用IEEE 754标准来表示浮点数，这是计算机科学中广泛使用的浮点数表示方法。当你存储1698925725.0340421这样的浮点数时，SQLite会将其转换为二进制格式并存储。

然而，由于浮点数的内部二进制表示和计算机硬件的浮点数运算精度限制，可能会出现精度损失。这意味着在某些情况下，读取存储的浮点数值时可能会略有舍入或精度损失。

在SQLite中，当你查询或检索REAL字段时，它会尝试以最接近的方式还原存储的浮点数值，但由于浮点数的精度限制，可能会出现与原始值略有不同的结果。

总之，当你在SQLite中存储浮点数时，它会将浮点数转换为其内部二进制表示形式并存储它，但由于浮点数的精度限制，可能会导致读取和存储的值略有不同。

浮点数值精度损失

在计算机科学中，浮点数的精度损失是一个普遍存在的问题，这是由于浮点数的内部二进制表示方式和计算机硬件的浮点数运算精度限制造成的。以下是一些可能导致浮点数精度损失的情况：

浮点数的内部表示方式：浮点数在计算机中使用二进制表示，而许多十进制浮点数（如0.1或0.01）在二进制中是无限循环的。这种十进制到二进制的转换可能会导致某些浮点数无法精确表示，具体参考之前的文章Python 杂记之 Python不认识0.1)。
计算和运算：在进行浮点数计算和运算时，由于舍入误差和算法的局限性，可能会导致结果的精度损失。例如，连续的浮点数运算可能会累积舍入误差，从而导致最终结果的精度降低。
浮点数的有限精度：计算机硬件和软件通常使用有限的位数来表示和处理浮点数。例如，单精度浮点数（float）使用32位，双精度浮点数（double）使用64位。这种有限的精度限制了浮点数的表示范围和精度，可能会导致某些浮点数无法精确表示。
浮点数格式和存储：在某些情况下，浮点数的格式和存储方式也可能影响其精度。例如，浮点数的四舍五入、截断或舍入误差可能会导致精度损失。
不同的计算机和操作系统：不同的计算机架构和操作系统可能使用不同的浮点数表示和计算方法，这也可能导致浮点数的精度损失。

丢失的精度0.0000001

具体到今天的问题问题中。存储数据时调试器显示的数据为1698925725.0340421，读取时为1698925725.034042，这里丢失的0.0000001应该是浮点数的内部表示方式导致的。

当存储一个十进制小数，如1698925725.0340421，作为浮点数时，它会被转换为其二进制浮点数表示形式并存储在数据库中。然而，由于计算机中浮点数的有限精度和内部表示方式，这个转换过程可能会导致一些精度损失。

在此的情况下，这种精度损失导致了最后的0.0000001（或更准确地说，可能是更小的精度差异）在读取时丢失，使得读取的值为1698925725.034042而不是原始的1698925725.0340421。

这种精度损失是浮点数在计算机中表示和处理时普遍存在的问题，特别是当涉及到非常接近的浮点数值时。为了避免或减少这种精度损失，你可以考虑使用更高的精度数据类型、进行适当的舍入或使用其他技术来处理浮点数。

精度损失的解决方法

要解决浮点数精度损失的问题，可以考虑以下几种方法：

使用更高的精度数据类型：可以使用SQLite中的DOUBLE或REAL（64位双精度浮点数）类型来存储浮点数，而不是使用FLOAT（32位单精度浮点数）。DOUBLE或REAL提供了更高的精度，这可以减少精度损失。
1
2
3
CREATE TABLE your_table (
your_column DOUBLE
);
存储为整数：如果可能的话，可以将浮点数乘以一个足够大的数（例如10的n次方，其中n是你想要的小数位数），然后将结果四舍五入到最接近的整数，并存储为整数。在检索时，可以将整数除以相同的数以获取原始的浮点数值。
1
2
3
4
5
-- 存储
INSERT INTO your_table (your_column) VALUES (ROUND(1698925725.0340421 * 10000000));

-- 检索
SELECT your_column / 10000000.0 FROM your_table;
使用字符串存储：还可以考虑将浮点数转换为字符串并存储它，然后在检索时将其转换回浮点数。这样可以避免浮点数精度问题，但可能会增加存储和处理数据的复杂性。
1
2
3
4
5
-- 存储
INSERT INTO your_table (your_column) VALUES ('1698925725.0340421');

-- 检索
SELECT CAST(your_column AS REAL) FROM your_table;
四舍五入或舍入到指定的小数位数：在存储浮点数之前，可以选择将其四舍五入或舍入到指定的小数位数，以减少精度损失。

选择哪种方法取决于具体需求和应用场景。如果精度是非常重要的，建议使用更高的精度数据类型（如DOUBLE或REAL）或将浮点数存储为整数，并在需要时进行适当的转换。如果精度要求不是很高，也可以考虑使用四舍五入或舍入到指定的小数位数来减少精度损失。

具体到项目中的问题，由于该字段储存的为时间戳，可以再分钟基本上使用。在容忍一些精度损失情况下，应用时可以将两个条目中的该字段时间戳乘以100（或其他适当的倍数）然后转换为整数进行比较。

小结

文中内容为个人总结，有不足之处，也望各位不吝赐教，多多指正，一起讨论学习。