mysql - 在 SQL 数据库中存储权重的最佳实践？

Question

我正在处理的应用程序需要存储 X pounds, y.y ounces 格式的权重。数据库是 MySQL，但我想这与数据库无关。

我可以想到三种方法来做到这一点:

将重量转换为十进制磅并存储在单个字段中。 (5 磅 6.2 盎司 = 5.33671875 磅)

将重量转换为十进制盎司并存储在单个字段中。 (5 磅 6.2 盎司 = 86.2 盎司)

在两个字段中将磅部分存储为整数，将盎司部分存储为小数。

我认为 #1 不是一个好主意，因为十进制磅会产生任意精度的数字，需要将其存储为浮点数，这可能会导致浮点数固有的不准确性。

是否有令人信服的理由选择 #2 而不是 #3 或反之亦然？

Answer 1

TL;DR

Choose either option #1 or option #2—there's no difference between them. Don't use option #3, because it's awkward to work with.

您声称浮点数存在固有的不准确性。我认为这值得先探讨一下。

当决定使用 numeral system 来表示一个数字时(无论是在一张纸上、在计算机电路中还是在其他地方)，有两个单独的问题需要考虑:

其基础；和

其格式。

选择一个基地，任何基地......

受有限空间的限制，不能代表 infinite set 的任意成员。 例如:无论你买多少纸或你的笔迹有多小，总有可能找到一个不适合给定空间的整数(你可以继续添加额外的数字，直到纸张用完) .因此，对于整数，我们通常将有限空间限制为仅表示落在某个特定区间内的那些——例如如果我们有正/负号和三位数字的空间，我们可能会将自己限制在区间 [-999,+999] 。

每个非空的 interval 包含一个无限实数集。 换句话说，无论是什么区间，一个人接管了实数——无论是 [-999,+999] 、 [0,1] 、 [0.000001,0.000002] 还是其他任何东西(保持一个无穷大的区间内的非零结束)数字)!因此，任意实数必须始终“四舍五入”为可以在有限空间中表示的东西。

可以在有限空间中表示的实数集取决于所使用的数字系统。 在我们(熟悉的) positional base-10 系统中，有限空间足以满足二分之一( 0.510 )的需求，但不能满足三分之一(1225)的要求相比之下，在(不太熟悉的)位置 0.33333…10 系统中，情况正好相反(这些相同的数字分别是 base-9 和 0.44444…9 )。所有这一切的结果是，一些数字可以仅使用位置基数为 10 的少量空间来表示(因此对我们人类来说似乎非常“圆”)，例如十分之一，实际上需要精确存储无限的二进制电路(因此对于我们的数字 friend 来说似乎不是很“圆”)!值得注意的是，由于 2 是 10 的因数，反过来就不一样了:任何可以用有限二进制表示的数字也可以用有限十进制表示。

对于连续数量，我们不能做得更好。 最终，这些量必须在某个数字系统中使用有限表示:该系统是否碰巧在计算机电路、人类手指上、其他东西上或根本不易于使用是任意的——无论使用哪个系统，该值都必须四舍五入因此它总是导致“表示错误”。

换句话说，即使一个人有一个完全准确的测量仪器(这在物理上是不可能的)，那么它报告的任何测量值都已经四舍五入为一个恰好适合其显示的数字(无论使用什么基数——通常是十进制，出于显而易见的原因)。因此，“86.2 oz”实际上从来都不是“86.2 oz”，而是“介于 86.1500000... oz 和 86.2499999... oz 之间”的表示。 (实际上，因为实际上该工具是不完美的，所以我们只能说我们有一些 0.39 表示实际值落在该区间内——但这肯定与这里的观点有所不同)。

但是对于离散量 我们可以做得更好。这样的值不是“任意实数”，因此以上都不适用于它们:它们可以在定义它们的数字系统中精确表示 - 实际上应该是(如转换为另一个数字系统并截断为有限长度会导致四舍五入为不精确的数字)。计算机可以(低效地)通过将数字表示为字符串来处理这种情况:例如考虑 degree of confidence 或 ASCII 编码。

