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","jdoe@nothing.net", 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:01199857514@stackoverflow.net

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 phone 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

Answer 3

Despite Guillame's completely incorrect answer that has been accepted for nine years, phone numbers can and should be stored as numbers.

None of the counter-examples he gives are correct.

First, to respond to his claims that a phone number cannot be stored in a number:

1. Phone commands are not part of a phone number. You might as well try to care for a discord handle in their mailing address. Databases don't carry substitute datasets.

2. There is no plus in an international country code. Yes, that's how Europeans write it, but get your phone out, and try to dial the plus. Let me know when you figure out how, and send me some instructions please. There's also no dashes or parentheses in a phone number. There are only digits.

3. Of course you shouldn't be storing SIP email addresses in your phone number column. That is not a phone number.

4. Phone numbers do not have leading zeroes. Guillame may not know this, but that is a country-bound call command. If you go to a different country, you won't have to dial it anymore, even though the phone number hasn't changed. Therefore, it is not actually part of the phone number. So, by example, if I call my Canadian friend from Germany, I have to start by dialling a zero. But if I call them from Canada, or the United States, or Mexico, or France, I don't have to. Why? Because phone numbers by definition never start with a zero. The only exception is the American/Canadian call prefix to operator. This was part of the international phone system agreements that built this system more than a hundred years ago.

So.

Why do this right?

Well.

1. Data that can be normalized should be normalized.
   • If you're able to write the same phone number three different ways, that's a problem.
   • The example given was 0199857514.
   • If you dial that from Europe, you get Uzbekistan. Europe is the only place on Earth where that dialing pattern will work, so I assume Guillame is European, and doesn't travel enough to know how phones work other places.
   • If you dial that from the United States or Canada, you get an operator after the first digit, because dialing 0 gets operator.
   • From a cruise ship, you get the police after the second digit.
   • In Japan, you get hung up on, because 09 is the beginning of the system prefix (what an American would think of as an X11 number like 411 or 611,) and whereas there is a 091 and through a 095, there is no 099.
   • In China, you get a fast busy, because starting a call with 01 is never correct in China under any circumstances
   • The fundamental problem here is that dialing 01 before a phone number in Europe is a phone system command, and not actually part of the phone number; the correctly parsed number there is (01 is a command,) (998 is a country code), (57514) is the actual local phone number.
   • The correct complete phone number is 99857514. It does not include the external dial prefix 01, which is a command to Guillame's local phone system.
   • That number starts with just a 1 from the US, with 010 from Japan, 001 from China, et cetera. In the United States, the way to call that number varies between networks; cell phones, legacy landlines, and ESS6+ landlines all call that number differently. Think I'm kidding? Ask someone from Atlanta, who has to know what phone carrier they're on to know how to make an international call, because one requires the 1 prefix and the other forbids it.
2. Why does normalization matter?
   • It's a database community. This should be obvious.
   • Guillame writes it 0199857514. I write it 1199857514. My Chinese friend writes it 0010199857514. My Japanese friend writes it 010199857514.
   • The customer was taken by the Japanese sales team, so it's in the database as 010199857514. I'm on the American sales team, and I try to look the customer up. They're in Europe, so they say their number is 0199857514. I look, and can't find that. As an American, I next try 1199857514, and 110199857514, but they're not in there. I don't know about the Japanese notation. The system incorrectly tells me that this phone number is not in use, so I create a new customer record and attach to that.
   • The customer is now in the database twice under two different phone numbers. Records are being lost. Double billing is beginning. Things that are recorded aren't being honored because we're looking at the wrong copy of the user.
   • Foreign keys are just as easy to violate as unique constraints.
   • "But the application shouldn't have to format with local phone rules!" Of course it should. Just like you don't track dates as strings, and format them on the way in, and on the way out. Just like Europeans write AA/BB/CCCC where Americans write BB/AA/CCCC, and Japanese write CCCC/BB/AA. This is a local formatting issue, and you cannot fold local formatting into your data.
3. Performance
   • If you didn't stop reading at "this makes your data incorrect," you're going too far
   • But also, holy crap, you really want to make string comparisons instead of integer comparisons on your index?

In order to do the job correctly, you must remove the country control code, because it's not part of the phone number, and works differently in different countries

You should not be storing things that are not part of the phone number, such as PBX commands and SIP email addresses, in a phone number column

Phone numbers should be stored as numbers