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 8 · Accepted Answer · edited Sep 28 '20 at 03:58

8

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

295,962
43
465
541

answered Sep 28 '20 at 03:02

Dark Knight

6,116
1
15
37

I'm using DECIMAL(10,8) – Stephane 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! – Stephane 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.

Serhii Popov · Answer 3 · 2022-10-04T09:55:51.977

As you like me came here from google and your issue is related to Doctrine, and your column type is type="decimal", then you should configure precision and scale properties of your column in the right way.

For me, it was like before:

/** @ORM\Column(name="revenue", type="decimal", scale=4, nullable=true) */
private $revenue;

after

/** @ORM\Column(name="revenue", type="decimal", precision=14, scale=4, nullable=true) */
private $revenue;

It will be converted to DECIMAL(14,4), which means fourteen digits total, four of which are to the right of the decimal point.

Don't forget to prepare migration and run it to apply the changes.

Finally, you should get SQL like this:

ALTER TABLE project_finance CHANGE revenue revenue NUMERIC(14, 4) DEFAULT NULL

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

3 Answers3