mysql datatype for telephone number and address

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

[phone]
[phone]
01234 567890

etc. would be stored as 441234567890.

Answer 4

Well, personally I do not use numeric datatype to store phone numbers or related info.

How do you store a number say 001234567? It'll end up as 1234567, losing the leading zeros.

Of course you can always left-pad it up, but that's provided you know exactly how many digits the number should be.

This doesn't answer your entire post,
Just my 2 cents

Answer 5

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.

Answer 6

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.