MySQL Error Code: 1264. Out of range value for column 'columnname' at row 1

Question

I'm using MySQL to store a 20-digit ID number. When I query the database with the following query, I get the following error.

Query:

UPDATE tablename SET columnname = 59641217344615859740;

Error:

Error Code: 1264. Out of range value for column 'columnname' at row 1

Table Info:

Engine: InnoDB
Row Format: Dynamic
Table Collation: utf8mb4_general_ci

Column Info:

Type: BIGINT(255)
Nullable: Yes
Privileges: Select, Insert, Update, References

What am I doing wrong? Is there something wrong with my query? Maybe with the table or column settings? Most others who have this error just aren't using a column type such as BIGINT but I am. An answer is much appreciated. Thanks!

Answer 1

You've reached the MAXIMUM VALUE of the data type you used.

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. 
As an extension to the standard, MySQL also supports the integer types 
TINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage 
and range for each integer type.

Type Storage Minimum Value Maximum Value

        (Bytes) (Signed/Unsigned)   (Signed/Unsigned)
TINYINT   1           -128           127
                       0             255
SMALLINT    2     -32768            32767
                     0               65535
MEDIUMINT   3     -8388608           8388607
                      0              16777215
INT         4   -2147483648          2147483647
                      0              4294967295
BIGINT       8  -9223372036854775808    9223372036854775807
                        0             18446744073709551615

Answer 2

Your value:          59641217344615859740
Max value of BIGINT:  9223372036854775807

Read https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

It makes no difference that you use BIGINT(255). The argument is only a hint for display width, it does not affect the range of values you can store in a 64-bit signed integer.

Answer 3

it looks like you are storing a value to big for your BIG INT column (5.9E+19> MAX 9.2E+18)!

If you look at MySQL documentation:

https://dev.mysql.com/doc/refman/5.5/en/integer-types.html

You have the following MAX/MIN values:

SIGNED BIGINT MIN=-9223372036854775808 MAX=9223372036854775807

UNSIGNED BIGINT MIN=0 MAX= 18446744073709551615

Last but not least, I would recommend to read the following link were MySQL Error Code 1264 is defined and explained with examples:

https://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html