CODEWITHSUNDEEP
mysqldatabase
Vivek Maru
Vivek Maru

Reputation: 8499

Comparing double values in mysql not seems to be working properly

When I compare a float value in where clause it does not give proper results. for example

SELECT * FROM users WHERE score = 0.61

in this query score is a column of double type

but the above query works if I check the score to be 0.50 nothing else is being searched while I have records with 0.61 too

The above query also work if i use 

SELECT * FROM users WHERE trim(score) = 0.61

Upvotes: 2

Views: 1395

Answers (2)

Charif DZ
Charif DZ

Reputation: 14721

If you didn't did not specify the decimal range in your float column i will not work without casting or trim:

this works fine :

-- drop table test_float;
create table test_float(f float(6,4) , d DECIMAL(4,2));
insert into test_float values (0.5,0.5);
insert into test_float values (0.61,0.61);
select * from test_float where f = d;
select * from test_float where f = 0.61;

this don't work :

drop table test_float;
create table test_float(f float , d DECIMAL(4,2));
insert into test_float values (0.5,0.5);
insert into test_float values (0.61,0.61);
select * from test_float;
select * from test_float where f = d;
select * from test_float where f = 0.61;
select * from test_float where CAST(f as DECIMAL(16,2)) = 0.61;

it work for decimal range range = 1 why , I really don't know why ?!!

Upvotes: 1

Rahul
Rahul

Reputation: 18557

I suggest you to use decimal instead of float. And it also have 2 decimal places only.

Here is the documentation on how to use it. Link.

I hope this will solve your problem.

Upvotes: 1

Related Questions