Different results based on data type in MySQL

Asked Sep 20 '21 at 23:32

Active Sep 20 '21 at 23:36

Viewed 17 times

I get different results based on data type in mysql? I wonder why this is caused?

CREATE TABLE `testdb`.`typenumbers` ( 
             `id` INT(11) NOT NULL AUTO_INCREMENT , 
             `type_flo` FLOAT(10,10) NOT NULL , 
             `type_dbl` DOUBLE(10,10) NOT NULL , 
             `type_dec` DECIMAL(10,8) NOT NULL , 
             PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO `typenumbers` (`id`, `type_flo`, `type_dbl`, `type_dec`) VALUES (NULL, '1.2', '1.2', '1.2');

SELECT (1.1 * `type_flo`) as flo, (1.1 * `type_dbl`) as dbl, (1.1 * `type_dec`) as deci FROM `typenumbers`;

Result:
+--------------+--------------+-------------+
| flo          | dbl          | deci        |
+--------------+--------------+-------------+
| 1.3200000525 | 1.3200000000 | 1.320000000 |
+--------------+--------------+-------------+

edited Sep 20 '21 at 23:36

Gordon Linoff

1,242,037
58
646
786

asked Sep 20 '21 at 23:32

Şahin Ersever

2

Floating point and double numbers are not exact. That's the problem. – Gordon Linoff Sep 20 '21 at 23:37
1

https://blog.greglow.com/2018/01/15/sql-newbie-mistake-1-using-float-instead-decimal/ – Derple Sep 20 '21 at 23:39
Thank you very much indeed! – Şahin Ersever Sep 20 '21 at 23:41

Different results based on data type in MySQL

0 Answers0