float precision problem in mysql

Question

Can anybody, given example below, make to me an explanation how FLOAT works in mySQL? I know that float is approximative, but really, such a difference? And there is only 9 digits, so it is not overflow problem, isn't 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 Distrib 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 is the value, the less is the absolute precision.

512659648 is large enough to introduce errors in tens.

Update:

In IEEE-754 (that's what FLOAT is), the data are stored in 32 bits: 1-bit sign, 8-bit binary exponent and 23-bit significand.

The exponent shows you the closest least power of 2 (28 in your case, or 268435456).

The significand is a binary fraction. It can store numbers from 1 to 2 with precision up to 2^-23. In your case, it's 1.11101000111010010000110, or ~ 1.9098060 in decimal notation.

The number is calculated as a product of the exponent and the significand.

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