Comparing double values in mysql not seems to be working properly

Question

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

you mean if you do : score = 0.61 nothing will be shown even if you have a record with score — Charif DZ, Apr 05 '17 at 07:40

score 1 · Accepted Answer · answered Apr 05 '17 at 07:28

1

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.

answered Apr 05 '17 at 07:28

Rahul

18,271
7
41
60

If I use decimal it automatically append 0 in remaining decimal range, for example `0.61` becomes `0.6100` and I don't need that – Vivek Maru Apr 05 '17 at 07:31
cast('score' AS CHAR) <=> '0.61' try this – Rahul Apr 05 '17 at 07:33

score 1 · Answer 2 · answered Apr 05 '17 at 08:09

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 ?!!

Comparing double values in mysql not seems to be working properly

2 Answers2