Select Float Mysql

Posted on

Question :

In mysql field is stored 104.13 in format Float ;
With this select, I can see the record:

SELECT * FROM parcelas WHERE valor like '104%';

But with this select it does not appear:

SELECT * FROM parcelas WHERE valor = '104.13';

How do I select values FLOAT ?


Answer :

The Float data type in MySQL is inherently inaccurate.
If you are planning to use a float data type for a column in your database, you should reconsider , especially if you are planning to use it to store monetary values. p>


Attempts to treat floating-point values as comparisons
  can cause problems.
  Source: B.5.4.8 Problems with floating-point values – MySQL documentation

Some possible ways

Converting to decimal with CAST :

SELECT * FROM parcelas WHERE CAST(valor as decimal(5,2)) = 104.13

Using ROUND :

SELECT * FROM tabela WHERE ROUND(valor,2) = 104.13

SQL Fiddle

This link will take away virtually all of your questions about using float :

What is the correct way to use float, double, and decimal types?

Some more:

MySQL Float

Floating-point arithmetic – Wikipedia


Leave a Reply

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