MySql not saving correct float value

user_5652970 0
Viewed 40

I have tickets table with nat_curr_amount of type float. Why is it not saving correct value for higher values?

mysql> update tickets set nat_curr_amount = 60.5 where id = 19;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select nat_curr_amount from tickets where id = 19;
+-----------------+
| nat_curr_amount |
+-----------------+
| 60.5            |
+-----------------+
1 row in set (0.00 sec)

mysql> update tickets set nat_curr_amount = 600004.5 where id = 19;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select nat_curr_amount from tickets where id = 19;
+-----------------+
| nat_curr_amount |
+-----------------+
| 600004          |
+-----------------+
1 row in set (0.00 sec)

mysql> update tickets set nat_curr_amount = 6000004.5 where id = 19;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select nat_curr_amount from tickets where id = 19;
+-----------------+
| nat_curr_amount |
+-----------------+
| 6000000         |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql
1 Answers

Don't confuse the output with the actual value that is stored.

From MySQL manual:

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values

You can use format() or cast() to decimal to verify the correct values stored.

create table tickets (
id int, 
nat_curr_amount  float );
Insert into tickets  values
(19, 20);


update tickets set nat_curr_amount = 600004.5 where id = 19;


select nat_curr_amount from tickets where id = 19;

Result:

    nat_curr_amount
    600004

select format(nat_curr_amount, 3) from tickets where id = 19;

Result:

format(nat_curr_amount, 3)
 600,004.500

For precise calculation I suggest using decimal rather than float.

Check Problems with Floating-Point Values

user_164619520
Related