Skip navigation links

User Comments

Posted by Mohamed Infiyaz Zaffer Khalid on June 1 2010 12:11pm[Delete] [Edit]

I've just implemented a PHP-MySQL-based application and it took me a while to figure this out. I hope all of you coders out there will benefit from this tip.

In PHP, I calculated a value that arrives at the amount 20072.64 and I wrote this into a mysql field of type FLOAT.

Strangely (despite the technical specs indicating a large range), the number that got stored was 20072.6 - note truncation!

Solution: I changed the field type to DOUBLE and this was resolved.

Alternatives that did not work: Even type-casting in PHP did not do any good since ultimately the values had to be stored by MySQL.

Happy coding!
Khalid

Posted by Felipph Calado on December 29 2010 12:52pm[Delete] [Edit]

I had this problem too. It's happen sometimes randomly. My solution was expecify colunm to float(10,2) with 2 decimals. This looks solve my problem.

Anyway I will try double fields

Posted by Geoffrey Downs on March 10 2011 9:27am[Delete] [Edit]

Khalid -
This is not a mystery. The problem is that Float columns only store 4-bytes per entry. This means that the precision available to the decimal portion of your number depends on the size of the non-decimal portion of your number. The more bytes are requires to represent the non-decimal portion of your number, the fewer bytes are available to represent the approximate decimal value of your number. If you store a sufficiently large number, your entire decimal value will be truncated to 0. You have solved the problem by increasing your per-entry storage to 8 bytes instead of 4.

Posted by Geoffrey Downs on March 10 2011 10:26am[Delete] [Edit]

Following up... I *think* this is correct for the default float columns in mysql:

var yourNumber = some floating point value
max decimal precision = 10 ^ (-5 + floor(yourNumber log 10))
So:
0 < x < 10 -> max precision is 0.00001
10 <= x < 100 -> max precision is 0.0001
100 <= x < 1000 -> max precision is 0.001
etc.

Posted by Peter Soltesz on March 26 2012 7:15am[Delete] [Edit]

Geoffrey Downs is right!

Selecting a tolerance level is not good, because the tolerance level differs from value to value depending on the number. As I inspected duplicates in my db for example two float stored values both 13442 compared as NOT EQUAL to each other when using too high (0.01) tolerance level, however they were EQUAL when I used lower (0.1) tolerance level.

Therefore I also recommend to change the documentation because the recommended solution (compare the difference to a selected threshold) is not safe.

I translated the equation of Geoffrey Downs to MySQL as follows for FLOAT values:

IF(ABS(yourFloat1-yourFloat2)<POW(10,FLOOR(LOG10(GREATEST(ABS(yourFloat1),ABS(yourFloat2)))-5)),"E Q U A L","N O T - E Q U A L")