MYSQL: DECIMAL with accuracy of 10 digits after the comma

Question

In MySQL I have a DECIMAL field set to 10,10. However, whenever I enter a new value, it shows 0.9999999999. What would I need to change to accept any number with an accuracy of 10 digits after the comma? It does work with 10,6 for some reason.

PS: I want to insert exchange rates.

Solution 1

The first number is the total number of digits to store, the second one is the number of digits on the fractional part. So DECIMAL (10, 10) is only for 10 fractional digits. You can use something like DECIMAL (20, 10) to allow 10 digits on both the integral and fractional parts.

Solution 2

DECIMAL(10,10) means there's no decimal places left for values before the decimal place. You're limiting things to always being x < 1.

It's DECIMAL(total number of digits, digits after the decimal). With 10,10, you're saying "10 digits after the decimal", leaving 10-10 = 0 before the decimal. This means you cannot store 1, and 0.9999999999 is the nearest value that fits within your field definition.

Solution 3

The answer from @Xint0 is correct. You've set the precision and scale to the be same number of digits, so you can only insert values less than 1.0.

The other thing that's going on is MySQL default behavior of truncating values if they don't fit in the data type for the column.

mysql> CREATE TABLE foo (dec DECIMAL(10,10));
mysql> INSERT INTO foo VALUES (1.0);
Query OK, 1 row affected, 1 warning (0.00 sec)

Note this generates a warning.

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1264 | Out of range value for column 'dec' at row 1 |
+---------+------+----------------------------------------------+

mysql> SELECT * FROM foo;
+--------------+
| dec          |
+--------------+
| 0.9999999999 |
+--------------+

You can turn the warning into an error with the strict SQL mode:

mysql> SET SQL_MODE = STRICT_ALL_TABLES;
mysql> INSERT INTO foo VALUES (1.0);
ERROR 1264 (22003): Out of range value for column 'dec' at row 1

Comments

[Frank Vilea]

Thanks for your explanation, I always thought the first part is for the number of digits before and the second part for those after the comma.