Reputation: 257
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.
Upvotes: 5
Views: 35077
Reputation: 23411
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
Upvotes: 7
Reputation: 9688
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:
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.
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.
Of course you shouldn't be storing SIP email addresses in your phone number column. That is not a phone number.
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.
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
Upvotes: 0
Reputation: 6493
Don't do that, phone numbers are not integers for very good reasons :
0199857514
+34199857514
#992,514
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.
Upvotes: 14