SQL/MariaDB SELECT * FROM ... WHERE decimal number-CodePudding

I am having an issue with the SELECT statement. I want to do this:

SELECT * FROM `table1` WHERE x=0.0509

but it returns no rows. I know for sure that there is a row with an x value of 0.0509. If I do:

SELECT * FROM `table1` WHERE x=0

It does in fact return all rows where x=0, this means that my sql statement overall is correct. How do I SELECT where x is a number with decimals? I assume that the decimals is causing the issue?

CodePudding user response：

maybe try balancing the search term between 2 values like this:

SELECT * FROM table1 WHERE x BETWEEN 0 AND 0.1;

This worked for me :)

Photo here

Edit: I can't find any way to get the precise decimal but you can probably just do this but make the values:

SELECT * FROM table1 WHERE x BETWEEN 0.05089999 AND 0.05090001;

This is probably your best way to do it, I'm pretty sure

CodePudding user response：

The FLOAT is an approximate value. So, the stored value is not exactly 0.0509. You should use DECIMAL instead.

You can always convert the value to DECIMAL.

SELECT * 
FROM table1 
WHERE cast(x as decimal(10,4))=0.0509