How to store a phone number in SQL as an integer?

Question

In MySQL, I built a table using the following code:

BUILD TABLE user
(
    userName char(25) NOT NULL PRIMARY KEY,
    firstName char(25) NOT NULL,
    lastName char(25) NOT NULL,
    userEmail char(25) NOT NULL,
    userPhone int(10) NOT NULL,
);

And suppose I make the following command:

INSERT INTO `user`(`userName`, `firstName`, `lastName`, `userEmail`, `userPhone`) 
VALUES ("JDoe","John","Doe","[email protected]", 9725550145)

MySQL runs the command without an issue, and puts the information in the table. However, when I retrieve the information for said John Doe, his phone number comes up as 2147483647.

In a number of the entries, I sort of noticed that if the first number of their area code is greater than 2, then they get that same number. What did I do wrong, and how can I fix this so that everyone has their respective phone number and not this seemingly random value that MySQL assigns them?

Thank you kindly.

Answer 1

Don't do that, phone numbers are not integers for very good reasons :

• leading zeroes : 0199857514
• country codes : +34199857514
• special commands : #992,514
• sip phones : sip:[email protected]

If you're going to store your phone numbers as integers, you are going to have major limitations and problems later.

To answer your question: You can't store a phone number as an integer, you must use a string.

Answer 2

Change your column userPhone int(10) NOT NULL to userPhone BIGINT NOT NULL

If you need to change the column type, here is the command:

ALTER TABLE user MODIFY userPhone BIGINT NOT NULL;

Also take a time to read about the number types on MySql docs

http://dev.mysql.com/doc/refman/5.7/en/numeric-types.html

As pointed out in the comments you shouldn't use a numeric type to store a phone number since there are numbers with a leading 0 and if it is stored as a number you would be losing this information. Pointed out by @GolezTrol and @GordonLinoff