3
In a field mysql is stored 104.13 format Float;
With this select, I can see the record:
SELECT * FROM parcelas WHERE valor like '104%';
But with this select does not appear:
SELECT * FROM parcelas WHERE valor = '104.13';
How do I select values FLOAT ?
6
The type of Float data in Mysql is inherently inaccurate. If you are planning to use a data type float for a column in your database, should reconsider, especially if you are planning to use it to store monetary values.
Attempts to treat floating point values as comparisons exact can cause problems. Source: B.5.4.8 Floating point value problems - Mysql documentation
Some possible ways
Converting to decimal with CAST:
SELECT * FROM parcelas WHERE CAST(valor as decimal(5,2)) = 104.13
Using the ROUND:
SELECT * FROM tabela WHERE ROUND(valor,2) = 104.13
This link will take away virtually all your doubts about the use of float:
What is the correct way to use the float, double and decimal types?
A few more:
https://answall.com/questions/37947/valor-decimal-mysql/37952#37952
https://answall.com/questions/176745/problemas-com-inser%C3%A7%C3%A3o-de-dados-em-campo-float-no-mysql
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
@RBZ thus returns values that are "near", not only 104.13
– Pedro Augusto
@Diegorafaelsouza 0 results returned
– Pedro Augusto
@Diegorafaelsouza seems to me that the field is text, so the quotes and the quote in the question "float-shaped", so no single quotes should not work.
– Ricardo Pontual
Actually, it’s Float itself, but I can’t select the record
– Pedro Augusto
Tried with
ROUND? Because the float may not be returning identical value.– rbz
If your field is text, it may be space problem, you tried
WHERE valor like '104.13%'?– Ricardo Pontual
B.5.4.8 Problems with Floating-Point Values
– rbz
If it is
floatquotes are unnecessary, and @Diegorafaelsouza’s suggestion should work well– Ricardo Pontual
Try:
SELECT * FROM parcelas WHERE CAST(valor as decimal(5,2)) = 104.13– rbz
WHERE valor like '104.13%'so it works, even so I’m weirding this behavior– Pedro Augusto
so tbm works
SELECT * FROM parcelas WHERE CAST(valor as decimal(5,2)) = 104.13, But PQ this ????– Pedro Augusto
Here’s a related answer. Depending on how the record was entered, it might actually be the floating point problem mentioned by @RBZ.
– Diego Rafael Souza
Realmete @Diegorafaelsouza, what RBZ said is true, I was able to verify the problem with this fiddle: http://sqlfiddle.com/#! 9/69728/9 And only happens in
MySQLon the other Dbs works– Ricardo Pontual
@Ricardopunctual It should work can be by pure accident. The same problem can happen in other banks with other values. I believe the discrepancy is related to the low-level implementation of each DBMS or the architecture.
– Diego Rafael Souza