Mysql Error 1264: Out of Range Value for Column

MySQL Error 1264: out of range value for column

The value 3172978990 is greater than 2147483647 – the maximum value for INT – hence the error. MySQL integer types and their ranges are listed here.

Also note that the (10) in INT(10) does not define the "size" of an integer. It specifies the display width of the column. This information is advisory only.

To fix the error, change your datatype to VARCHAR. Phone and Fax numbers should be stored as strings. See this discussion.

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

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:

1. M is the maximum number of digits (the precision). It has a range of 1 to 65.
2. 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;

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

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

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

If the data type is DECIMAL(5,2), this means five digits total, two of which are to the right of the decimal point. So the greatest value you can use is 999.99, and the least value is -999.99. Those are the most you can fit in five digits.

If you don't use strict mode, this generates a warning and truncates the value you tried to use to five digits:

mysql> insert into irctc_cap values (296,-1000,128);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'amount' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from irctc_cap;
+------+---------+------+
| lid  | amount  | type |
+------+---------+------+
|  296 | -999.99 |  128 |
+------+---------+------+

If you use strict mode, the warning becomes an error:

mysql> set sql_mode=strict_all_tables;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into irctc_cap values (296,-1000,128);
ERROR 1264 (22003): Out of range value for column 'amount' at row 1

I'm not sure why phpMyAdmin is showing the wrong data type.

Use DESCRIBE or SHOW CREATE TABLE or SELECT column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='irctc_cap' AND COLUMN_NAME='amount' to get the accurate definition of the column.

Why am I getting #1264 - Out of range value for column 'Identity_no' at row 1?

The INTEGER type is a 32 bit signed int, which means numbers bigger than 2^31 cannot be stored in a column with this type.
Use BIGINT instead:

CREATE TABLE Members(
Member_id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
Identity_no BIGINT NOT NULL UNIQUE,
Member_Name varchar(80) NOT NULL,
Member_Surname varchar(80) NOT NULL,
Member_Phone CHAR(11),
MemberCityID INTEGER NOT NULL,
FOREIGN KEY(MemberCityID) REFERENCES Cities(City_id));

Why am I getting error 1264: Out of range value for column 'ping' at row 1?

Type:

Floating-Point Types

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, (M,D) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point.

So: float(10,10) does not allow for storing 48.68. Before decimal point could be only 0.

db<>fiddle demo

---

Related Topics