How To Match A Float Column In MySQL

The Float datatype in MySQL is inherently inaccurate. If you are planning to use a float datatype for a column in your database you should reconsider, especially if you are planning to use it to store money values. Here is an example of the problem.

If you store the number 1.3 in a float column of your database table, MySQL actually stores the number 1.2999999523162842. You can see this for yourself by running this SQL command:

1	SELECT CAST(my_float_column AS DECIMAL(30, 16)) from my_table;

This means that it is difficult to match against float columns and calculations involving float values my become inaccurate due to rounding errors.

The following statements show the problem with matching:

1 2 3

INSERT INTO my_table (my_float_column) VALUES (1.3); SELECT * from my_table where my_float_column = 1.3; >> no results found

For this reason, it’s much better and more accurate if you can either use an Integer or Decimal datatype instead of a Float. If you are storing money values, you could store the values in cents as an Integer or you could store the value in dollars as a Decimal.

In the event that you absolutely must compose a query that matches against a Float column, here’s how to do it.

1	SELECT * FROM  your_table WHERE cast(my_float as decimal(5,1)) = 1.3;