Alex Poca Alex Poca - 5 months ago 36
SQL Question

MySQL (MariaDB) float visualization: why are values rounded to hundreds or thousands?

First: I am a MySql newbie and it looks like I am missing something important here.

In a MySQL database my float values range from units to billions.
I spent days trying to understand why all of them showed no more than 6 significant cyphers with the less significant ones set to 0:

i.e.:

select `field` from `table`;
(instead of X -> i get Y)
1 -> 1
12 -> 12
123 -> 123
1234 -> 1234
12345 -> 12345
123456 -> 123456
1234567 -> 1234570
12345678 -> 12345700
123456789 -> 123457000


Only with a "trick" I found somewhere I could read the real values:

select `field`+0.0 from `table`;


My questions: what is the rationale behind this (imho) strange behavior? and where is it described in the documentation?
I find completely non-intuitive that I don't see the real value (of course approximated according to IEEE specifications) with the normal select * but I need the trick... What am I missing here?

Answer
  • A FLOAT has 24 bits, or log10(224) (over 7) digits, of significance: 12345678.910 is stored in a FLOAT as 1011110001100001010011112 (which is 1234567910). When displaying as decimal, the client knows for certain that everything from the 8th digit onwards is definitely false precision (since a FLOAT was only capable of storing 7 decimal digits of precision). It therefore discards the remainder, leaving you with 12345680.

  • A DOUBLE has 53 bits, or log10(253) (almost 16) digits, of significance: 12345678.910 is stored in a DOUBLE as 101111000110000101001110.111001100110011001100110011012 (which is 12345678.9000000003710). When displaying as decimal, the client knows for certain that everything from the 16th digit onwards is definitely false precision (since a DOUBLE was only capable of storing 15 decimal digits of precision). It therefore discards the remainder, leaving you with 12345678.9000000.

When adding +0.0, MySQL first casts the FLOAT up to a DOUBLE.

Comments