I've encountered a perplexing issue while working with floating point values in MySQL. Specifically, I'm attempting to store and retrieve a floating point value with seven significant digits. However, when I fetch the data using a plain SELECT statement, the returned value appears to be rounded off. Conversely, when I utilize the ROUND or FORMAT functions, I receive the exact, unrounded value.
I've read some related discussion but most seem to be about suggesting the use of decimal type, but don't mention the reason for the discrepancy.
Here's a demonstration of my problem using a basic SQL command sequence:
- Table Creation:
mysql> CREATE TABLE IF NOT EXISTS `demo`
(
`id` INT(10) NOT NULL auto_increment,
`value` FLOAT,
PRIMARY KEY (`id`)
);
- Inserting Value:
mysql> INSERT INTO demo (value) VALUES (2998877.0);
- Retrieving Value (Returns Rounded Off Value):
mysql> SELECT value FROM demo;
+---------+
| value |
+---------+
| 2998880 |
+---------+
1 row in set (0.00 sec)
- Retrieving Value with ROUND Function (Returns Exact Value):
mysql> SELECT round(value) FROM demo;
+--------------+
| round(value) |
+--------------+
| 2998877 |
+--------------+
1 row in set (0.00 sec)
Given that the ROUND function returns the accurate value, it seems to indicate that the value is stored precisely. Therefore, my main question is: why does the SELECT statement, without any accompanying functions, yield a rounded value instead of the precise one? Any insights into this issue would be greatly appreciated.
default precision is 6 digits
in the TiDB docs but nothing similar in the MySQL docs. It does seem like odd behaviour.