快捷導(dǎo)航

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

更新時間：2012年11月03日 22:04:34 作者：

我想很多朋友都不怎么會在mysql中使用float類型，特別是用到金錢時我們可能會用雙精度來做，我們知道m(xù)ysql的float類型是單精度浮點類型不小心就會導(dǎo)致數(shù)據(jù)誤差

單精度浮點數(shù)用4字節(jié)（32bit）表示浮點數(shù)
采用IEEE754標(biāo)準(zhǔn)的計算機(jī)浮點數(shù)，在內(nèi)部是用二進(jìn)制表示的
如：7.22用32位二進(jìn)制是表示不下的。
所以就不精確了。

mysql中float數(shù)據(jù)類型的問題總結(jié)

對于單精度浮點數(shù)Float: 當(dāng)數(shù)據(jù)范圍在±131072（65536×2）以內(nèi)的時候，float數(shù)據(jù)精度是正確的，但是超出這個范圍的數(shù)據(jù)就不穩(wěn)定，沒有發(fā)現(xiàn)有相關(guān)的參數(shù)設(shè)置建議：將float改成double或者decimal，兩者的差別是double是浮點計算，decimal是定點計算，會得到更精確的數(shù)據(jù)。

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

復(fù)制代碼代碼如下:

 
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

注：超出字段范圍，無法插入

代碼如下復(fù)制代碼
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)

注：小數(shù)位數(shù)不夠，自動補(bǔ)齊，但是存在一個問題就是如上的近似值。

復(fù)制代碼代碼如下:

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)

注：小數(shù)位數(shù)超了，自動取近似值。

一、浮點數(shù)的概念及誤差問題

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

二、mysql 和 oracle中的數(shù)值類型

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

三、編程中也存在浮點數(shù)問題

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

四、總結(jié)：

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

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

注意事項

MYSQL 5.022中,
如果某個字段 f是float類型,那么在查詢的時候,sql語句為:
select * from T where f = 2.2;
那么即使表中有2.2的數(shù)據(jù)也不能被查詢到.

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

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

您可能感興趣的文章: