why is phone number not stored in integer datataype?

Question

mysql> desc Student;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| Roll_no | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(200) | YES  |     | NULL    |                |
| address | varchar(200) | YES  |     | NULL    |                |
| phone   | int(100)     | YES  |     | NULL    |                |
| age     | int(11)      | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> insert into Student values(1,'Ram','Delhi',90404444389,18);

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

why in error..phone size 100 but why out of range..

Because phone numbers if treated as number look like very large numbers Larger than an integer can hold maybe [See 11.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT](https://dev.mysql.com/doc/refman/8.0/en/integer-types.html) — RiggsFolly, Feb 21 '20 at 14:20
Does this answer your question? [How do I fix the 'Out of range value adjusted for column' error?](https://stackoverflow.com/questions/1786509/how-do-i-fix-the-out-of-range-value-adjusted-for-column-error) — Zeljka, Feb 21 '20 at 14:21
check https://dev.mysql.com/doc/refman/8.0/en/integer-types.html for integer range values, I suggest that you make it `varchar` instead! — ROOT, Feb 21 '20 at 14:22
And this one as well: https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html — PM 77-1, Feb 21 '20 at 14:22
I think you misunderstood the meaning of int(11). Int(11) does not mean that you can have an integer with eleven digits. Unlike the varachar(200) which means you can store maximum 200 characters. In fact, for Int type, the number inside the parentheses is telling you the size of one integer value and in this case it means one int value is stored in 4 bytes and the phone number you are using is way bigger than that. I would recommend to take at this https://stackoverflow.com/questions/4151259/whats-the-size-of-an-sql-intn — Majid khalili, Feb 21 '20 at 14:30
Storage concerns aside, a phone number is not a number in the sense of being a quantity, or something you want to perform arithmetic on. It's a string composed of digits, so it retains leading zeros, and you can perform string operations on it such as checking whether it starts with a particular area code. — snakecharmerb, Feb 21 '20 at 14:40

totalhack · Accepted Answer · 2020-02-21T14:31:20.063

5

Because your number is out of range for an int type. You could try using bigint instead, or store the column as a varchar which is likely more appropriate as described here.

See this answer. An int is always stored with 4 bytes. I think you are misinterpreting what the (100) does, it just controls the display width.

More info on numeric types in the docs.

edited Feb 21 '20 at 14:31

answered Feb 21 '20 at 14:23

totalhack

2,298
17
23

score 2 · Answer 2 · answered Feb 21 '20 at 14:23

The maximum size of a signed integer in MySQL is 2,147,483,647.

90404444389 is 88,256,960,742 more than that maximum.

While they're (usually! but not always!) made up of numbers, phone numbers are not integers. Don't store them that way. A VARCHAR is far more appropriate.

score 1 · Answer 3 · answered Feb 21 '20 at 14:23

1

I'm just guessing

Usually this happens because phone number are very long and int type are limited. For example a 32 bit Integer (both positive and negative value) can represent as maximum the number 2*147*483*648

So if the phone number if something like 3*000*000*000 it's not possible to read it as int32.

answered Feb 21 '20 at 14:23

Y. Lucarelli

84
7

2

_Small Point_ It is never a good idea to start an Answer with `I am guessing` :) – RiggsFolly Feb 21 '20 at 14:24
I'll explain why I wrote "I'm guessing": in python there's no problem in reading huge numbers because it change the size in Long, but in other language this can be an issue. I don't know if it can be an issue in his platform/language combination :) thanks for the advice, I will tresure it for the next answers – Y. Lucarelli Feb 21 '20 at 14:27

why is phone number not stored in integer datataype?

3 Answers3