Posted by ZeldorBlat on 10/14/22 11:57

Lazeez Jiddan wrote:
> On Fri, 1 Sep 2006 15:31:33 -0400, Andy Hassall wrote
> (in article <pb2hf2t9uaj55862mm7ogi9bhkrkkbm9k2@4ax.com>):
>
> > On Fri, 1 Sep 2006 11:26:09 -0400, Lazeez Jiddan
> > <lazeez@storiesonline.removethispart.net> wrote:
> >
> >> Is mysql unable to store 9.7 as 9.7 float and uses some kind of
> >> approximation?
> >
> > Yes, this is a common issue on computers. The IEEE standard for
> > floating point numbers only represents a subset of numbers; some
> > precision is traded for range.
> >
> > The closest you can get to 9.7 is 9.699999809265137.
> > The closest you can get to 9.8 is 9.800000190734863.
> >
> > See http://www.h-schmidt.net/FloatApplet/IEEE754.html
>
> I thought that was the issue, however, I also thought that, when
> selecting from a float type column MySQL would convert the comparison
> value also to float and would end up with the same value.
>
> So if I store 9.7 in a float column (MySQL actually stores
> 9.699999809265137) and then I search that column for 9.7, then MySQL
> would convert the 9.7 searched for to a float and would get
> 9.699999809265137, and comparing them both would get a match.
>
> Obviously, I was mistaken.
>
> >> if so, is there a way to get the results that I'm looking for
> >> without some stupid workaround like changing 9.7 (or whatever the
> >> score sought) to 9.69 (or whatever is close)?
> >
> > Multiply all your values by 100 and store in an integer format.
> > Divide them back down by 100 for display.
>
> Actually, I found something better (it doesn't need the constant and
> repeated conversion on the fly; the site is quite busy). Storing the
> values in a char(4) column. It's indexed either way, so it give back the
> expected results and I detected no slow down.
>
> If somebody knows of a drawback to using a char type column that I
> overlooked, I would love to hear it.
>
> Thank you for your reply.

The drawback is that you're storing numeric data using a datatype that
is meant for storing character data. Why not use an exact numeric type
like decimal?

[Back to original message]