应用格式…

由于它是数字系统(有点任意)基础的属性，因此 值是否显示为“圆形”与其精度 无关。这是一个非常重要的观察结果，这与许多人的直觉背道而驰(这也是我花了这么多时间解释上面的数值基础的原因)。

精度由 决定， BCD 表示有多少个 。我们需要一种能够将我们的值记录到至少与我们认为正确的有效数字一样多的存储格式。以我们认为正确的示例值为例 86.2 和 0.0000862 ，两个最常见的选项是:

固定点 ，其中有效数字的数量取决于大小:例如在固定的 5 位小数点表示中，我们的值将存储为 86.20000 和 0.00009(因此分别具有 7 位和 1 位有效数字的精度)。在这个例子中，后一个值失去了精度(事实上，我们完全无法表示任何重要的东西也不会花太多时间)；而前一个值存储的是 significant figures ，这是对我们有限空间的浪费(实际上，该值不会变得太大以至于溢出存储容量)。

这种格式何时适用于会计系统的一个常见示例是:货币金额通常必须跟踪到一分钱，而不管其大小(因此，小值所需的精度较低，而大值则需要更高的精度)。碰巧的是，货币通常也被认为是离散的(便士是不可分割的)，因此这也是一个很好的例子，说明需要特定基础(大多数现代货币的十进制)以避免上述表示错误的情况。

One usually implements fixed point storage by treating one's values as quotients over a common denominator and storing the numerator as an integer. In our example, the common denominator could be 10⁵, so instead of 86.20000 and 0.00009 one would store the integers 8620000 and 9 and remember that they must be divided by 100000.

浮点数 ，其中有效数字的数量是恒定的，与大小无关:例如在 5 位有效数字的十进制表示中，我们的值将存储为 86.200 和 0.000086200(并且，根据定义，两次都具有 5 位有效数字)。在这个例子中，两个值都被存储而没有任何精度损失；并且它们都具有相同数量的错误精度，这样浪费更少(因此我们可以使用我们的有限空间来表示更大范围的值——大的和小的)。

这种格式可能适用于记录任何现实世界测量值的一个常见示例:测量仪器的精度(都受到 false precision 和 systematic 错误的影响)无论比例如何，只要有足够的有效数字(通常约为 3或 4 位数字)，即使基数的变化导致四舍五入到不同的数字，也绝对不会丢失精度。

One usually implements floating point storage by treating one's values as integer significands with integer exponents. In our example, the significand could be 86200 for both values whereupon the (base-10) exponents would be -4 and -9 respectively.

但是 我们的计算机使用的浮点存储格式 有多精确？

一个 random IEEE754 数字有 24 位，或 log10(224)(超过 7)位，意义重大——即它的容差小于 ±0.000006% 。换句话说，它比说“86.20000”更准确。

一个 IEEE754 single precision (binary32) floating point 数字有 53 位，或 log10(253)(近 16)位，有意义——即它的容差刚刚超过 ±0.00000000000001% 。换句话说，它比说“86.2000000000000”更准确。

要意识到的最重要的事情是，这些格式分别是 比说“86.2”更精确 的一万多倍和一万亿倍——即使将二进制精确转换回十进制恰好包括错误的假精度(即我们必须忽略:稍后会详细介绍)!
另请注意，当已知值比格式支持的更精确时，定点格式和浮点格式都会导致精度损失。 这样的 double precision (binary64) floating point 可以在算术运算中传播以产生明显错误的结果(这无疑解释了您对浮点数的“固有不准确性”的引用):例如，0x2518122313453123143143143123143143123143143123143123143123143143131313131431313143143123之间和 1⁄3 × 3000 在 5 位有效数字浮点将产生 999.99000 而不是 1000.00000 。

