2

I am working on a college project having a form to store mobile numbers. Recently i found out a 10 digit phone number (without +/-) cant be stored in a data type of integer of length 10. Why is it so?

Unfortunately a phone number of 9 digit can be stored without any problem. Atlast i changed the data type to VARCHAR. But still am curious about the integer. After all there is only 10 integer digits.

James Z
  • 12,209
  • 10
  • 24
  • 44
Cinthya M Shaji
  • 49
  • 6
  • 1
    Phone numbers nowadays include "+" signs as well as dashes and parenthesis. An `INT4` can store 9.5 digits only, so it comes short of what you need. You could use a `BIGINT` (18.9 digits). – The Impaler Jul 22 '22 at 16:47
  • but i never added '+' sign on it. – Cinthya M Shaji Jul 22 '22 at 16:48
  • 1
    Add the ddl, the insert statement and the error message you get – Jens Jul 22 '22 at 16:48
  • You never add the "+" sign, but the end users will, and will end up complaining. – The Impaler Jul 22 '22 at 16:50
  • 1
    @Jens i solved the error using VARCHAR. just curious about the integer data type – Cinthya M Shaji Jul 22 '22 at 16:50
  • 1
    There is no such thing as a store integer length 10...see https://dev.mysql.com/doc/refman/8.0/en/integer-types.html and https://stackoverflow.com/questions/58938358/mysql-warning-1681-integer-display-width-is-deprecated – P.Salmon Jul 22 '22 at 16:50
  • 3
    A phone number is not an integer. an integer has no leading zero's. a phone number can have it – Jens Jul 22 '22 at 16:51
  • Please read [this](https://github.com/google/libphonenumber/blob/master/FALSEHOODS.md). And please know that the standard for representing telephone numbers is [this](https://en.wikipedia.org/wiki/E.164). Use characters, not number data types, to represent telephone numbers. 64 characters -- VARCHAR(64) in a DBMS -- is enough. – O. Jones Jul 22 '22 at 17:07
  • Most importantly, you don't WANT to store a phone number as a numeric field, just like you don't want a ZIP code to be stored as a numeric field. If you're not going to be doing arithmetic calculations on it, store it as a string. – Andy Lester Jul 22 '22 at 18:53

2 Answers2

2

the INT data type in mysql does not go by length of the integer, but rather the max and min value that can be stored with 4 bytes. Also, it should be noted that you can have either a SIGNED INT, or an UNSIGNED INT, which also effects the max and min value.

So for instance, a SIGNED INT can store values from -2147483648 to 2147483648, and an UNSIGNED INT can store values from 0 to 4294967295. Neither option could store a ten digit phone number.

You could however use a BIGINT, which uses 8 bytes of data to store its values, allowing a much higher min and max allowed. But you may be better served by VARCHAR, as a phone number is not really an integer. For instance, the phone number 0012345678, would just be stored as 12345678 in an INT field, and you would then have to run formatting conversions on the data before displaying.

dqhendricks
  • 19,030
  • 11
  • 50
  • 83
-4

To store mobile numbers you should use BIGINT data type in MYSQL, because 10 digits mobile number exceeds the range of INT data type as INT takes only 4 bytes ranging from 0 to 4,294,967,295 (Unsigned INT) which is not enough to store 10 digits mobile number. A BIGINT (8 bytes) can store it easily as it's range is from 0 to 18,446,744,073,709,551,615 (Unsigned BIGINT)

Hemant
  • 27
  • 2
  • 8