MySQL ERROR 1264 (22003): Out of range value for column

Question

I get this error:

ERROR 1264 (22003): Out of range value for column 'median_comments' at row 1

after running this query:

update influencers set `median_comments` = 1347 WHERE `id` = 1;

I'm not sure why this fails on this number which doesn't have any decimals and which is only 4 digits.

The field type is:

median_comments   decimal(10,8)

It seems like your `median_comments` column's data type is `TINYINT` and you're storing value that does not fit in its range. — Dark Knight, Sep 28 '20 at 02:56
Does this answer your question? [MySQL Error 1264: out of range value for column](https://stackoverflow.com/questions/14284494/mysql-error-1264-out-of-range-value-for-column) — Dark Knight, Sep 28 '20 at 03:02

score 4 · Accepted Answer · edited Sep 28 '20 at 03:58

4

You are using DECIMAL(10,8) that means max digits before decimal would be (10 - 8) = 2.

Reference: DECIMAL Data Type Characteristics

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65.

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

To fix the error, change your datatype to DECIMAL(10,2).

ALTER TABLE `influencers`
    CHANGE COLUMN `median_comments` `median_comments` DECIMAL(10,2) NOT NULL DEFAULT 0;

edited Sep 28 '20 at 03:58

Mitch Wheat

288,400
42
452
532

answered Sep 28 '20 at 03:02

Dark Knight

5,626
1
11
32

I'm using DECIMAL(10,8) – Steve Sep 28 '20 at 03:02
same issue with that too. – Dark Knight Sep 28 '20 at 03:03
Thanks, I just figured it out. Thank you for helping! – Steve Sep 28 '20 at 03:06

score 0 · Answer 2 · answered Sep 28 '20 at 03:08

If you are using decimal(10,8) as data type it means you are specifying 8 digits after decimal place which leaves only (10 - 8 i.e 2 digits) for your whole number.

In this case since your number 1347 contains 4 digits (whole number) hence you are getting the error as "Out of range value" since you are allowed only 2.

You should consider changing it to at least decimal (12,8) which will leave you 4 digits for your whole number part and your above command should work.

Please refer to post - Number format issue in Oracle. Same issue.

MySQL ERROR 1264 (22003): Out of range value for column

2 Answers2