CODEWITHSUNDEEP
mysql
user2133369
user2133369

Reputation: 31

MySQL Warning #1264 Out of range

I have a profile database, and I have selected a column of int(10) to store the phone number. So when I enter the 10 digit phone number, it returns a warning #1264, saying the value is out of range.

I increased the int(250) but I still get the same error. Why??!

Thanks

Upvotes: 2

Views: 7024

Answers (3)

WoodrowShigeru
WoodrowShigeru

Reputation: 1594

I increased the int(250) …

No you didn't: for all integer types, that value does not increase the field size – only the display width if the field also has the ZEROFILL flag.

CREATE TABLE `ints` (
  `tinyint1` tinyint(1) unsigned zerofill DEFAULT NULL,
  `tinyint4` tinyint(4) unsigned zerofill DEFAULT NULL,
  `int11` int(11) unsigned zerofill DEFAULT NULL,
  `bigint30` bigint(30) unsigned zerofill DEFAULT NULL
);

INSERT INTO ints VALUES (1, 1, 1, 1);
INSERT INTO ints VALUES (5, 5, 5, 5);
INSERT INTO ints VALUES (10, 10, 10, 10);
INSERT INTO ints VALUES (100, 100, 100, 100);

INSERT INTO ints VALUES (10212072467628961, 10212072467628961, 10212072467628961, 10212072467628961);
ERROR 1264 (22003): Out of range value for column 'tinyint1' at row 1

INSERT INTO ints VALUES (0, 0, 0, 10212072467628961);

SELECT * FROM ints;
+----------+----------+-------------+--------------------------------+
| tinyint1 | tinyint4 | int11       | bigint30                       |
+----------+----------+-------------+--------------------------------+
|        1 |     0001 | 00000000001 | 000000000000000000000000000001 |
|        5 |     0005 | 00000000005 | 000000000000000000000000000005 |
|       10 |     0010 | 00000000010 | 000000000000000000000000000010 |
|      100 |     0100 | 00000000100 | 000000000000000000000000000100 |
|        0 |     0000 | 00000000000 | 000000000000010212072467628961 |
+----------+----------+-------------+--------------------------------+
5 rows in set (0.01 sec)

As the other guys have suggested, you have to use a different integer type:

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

The only way to "increase" the effective range is by using the UNSIGNED flag and omitting all negative values – but that's technically a shifting of the range, not an increase. Technically.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116100

The largest value for an int field is 2147483647. Make it a BIGINT, or use a VARCHAR field if you need even bigger values. It is quite common to use a textual fields (varchar) for phone numbers anyway.

See: http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

Upvotes: 2

Filippos Karapetis
Filippos Karapetis

Reputation: 4652

You are storing a phone as an integer, which has an upper limit. The maximum value for 32-bit signed integers is 2147483647, so if your phone number is larger than that, you'll get an out of range warning. I'd suggest to change your field to a VARCHAR with a size of 10, as an integer isn't a good field type to represent phone numbers.

Upvotes: 3

Related Questions