Posted by Lazeez Jiddan on 09/13/06 22:26

On Mon, 4 Sep 2006 11:42:02 -0400, ZeldorBlat wrote
(in article <1157384522.367120.232690@b28g2000cwb.googlegroups.com>):

>
> 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?

Thanks, that actually makes more sense.

I didn't notice the DECIMAL column type until you mentioned it.

It works very well.

Although, with char(4) it was the same. No calculations were ever going
to be applied to the value read from that column. The column was simply a
store and search and display only.

[Back to original message]