[mysql] mysql datatype for telephone number and address

I want to input telephone number in a form, including country code, extension

create table if not exists employee(    `   
      country_code_tel   int(11),
      tel_number         int(10),
      extension          int(10),
      mobile             bigint(20)
);

If tel_number is larger than 15 bit, which datatype can I use, I'd better use Bigint(20)?

create table address(
      address           varchar(255),  
      city              varchar(255),
      country           varchar(255),
      post_code         int(11)
);

For example, if I have a country code for Canada I can use +2 or 002. Which is better for processing?

Thanks for your advice.

Consider normalizing to E.164 format. For full international support, you'd need a VARCHAR of 15 digits.

See Twilio's recommendation for more information on localization of phone numbers.

Actually you can use a varchar for a telephone number. You do not need an int because you are not going to perform arithmetic on the numbers.

I usually store phone numbers as a BIGINT in E164 format.

E164 never start with a 0, with the first few digits being the country code.

+441234567890
+44 (0)1234 567890
01234 567890

etc. would be stored as 441234567890.

INT(10) does not mean a 10-digit number, it means an integer with a display width of 10 digits. The maximum value for an INT in MySQL is 2147483647 (or 4294967295 if unsigned).

You can use a BIGINT instead of INT to store it as a numeric. Using BIGINT will save you 3 bytes per row over VARCHAR(10).

To Store "Country + area + number separately". You can try using a VARCHAR(20), this allows you the ability to store international phone numbers properly, should that need arise.

I'm not sure whether it's a good idea to use integers at all. Some numbers might contain special characters (# as part of the extension for example) which you should be able to handle too. So I would suggest using varchars instead.

i would use a varchar for telephone numbers. that way you can also store + and (), which is sometimes seen in tel numbers (as you mentioned yourself). and you don't have to worry about using up all bits in integers.

Store them as two fields for phone numbers - a "number" and a "mask" as TinyText types which do not need more than 255 items.

Before we store the files we parse the phone number to get the formatting that has been used and that creates the mask, we then store the number a digits only e.g.

Input: (0123) 456 7890
Number: 01234567890
Mask: (nnnn)_nnn_nnnn

Theoretically this allows us to perform comparison searches on the Number field such as getting all phone numbers that begin with a specific area code, without having to worry how it was input by the users

varchar or text should be the best datatypes for storing mobile numbers I guess.

If storing less then 1 mil records, and high performance is not an issue go for varchar(20)/char(20) otherwise I've found that for storing even 100 milion global business phones or personal phones, int is best. Reason : smaller key -> higher read/write speed, also formatting can allow for duplicates.

1 phone in char(20) = 20 bytes vs 8 bytes bigint (or 10 vs 4 bytes int for local phones, up to 9 digits) , less entries can enter the index block => more blocks => more searches, see this for more info (writen for Mysql but it should be true for other Relational Databases).

Here is an example of phone tables:

CREATE TABLE `phoneNrs` (   
    `internationalTelNr` bigint(20) unsigned NOT NULL COMMENT 'full number, no leading 00 or +, up to 19 digits, E164 format',
    `format` varchar(40) NOT NULL COMMENT 'ex: (+NN) NNN NNN NNN, optional',
    PRIMARY KEY (`internationalTelNr`)
    )
DEFAULT CHARSET=ascii
DEFAULT COLLATE=ascii_bin

or with processing/splitting before insert (2+2+4+1 = 9 bytes)

CREATE TABLE `phoneNrs` (   
    `countryPrefix` SMALLINT unsigned NOT NULL COMMENT 'countryCode with no leading 00 or +, up to 4 digits',
    `countyPrefix` SMALLINT unsigned NOT NULL COMMENT 'countyCode with no leading 0, could be missing for short number format, up to 4 digits',
    `localTelNr` int unsigned NOT NULL COMMENT 'local number, up to 9 digits',
    `localLeadingZeros` tinyint unsigned NOT NULL COMMENT 'used to reconstruct leading 0, IF(localLeadingZeros>0;LPAD(localTelNr,localLeadingZeros+LENGTH(localTelNr),'0');localTelNr)',
    PRIMARY KEY (`countryPrefix`,`countyPrefix`,`localLeadingZeros`,`localTelNr`)  -- ordered for fast inserts
) 
DEFAULT CHARSET=ascii
DEFAULT COLLATE=ascii_bin
;

Also "the phone number is not a number", in my opinion is relative to the type of phone numbers. If we're talking of an internal mobile phoneBook, then strings are fine, as the user may wish to store GSM Hash Codes. If storing E164 phones, bigint is the best option.