I Have table with column of decimal(20, 2) unsigned. I am trying modify this column (increase fraction part precision):

alter table table_name modify column column_name decimal(20, 18) unsigned;

But receiving error:

[22001][1264] Data truncation: Out of range value for column 'amount' at row 1

And what strange - I don't have row with such id - first id starts much latter.

So why I receiving error about row 1, or row 329 (for decimal(20, 16)) if I don't even have such rows? Is it some internal representation, or something that vacuum failed to remove, so I don't see it, but it is exists?

And why truncation appears when I am just increasing precision of fraction part?

CodePudding user response：

You misunderstand how the DECIMAL type declaration works.

The first number is the total number of digits allowed.

The second number is how many of the total digits are to be to the right of the decimal place.

By declaring DECIMAL(20,18) this does not increase the number of digits. It forces 18 of the 20 digits allowed to be to the right of the decimal place.

So the maximum value supported by such a column is 99.999999999999999999.