Float與Real數據類型的陷阱

SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。本文介紹了使用Float或Real數據類型的危險。

浮點數據類型可容納非常大的數字，但是缺少了精度。它們對于某些類型的科學計算很方便，但是在更廣泛地使用時很危險，因為它們會引入較大的舍入誤差。

浮點運算就是為了避免計算中的溢出錯誤而容忍和管理近似。在現實世界中，我們通常關心數字的準確性，而會犧牲空間和資源以避免溢出。

科學是在誤差范圍內工作的，而精確在商業會計中至關重要。當我還是一名初級程序員時，我曾經寫過一種我認為是銀行計算交易利潤非常合適的方法。在一百萬英鎊中，最多也就一兩便士的誤差，我很滿意。它使用了我們當時用來開發財務軟件包的PL / 1編譯器中固有的計算。我向他們展示了精心制作的應用程序，他們感到震驚。冷酷無情的銀行家們毫不留情地表示一百萬英鎊沒了幾分錢。他們不會接受的。我被迫用精確的匯編代碼編寫一個精確的二進制編碼的十進制（BCD）程序包。

SQL Prompt具有代碼分析規則（BP023），該規則將提醒您使用Float或Real數據類型，這是因為它們可能會引入許多組織通常在其SQL Server數據上常規執行的那種計算方式。

近似數的數據類型

浮點運算是在優先考慮節省內存的同時，提供了一種涉及大量運算的通用方法的時代設計出來的。盡管它對于許多類型的科學計算（尤其是那些符合浮點算術雙精度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（）函數，指定實際需要的精度。

這已經令人擔憂。畢竟，我們正在處理具有數百萬行的數據，因此，除非像“銀行家四舍五入”之類的結果取平均值，否則小錯誤就會堆積起來。這個錯誤已經接近我在引言中提到的“百萬英鎊的便士”（1/240000000）！

讓我們避免使用0.1，并將其歸結為奇怪的浮點數。1除以3怎么樣？這肯定不是問題吧？

哎呀，它錯了。好的，這是一個很小的錯誤，但請記住我關于銀行家的故事。答案是對還是錯，穿著灰色西裝的男人沒有灰色陰影。在商學院，只有一個標記和一個叉。沒有表示“足夠近”的標志。

一個簡單的測試是將數字1除以1到20。會出什么問題呢？

我們可以存儲浮點數和數值計算的結果，將它們都轉換為字符串，然后比較字符串（請注意，字符串STR()可以放在前導空格中，這會使情況變得復雜）。

現在，如果我們列出那些數字不匹配的行呢？

啊! 只有在除數為1、2、4、8或16的情況下，結果才正確。

如果您希望某種程度上的浮點數是準確的，而數值版本卻不正確，則以下是在Excel中計算出的數值商：

使用FLOAT（25）或更高（雙精度）的計算

如果使用雙精度浮點數FLOAT(25)或更高的精度，則所有測試都將通過，因為STR()函數最多允許小數點右邊16位。如果大于16，則結果將被截斷。雙精度數據類型具有16位數字，而單精度數據類型具有7位數字。您還將看到單精度數據類型正確獲取了前七個數字。同樣，雙精度會正確獲取前16位數字。我們可以擴大數字以查看近似值。

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

那小部分消失了，不是嗎？這可能只是微小的差異，但是在某些計算中，它可能會引起問題。

結論

浮點算法在存儲上既快速又經濟，但提供了近似的結果。它適用于條件良好的科學應用，但不適用于財務計算，因為財務計算要求數字是“正確”或“錯誤”。它在數據庫中還具有額外的缺點，因為您不能可靠且一致地測試兩個近似數是否相等。

說永遠不要在SQL數據類型或算術中使用浮點數是不正確的。在SQL標準中，有一個特定的近似類型。如今，在有適當要求的SQL Server中，我始終堅持使用雙精度浮點數據類型。它們非常適合用于建模天氣系統或繪制軌跡等目的，但不適用于普通組織可能使用數據庫的計算類型。

如果發現錯誤使用了這些類型，則應改用合適的DECIMAL/ NUMERIC類型。如果您知道需要浮點算法并可以解釋原因，那么您可能足夠了解避免浮點的陷阱。