mysqldatabasemysql-workbenchworkbenchmysql-error-1264

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

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!

Solution

  • 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