3

I have a MySQL table with column of type float(10, 6).

If I insert 30.064742 into the column the value stored in the database is 30.064741.

Why?

PetrB
  • 31
  • 1
  • 3
  • 1
    See: http://stackoverflow.com/questions/618535/what-is-the-difference-between-decimal-float-and-double-in-c – cofiem Aug 26 '10 at 13:13
  • 2
    Usual story about [floating-point numbers](http://stackoverflow.com/questions/713763/strange-results-with-floating-point-comparison)? – pascal Aug 26 '10 at 13:14
  • Ok, the answer is simple. But why would google suggest to store these kind of numbers as float if it's not accurate enough? See: http://code.google.com/intl/cs/apis/maps/articles/phpsqlajax.html – PetrB Aug 26 '10 at 13:16
  • 3
    I guess it depends on what is "accurate enough"? In that example, they are storing latitude and longitude. Being off by 1 in the sixth decimal point may be good enough for their application. – Ned Batchelder Aug 26 '10 at 13:20
  • Its an obolus for the gods of science... – Yves M. Aug 26 '10 at 13:21
  • I needed to compare those numbers. I'll use some "epsilon" instead of exact comparing. Is it ok? How big the epsilon should be? – PetrB Aug 26 '10 at 13:24

2 Answers2

4

Floating-point numbers imply a certain amount of imprecision. Use a DECIMAL column if you need to be certain to retain every digit.

Ned Batchelder
  • 364,293
  • 75
  • 561
  • 662
2

It's a general problem with rounding numbers to a precision which can be stored in the database. Floats will round to multiples of powers of two. If you want something that is easier to think about, you can use the Decimal type, which will round to powers of ten.

More details in the documentation for numeric types:

When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the allowable number of digits.)

Douglas
  • 36,802
  • 9
  • 76
  • 89