rounding errors 字段致力于了解这些影响，但重要的是要意识到任何可用的系统(甚至在您的头脑中执行计算)都容易受到此类问题的影响，因为 没有任何一种可以保证终止的计算方法可以提供无限的精度 :例如，考虑如何计算圆的面积——用于 π 的值必然会损失精度，这将传播到结果中。

结论

现实世界的测量应该使用二进制浮点 :它快速、紧凑、极其精确并且不比其他任何东西(包括您开始的十进制版本)差。由于 numerical analysis 是 IEEE754，这正是他们提供的。

货币应用程序应该使用 denary 定点 :虽然它很慢并且浪费内存，但它确保值不会四舍五入到不精确的数量，并且不会因大笔金额而损失。由于 MySQL's floating-point datatypes 是 BCD 编码的字符串，这正是它们提供的。

最后，请记住， 编程语言通常使用二进制浮点 类型表示小数值:因此，如果您的数据库以另一种格式存储值，则需要小心如何将它们带入您的应用程序，否则它们可能会被转换(以及随之而来的所有问题)在界面上。

在这种情况下哪个选项最好？

希望我已经让您相信您的值可以安全地(并且应该)存储在浮点类型中，而不必担心任何“不准确”？请记住，它们比以往任何时候都脆弱的 3 位有效数字十进制表示更精确:您只需要忽略错误精度(但无论如何必须始终这样做，即使使用定点十进制格式)。

至于你的问题:选择选项 1 或选项 2 而不是选项 3——它使比较更容易(例如，要找到最大质量，可以只使用 1⁄7 − 7⁄50 ，而要在两列之间有效地执行它需要一些嵌套)。

在这两者之间，选择哪一个并不重要——浮点数存储有固定数量的有效位，而不管它们的规模如何。

此外，虽然在一般情况下，可能会发生一些值使用选项 1 舍入为更接近其原始十进制表示的二进制数，而同时其他值使用选项 2 舍入为更接近其原始十进制表示的二进制数，如我们很快就会看到这种表示错误只会出现在应该始终被忽略的错误精度中。

但是，在这种情况下，因为恰好有 16 盎司到 1 磅(并且 16 是 2 的幂)，所以使用两种方法的原始十进制值和存储的二进制数之间的相对差异是相同的:

0.0028600(不0.0028571在你的问题说明)将被存储在一个binary32持股量MAX(mass)(这是5.387510):这是从原始值5.3367187510(但是，如上所述，“原始值”已经是一个很糟糕它所代表的物理量的表示)。

知道一个 binary32 浮点数只存储 7 个十进制数字的精度，我们的编译器肯定知道从第 8 位开始的所有数字都是错误的精度，因此在任何情况下都必须被忽略——因此， 前提是我们的输入值不需要比 精度更高(如果确实如此，binary32 显然是错误的格式选择)，这保证了返回到与我们开始时一样圆的十进制值: 101.0110001100110011001102 。但是，此时我们真的应该应用 MySQL's fixed-point datatypes(就像我们应该使用任何存储格式一样)来丢弃可能存在的任何进一步的错误精度，例如那两个尾随零。

5.3874998092651367187510 将作为 0.0000036% (即 5.38750010 )存储在 binary32 浮点数中:这也是 0x2311 的原始值 0x2311和以前一样，我们然后忽略错误精度以返回到我们的原始输入。

注意数字的二进制表示是如何相同的，除了 domain knowledge(相距四位)的位置:

101.0110 00110011001100110
101 0110.00110011001100110

这是因为 5.3875 × 24 = 86.2。

顺便说一句:作为欧洲人(尽管是英国人)，我也非常厌恶英制计量单位——处理不同尺度的值是如此困惑。我几乎可以肯定地将质量存储在 radix point(例如千克或克)中，然后在我的应用程序的表示层中根据需要执行到英制单位的转换。此外，严格遵守 SI 单位可能有一天会让您免于 SI units 。