How To Match A Float Column In MySQL

MySQL Logo 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:

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:

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.

2 thoughts on “How To Match A Float Column In MySQL

  1. Amine says:

    I’m a not an expert in MySQL but would it be wrong to do:

    SELECT * FROM table WHERE float_column LIKE 1.3 ?

  2. Sourab says:

    Hi Lee!

    Thanks for your explanation .It works perfectly .

     

Leave a Reply

Your email address will not be published. Required fields are marked *