mysql decimal query issue
First off I am running mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0
Sorry for the formatting but I don't have the ability to copy it so I have to type it in.
the two fields in question are the following
id int(11)
startj1950 decimal(38,6)
mysql> select id,startj1950 from store where id = 32513;
+------------------+
| id | startj1950 |
+----------|----------------------|
|32513 | 1912181654.500000 |
-----------------------------------
mysql> select id from store where startj1950=1912181654.500000;
Empty set
So I figured I was messing up somehow in the copy so I would let mysql get the value for me.
mysql> select id from store where startj1950=(select startj1950 from store where id=32513);
Empty set <br/>
But it works for other results
mysql> select id,startj1950 from store where id = 32513;
+------------------+
| id | startj1950 |
+--------开发者_如何学JAVA--|----------------------|
|18675 | 1907365784.570000 |
-----------------------------------
mysql> select id from store where startj1950=1907365784.570000;
+----------+
| id |
+----------|
|18675 |
------------
mysql> select id from store where startj1950=(select startj1950 from store where id=18675);<br/>
+----------+
| id |
+----------|
|18675 |
------------
I figure I either have hit a mysql bug or I am misunderstanding some sort of concept. Thanks in advance for the help.
decimal (38, 6) will be a floating-point type, with all the difficulties associated with that type. In this particular case, it means that some of the values displayed are not exact values. The size of that column leads me to suspect the underlying type is already double-precision, so to enquire on this column you need to do something like...
SELECT *
FROM store
WHERE startj1950 BETWEEN somevalue - @delta AND somevalue + @delta;
and fiddle with the value of delta until you get the desired results, or redesign the column so that you can use INTEGER or CHAR types.
精彩评论