0
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?

  • what is the problem exactly? – Tom V Jun 27 '15 at 17:45
  • I should be able to insert values till 10e25 right? – Rahul Jun 27 '15 at 17:46
4

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

1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.