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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] mysql datatype for telephone number and address

Examples related to mysql

Examples related to types

Examples related to phone-number

Examples related to street-address