SQL field type field not shows up

I have a MySQL table in which one of the fields is variation and type is float. Its value is 0.2. But when I query like WHERE variation =开发者_运维问答 '0.2' it is not showing the result, instead it returns null. What I am doing wrong?

SELECT * FROM tbl_products
  WHERE frequency BETWEEN '2' AND '3'
    AND gain = '2'
    AND gain_variation = '0.2'
    AND noise = '2'
    AND power = '10'

---

Remove the quotes around the number a try again.

eg WHERE variation = 0.2

Edit: As Peeka pointed out, the quotes don't matter in MySQL.

Are you absolutely sure the value of the float is exactly 0.2 and there aren't significant digits that are getting rounded? Can you try using BETWEEN 0.19 AND 0.21 and seeing whether the row you're after gets returned (I know it's not an equivalent query and may return unwanted results but it will test whether the value is exactly 0.2 or whether it's rounded in the client).

Edit 2: I'd wager it's because of the way MySQL stores floats - taken from their manual:

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally.

See here for more info: http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

It is recommended to use a range to filter instead of an equality, so if you use BETWEEN 0.19 AND 0.21 do you get the result you want?

---

Per the discussion on @mwan's answer, consider using an INT for this field instead of FLOAT. When you INSERT a row you can multiply by a certain power of ten (depending on the level of precision you need), and when you SELECT you can divide by the same power of ten, but since the field is an INT you can always be confident that your WHERE clause will match the correct rows:

CREATE TABLE `tbl_products` (
  frequency             INT,
  gain                  INT,
  gain_variation_x100   INT,  # give it a name that will remind you
  noise                 INT,
  power                 INT
);

INSERT INTO `tbl_products` VALUES
  ('1', '2', '0.2' * 100, '2', '10'),
  ('2', '2', '0.2' * 100, '2', '10'),
  ('2', '2', '0.5' * 100, '2', '10')
  #                ↑
  #                 `---- multiply here
;

SELECT *, ROUND(gain_variation_x100 / 100, 1) AS gain_variation
  FROM tbl_products  #              ^---.  ^---- show 1 digit of precision
  WHERE frequency BETWEEN '2' AND '3' #  \
    AND gain = '2'                    #   `----- divide here
    AND gain_variation_x100 = ROUND('0.2' * 100)
    AND noise = '2'  #                    ^----- multiply here
    AND power = '10'
;

You can see this working here: http://sqlize.com/829275n1Fc

---

This explains it

http://dev.mysql.com/doc/refman/5.0/en/precision-math-examples.html