Mysql浮点数float和double

#1

小数2位，位数不多，则可以用float其他情况，尽量用decimal

对于单精度浮点数Float:  当数据范围在±131072（65536×2）以内的时候，float数据精度是正确的，但是超出这个范围的数据就不稳定，没有发现有相关的参数设置建议：将float改成double或者decimal，两者的差别是double是浮点计算，decimal是定点计算，会得到更精确的数据。

 

#2 尽量指定精度和有效位数

 

 

看官方说的：

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

For FLOAT, the SQL standard permits an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. MySQL also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in a 4-byte single-precision FLOAT column. A precision from 24 to 53 results in an 8-byte double-precision DOUBLE column.

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)”means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies. For more information, see Section B.5.5.8, “Problems with Floating-Point Values”

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

 

也就是我们平时用的 FLOAT(M,D)  是用来让mysql去决定怎么存储的。怪不得好问题出来。

 

不适用于精度计算。但我们一般用来存储，也够了。

 

 FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

 

4字节，范围：-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38

最多只有7位有效小数位就占满了存储空间。

 

要得到1位或2位精确小数的话，整数不能高于 32767

即：f<32767.99

因为 2E15=32768

 所以最多只能正确处理0~32767的整数，

 

要得到3位 精确 小数的话，整数不能高于16383

即：f< 16383 .999

因为 2E14=16384 

 

 

参考：

http://blog.csdn.net/afternoone/article/details/4183556

http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

http://blog.csdn.net/a3z2008/article/details/6675593

mysql下float类型使用一些误差详解http://www.jb51.net/article/31723.htm