将 numeric 转换为数据类型 numeric 时出现算术溢出错误。_使用Float或Real数据类型的危险...

桃扇骨 2022-10-24 01:57 907阅读 0赞

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。本文介绍了使用Float或Real数据类型的危险。

**点击下载SQL Prompt正式版**

浮点数据类型可容纳非常大的数字，但是缺少了精度。它们对于某些类型的科学计算很方便，但是在更广泛地使用时很危险，因为它们会引入较大的舍入误差。

浮点运算就是为了避免计算中的溢出错误而容忍和管理近似。在现实世界中，我们通常关心数字的准确性，而会牺牲空间和资源以避免溢出。

科学是在误差范围内工作的，而精确在商业会计中至关重要。当我还是一名初级程序员时，我曾经写过一种我认为是银行计算交易利润非常合适的方法。在一百万英镑中，最多也就一两便士的误差，我很满意。它使用了我们当时用来开发财务软件包的PL / 1编译器中固有的计算。我向他们展示了精心制作的应用程序，他们感到震惊。冷酷无情的银行家们毫不留情地表示一百万英镑没了几分钱。他们不会接受的。我被迫用精确的汇编代码编写一个精确的二进制编码的十进制（BCD）程序包。

SQL Prompt具有代码分析规则（BP023），该规则将提醒您使用Float或Real数据类型，这是因为它们可能会引入许多组织通常在其SQL Server数据上常规执行的那种计算方式。

![17f1647c53ecf1eaf73799a4615312c5.png][]

**近似数的数据类型**

浮点运算是在优先考虑节省内存的同时，提供了一种涉及大量运算的通用方法的时代设计出来的。尽管它对于许多类型的科学计算（尤其是那些符合浮点算术双精度IEEE 754标准的科学计算）仍然有用，但它必然是一种折衷方案。线索就是这种数据和算术的名称：“近似”。浮点数不能精确表示所有实数：此外，浮点运算不能精确表示所有算术运算。但是，即使不总是精确地保留数字，它们可以保留的数字的幅度范围也远大于其他数字类型。

使用浮点运算引起的问题是由于复杂计算过程中的四舍五入而引起的，如果数据处于“不良条件”状态，则最常见的问题就是输入中的细微变化会在输出中放大。随着数字表示精度的提高，这种不精确性已经不那么明显了，但是它们仍然存在。在使用有效但不能用浮点数表示的数字时，还存在一些深奥的限制，例如tan（π/ 2），但这些可能仅会激发数学家的兴趣。

**SQL Server浮点数据类型**

SQL标准具有三个浮点，近似数据类型、REAL、DOUBLE PRECISION和FLOAT（n）。 SQL Server符合此要求，只是它没有DOUBLE PRECISION数据类型，而改用FLOAT（53）。 FLOAT（24）和FLOAT（53）数据类型对应于IEEE 754标准中的**Binary32（Single）**和**Binary64（double）**，并分别存储在4和8字节中，并分别保留7和16位数字。当计算产生与使用还使用IEEE 754的.NET框架的应用程序相同的结果很重要时，它们很有用。当数字的大小超过DECIMAL数据类型所允许的最大值（38位）时，还需要双精度类型，但精度下降。当然，近似数不能可靠地用于任何相等性检验中，例如WHERE子句。

**使用REAL数据类型的计算（单精度）**

我将尝试REAL数据类型。FLOAT(24)数据类型或更小的数据类型以相同的方式反应。在SQL Server中使用浮点数进行实验时，要记住的第一件事是，SSMS以掩盖微小差异的方式呈现浮点数。例如：

SELECT Convert(REAL,0.100000001490116119384765625)

…得到0.1

为了更准确地看到浮点数中存储了什么值，您必须使用STR（）函数，指定实际需要的精度。

![aa8d1605a2f70e305309ef7c0f180d75.png][]

![fd1085f9ec39874ea0f5cb68f446b9df.png][]

这已经令人担忧。毕竟，我们正在处理具有数百万行的数据，因此，除非像“银行家四舍五入”之类的结果取平均值，否则小错误就会堆积起来。这个错误已经接近我在引言中提到的“百万英镑的便士”（1/240000000）！

让我们避免使用0.1，并将其归结为奇怪的浮点数。1除以3怎么样？这肯定不是问题吧？

![f0f1afe740091b46a82067c6e543824e.png][]

哎呀，它错了。好的，这是一个很小的错误，但请记住我关于银行家的故事。答案是对还是错，穿着灰色西装的男人没有灰色阴影。在商学院，只有一个标记和一个叉。没有表示“足够近”的标志。

一个简单的测试是将数字1除以1到20。会出什么问题呢？

我们可以存储浮点数和数值计算的结果，将它们都转换为字符串，然后比较字符串（请注意，字符串STR()可以放在前导空格中，这会使情况变得复杂）。

![6c984c93dfb8238a18c5afe22ed5958e.png][]

![730c76053811faafaabf015851c6b910.png][]

现在，如果我们列出那些数字不匹配的行呢？

![b4a61959e8271d8b3257f74e338753dc.png][]

![04bdec85ac961527153b8e2703c10375.png][]

啊! 只有在除数为1、2、4、8或16的情况下，结果才正确。

如果您希望某种程度上的浮点数是准确的，而数值版本却不正确，则以下是在Excel中计算出的数值商：

![48e4c04878b575e79e20db6e014764db.png][]

使用FLOAT（25）或更高（双精度）的计算

如果使用双精度浮点数FLOAT(25)或更高的精度，则所有测试都将通过，因为STR()函数最多允许小数点右边16位。如果大于16，则结果将被截断。双精度数据类型具有16位数字，而单精度数据类型具有7位数字。您还将看到单精度数据类型正确获取了前七个数字。同样，双精度会正确获取前16位数字。我们可以扩大数字以查看近似值。

DECLARE @FirstApproximate FLOAT(53) = 10000000000000000.1
    SELECT Str(@FirstApproximate,40,16) AS BigNumberWithaDecimal

![81ede31c9d329161320834cc951b0d3a.png][]

那小部分消失了，不是吗？这可能只是微小的差异，但是在某些计算中，它可能会引起问题。

**结论**

浮点算法在存储上既快速又经济，但提供了近似的结果。它适用于条件良好的科学应用，但不适用于财务计算，因为财务计算要求数字是“正确”或“错误”。它在数据库中还具有额外的缺点，因为您不能可靠且一致地测试两个近似数是否相等。

说永远不要在SQL数据类型或算术中使用浮点数是不正确的。在SQL标准中，有一个特定的近似类型。如今，在有适当要求的SQL Server中，我始终坚持使用双精度浮点数据类型。它们非常适合用于建模天气系统或绘制轨迹等目的，但不适用于普通组织可能使用数据库的计算类型。

如果发现错误使用了这些类型，则应改用合适的DECIMAL/ NUMERIC类型。如果您知道需要浮点算法并可以解释原因，那么您可能足够了解避免浮点的陷阱。

[17f1647c53ecf1eaf73799a4615312c5.png]: /images/20221024/18ef206884d24901bfc9089cc1ea9cfc.png
[aa8d1605a2f70e305309ef7c0f180d75.png]: /images/20221024/b7542ab601a94c0e9978b3d0783eb843.png
[fd1085f9ec39874ea0f5cb68f446b9df.png]: /images/20221024/acd0a78ff9fc45388b176e4803133cbf.png
[f0f1afe740091b46a82067c6e543824e.png]: /images/20221024/3408eb16efe34ffbbcb0545a7afaa211.png
[6c984c93dfb8238a18c5afe22ed5958e.png]: /images/20221024/674a42c4d2cd4698a9fc429be8db3d09.png
[730c76053811faafaabf015851c6b910.png]: /images/20221024/7635946a929249e086bd4eac66b7768c.png
[b4a61959e8271d8b3257f74e338753dc.png]: /images/20221024/0eb9530287114e2a967d0efc3638fb4a.png
[04bdec85ac961527153b8e2703c10375.png]: /images/20221024/2069090c1b204964aed46f15a0f46c55.png
[48e4c04878b575e79e20db6e014764db.png]: /images/20221024/55fae63936cd430f911e101f862b51c5.png
[81ede31c9d329161320834cc951b0d3a.png]: /images/20221024/f3b22153557d476c896e1e0eac1e1482.png