mysql下float類型使用一些誤差詳解

單精確度浮點數用4位元組（32bit）表示浮點數
採用IEEE754標準的電腦浮點數，在內部是用二進位表示的
如：7.22用32位二進位是表示不下的。
所以就不精確了。

mysql中float資料類型的問題總結

對於單精確度浮點數Float: 當資料範圍在±131072（65536×2）以內的時候，float資料精度是正確的，但是超出這個範圍的資料就不穩定，沒有發現有相關的參數設定建議：將float改成double或者decimal，兩者的差別是double是浮點計算，decimal是定點計算，會得到更精確的資料。

1.float類型
float列類型預設長度查不到結果，必須指定精度，
比如 num float, insert into table (num) values (0.12); select * from table where num=0.12的話，empty set。

複製代碼 代碼如下:num float(9,7), insert into table (num) values (0.12); select * from table where num=0.12的話會查到這條記錄。

mysql> create table tt
-> (
-> num float(9,3)
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tt(num)values(1234567.8);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

註：超出欄位範圍，無法插入

代碼如下 複製代碼
mysql> insert into tt(num)values(123456.8);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
+------------+
| num |
+------------+
| 123456.797 |
+------------+
1 row in set (0.00 sec)

註：小數位元不夠，自動補齊，但是存在一個問題就是如上的近似值。

複製代碼 代碼如下:mysql> insert into tt(num)values(123456.867);
Query OK, 1 row affected (0.04 sec)

mysql> select * from tt;
+------------+
| num |
+------------+
| 123456.797 |
| 123456.797 |
| 123456.867 |
+------------+
3 rows in set (0.00 sec)

mysql> select * from tt where num=123456.867;
+------------+
| num |
+------------+
| 123456.867 |
+------------+
1 row in set (0.00 sec)

mysql> insert into tt(num)values(2.8);
Query OK, 1 row affected (0.04 sec)

mysql> select * from tt;
+------------+
| num |
+------------+
| 123456.797 |
| 123456.797 |
| 123456.867 |
| 2.800 |
+------------+
4 rows in set (0.00 sec)

mysql> select * from tt where num=2.8;
+-------+
| num |
+-------+
| 2.800 |
+-------+
1 row in set (0.00 sec)

mysql> insert into tt(num)values(2.888888);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
+------------+
| num |
+------------+
| 123456.797 |
| 123456.797 |
| 123456.867 |
| 2.800 |
| 2.889 |
+------------+
5 rows in set (0.00 sec)

註：小數位元超了，自動取近似值。

一、浮點數的概念及誤差問題

浮點數是用來表示實數的一種方法，它用 M(尾數) * B( 基數)的E(指數）次方來表示實數，相對於定點數來說，在長度一定的情況下，具有表示資料範圍大的特點。但同時也存在誤差問題，這就是著名的浮點數精度問題！浮點數有多種實現方法，電腦中浮點數的實現大都遵從 IEEE754 標準，IEEE754 規定了單精確度浮點數和雙精確度浮點數兩種規格，單精確度浮點數用4位元組（32bit）表示浮點數，格式是：1位符號位 8位表示指數 23位表示尾數 雙精確度浮點數8位元組（64bit）表示實數，格式是：1位符號位 11位表示指數 52位表示尾數 同時，IEEE754標準還對尾數的格式做了規範：d.dddddd...，小數點左面只有1位且不能為零，電腦內部是二進位，因此，尾數小數點左面部分總是1。顯然，這個1可以省去，以提高尾數的精度。由上可知，單精確度浮點數的尾數是用24bit表示的，雙精確度浮點數的尾數是用53bit表示的，轉換成十進位：
2^24 - 1 = 16777215； 2^53 - 1 = 9007199254740991
由上可見，IEEE754單精確度浮點數的有效數字二進位是24位，按十進位來說，是8位；雙精確度浮點數的有效數字二進位是53位，按十進位來說，是16 位。顯然，如果一個實數的有效數字超過8位，用單精確度浮點數來表示的話，就會產生誤差！同樣，如果一個實數的有效數字超過16位，用雙精確度浮點數來表示，也會產生誤差！對於 1310720000000000000000.66 這個數，有效數字是24位，用單精確度或雙精確度浮點數表示都會產生誤差，只是程度不同：
單精確度浮點數：1310720040000000000000.00；雙精確度浮點數： 1310720000000000000000.00
可見，雙精確度差了 0.66 ，單精確度差了近4萬億！
以上說明了因長度限制而造成的誤差，但這還不是全部！採用IEEE754標準的電腦浮點數，在內部是用二進位表示的，但在將一個十進位數轉換為二進位浮點數時，也會造成誤差，原因是不是所有的數都能轉換成有限長度的位元。對於131072.32 這個數，其有效數字是8位，按理應該能用單精確度浮點數準確表示，為什麼會出現偏差呢？看一下這個資料二進位尾數就明白了 10000000000000000001010001...... 顯然，其尾數超過了24bit，根據舍入規則，尾數只取 100000000000000000010100，結果就造成測試中遇到的“奇怪”現象！131072.68 用單精確度浮點數表示變成 131072.69 ，原因與此類似。實際上有效數字小於8位的數，浮點數也不一定能精確表示，7.22這個數的尾數就無法用24bit二進位表示，當然在資料庫中測試不會有問題（舍入以後還是7.22），但如果參與一些計算，誤差積累後，就可能產生較大的偏差。

二、mysql 和 oracle中的數實值型別

問題是不是只有 mysql 存在呢？顯然不是，只要是符合IEEE754標準的浮點數實現，都存在相同的問題。
mysql中的數實值型別（不包括整型）：
IEEE754浮點數：float（單精確度），double或real（雙精確度）
定點數：decimal或numeric
oracle中的數實值型別：
oracle 浮點數 ：number（注意不指定精度）
IEEE754浮點數：BINARY_FLOAT（單精確度），BINARY_DOUBLE（雙精確度）FLOAT，FLOAT(n) （ansi要求的資料類型）
定點數：number(p,s)
如果在oracle中，用BINARY_FLOAT等來做測試，結果是一樣的。因此，在資料庫中，對於涉及貨幣或其他精度敏感的資料，應使用定點數來儲存，對mysql來說是 decimal，對oracle來說就是number(p,s)。雙精確度浮點數，對於比較大的資料同樣存在問題！

三、編程中也存在浮點數問題

不光資料庫中存在浮點數問題，編程中也同樣存在，甚至可以說更值得引起注意！
通過上面的介紹，浮點數的誤差問題應該比較清楚了。如果在程式中做複雜的浮點數運算，誤差還會進一步放大。因此，在程式設計中，如果用到浮點數，一定要意識到可能產生的誤差問題。不僅如此，浮點數如果處理不好，還會導致程式BUG！看下面的語句：if (x != y) { z = 1 / (x -y);}這個語句看起來沒有問題，但如果是浮點數，就可能存在問題！再看下面的語句會輸出什麼結果： public class Test { public static void main(String[]args) throws Exception { System.out.print("7.22-7.0=" + (7.22f-7.0f)); } } 我們可能會想當然地認為輸出結果應該是 0.22 ，實際結果卻是 0.21999979 !
因此，在編程中應盡量避免做浮點數的比較，否則可能會導致一些潛在的問題！除了這些，還應注意浮點數中的一些特殊值，如 NaN、+0、-0、+無窮、-無窮等，IEEE754雖然對此做了一些約定，但各具體實現、不同的硬體結構，也會有一些差異，如果不注意也會造成錯誤！

四、總結：

從上面的分析，我們可以得出以下結論：

1、浮點數存在誤差問題；
2、對貨幣等對精度敏感的資料，應該用定點數表示或儲存；
3、編程中，如果用到浮點數，要特別注意誤差問題，並盡量避免做浮點數比較；
4、要注意浮點數中一些特殊值的處理

注意事項

MYSQL 5.022中,
如果某個欄位 f是float類型,那麼在查詢的時候,sql語句為:
select * from T where f = 2.2;
那麼即使表中有2.2的資料也不能被查詢到.

此時解決方案有2種:
1.將float改為double類型,不會出現這種問題.但是如果資料庫中資料量龐大,或者修改量太大,則不適合這個方法.這個方法只適合設計資料庫的初期階段.
2.設定float的精度然後進行查詢就可以了.
如果要精確到3位,則:select * from T where format(f,3) = format(2.2,3);

但是,精度不能超過6.否則出錯.因為float類型最多允許精確到小數點後6位.