Mysql Data truncation for Valid DECIMAL

Question

create table test(id INT, value DECIMAL(25,6));
insert into test values (16, 10e16);
insert into test values (17, 10e17);  -- Works till here
insert into test values (18, 10e18);  -- Fails here. Data truncation: Out of range value for column 'value'
insert into test values (19, 10e19);

How is this possible?

Answer 1

A DECIMAL(25,6) stores 25 digits, 6 of them fractional, i.e. a value up to 9,999,999,999,999,999,999.999999

10e18 is 10,000,000,000,000,000,000.000000

Answer 2

What is DECIMAL(25,6) ?

• 19 digits to the left of the decimal
• 06 digits to the right of the decimal

10e18 is not a 19-digit number. It's a 20-digit number

mysql> select 10e18 ActualValue;
+-------------+
| ActualValue |
+-------------+
|        1e19 |
+-------------+
1 row in set (0.00 sec)

mysql>

That's 1 with 19 zeros.

Therefore, a 20-digit number will not fit inside the 19 digits imposed by DECIMAL(25,6)