mysql float data not selecting in where clause

This maybe an easy on开发者_如何学编程e but i couldn't get answer. I need to select float value from table

example table :-

value
10.2
4.5
4.6
4.06

my query

SELECT * FROM table where value = '4.6'

returns empty result set

how can i solve this !

Generally, you should never check equality with floats (unless, potentially, you have the same object). Internally, it is represented with more precision, even if it isn't showing it to you by the time it outputs to the screen. This basic tenet holds true for computing in general.

There are a dozens of schemes for doing this, but here is a simple one, which should make sense:

SELECT * FROM table where value BETWEEN 4.599 AND 4.601

Use decimal instead of float.

A decimal(10,2) will have 2 and only 2 decimal places and can be compared in the same manner as integers. Especially for monetairy values you should always use decimal, but anywhere where rounding errors are unwanted, decimal is a good choice.

See: http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

Or MySQL DECIMAL Data Type Characteristics

Today, I also came across the same situation and get resolved just by using FORMAT function of MySQL, It will return the results that exactly match your WHERE clause.

SELECT * FROM yourtable WHERE FORMAT(`col`,2) = FORMAT(value,2)

Explanation:

FORMAT('col name',precision of floating point number)

Hope it helps.

You can also try query

SELECT * FROM table WHERE value LIKE 4.6;

you write:

 SELECT * FROM table where round(value, 1) = 4.6