Mysql float precision problem

Question

Can someone below give me an explanation of how FLOAT works in mySQL? I know that a float is approximative, but really, is that the difference? And there are only 9 digits, so this is not an overflow problem, is it?

mysql> create table t(f FLOAT(15,2), db DOUBLE); mysql> insert into t(f,db) VALUES (512659663, 512659663); mysql> select * from t; +--------------+-----------+ | f | db | +--------------+-----------+ | 512659648.00 | 512659663 | +--------------+-----------+ 

(mysql Ver 14.14 Distribution 5.1.44 for Win32 (ia32) on Windows XP)

Answer 1

FLOAT is a 32 bit type with, as the name suggests, floating point. The higher the value, the lower the absolute accuracy.

512659648 is large enough to introduce dozens of errors.

Update:

In IEEE-754 (which is what FLOAT ), data is stored in 32 bits: 1 bit sign, 8 bit binary exponent and 23 bit valueand.

The indicator shows the lowest power 2 ( 28 in your case or 268435456 ).

Significance is a binary fraction. It can store numbers from 1 to 2 with an accuracy of 2^-23 . In your case, this is 1.11101000111010010000110 , or ~ 1.9098060 in decimal notation.

The number is calculated as the product of the exponent and significance.

Given all this, accuracy 2 ^ (28 - 23) = 2 ^ 5 = 32 for numbers of this order ( 2^28 to 2^29 ).

Answer 2

In fact, in MySQL, FLOAT and DOUBLE are approximate floating point numbers. MySQL uses four bytes for single precision values ​​and eight bytes for double precision values.

So both columns are approximate - it's just that your FLOAT column only gets 4 bytes to try to get closer to your number.

Answer 3

The mantissa with the number of floating point points with one precision has a length of 22 bits. Therefore, it cannot accurately store an integer greater than 2 ^ 22, 4194304.