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.

score 72 · Accepted Answer · answered Dec 04 '09 at 11:06

72

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

answered Dec 04 '09 at 11:06

o.k.w

24,261
6
60
62

@garcon: Honestly speaking, the other guys here gave more holistic views. Mine is just one of many scenarios which 'void' the use of numeric type for phone numbers. :P – o.k.w Dec 04 '09 at 11:18
10

http://img.thedailywtf.com/images/12/q2/err6/pic3.png pretty much proves everything – sinni800 Jun 15 '12 at 22:57

score 51 · Answer 2 · answered Dec 04 '09 at 11:05

51

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.

answered Dec 04 '09 at 11:05

Vincent Ramdhanie

98,815
22
134
183

@Vincent, but if i want to use authenticate the format of numbers, i think i have to use int. Actually it's the first time i make a "real application", So I want to get your suggestions from real projects. – SUN Jiangong Dec 04 '09 at 11:08
4

@garcon1986: The validation - (not 'authentication') - has to be done on your application prior to comitting to the databases. You could prefer writing an SQL trigger with a valitation PL procedure to live inside the database - this will be harder, but is the way to go if your DB data willbe acessed by front-end apps written in different programing languages by different people - , but you should not rely onthe database datatype for validation – jsbueno Dec 04 '09 at 11:52
2

@garcon1986 Validation is still easier with varchar than int. Every real project that I know uses varchar rather thasn int for telephone fields. Even validation libraries expect characters. – Vincent Ramdhanie Dec 04 '09 at 13:01
@thanks a lot, Vincent. I'm sorry for misunderstanding the two concepts firstly. And I'll try to use "varchar" type in telephone numbers with validation. – SUN Jiangong Dec 04 '09 at 13:21
3

@garcon1986 It is no problem at all. No need to apologize...that is why this site exists – Vincent Ramdhanie Dec 04 '09 at 13:29
I can't speak for mysql specifically, but you should be able to have a check constraint that ensures a varchar only contains digits – nafg Feb 01 '18 at 21:38

score 31 · Answer 3 · edited Jan 31 '18 at 17:37

31

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

edited Jan 31 '18 at 17:37

ΩmegaMan

22,885
8
76
94

answered Dec 04 '09 at 11:44

Dan Kelly

2,516
3
41
58

@Dan Kelly, Thanks , you have provided me with another method. cool! – SUN Jiangong Dec 04 '09 at 13:22
Just curious: Why TINYTEXT? – Parapluie Mar 07 '17 at 19:35
In our case we wanted to be able to store the leading 0 in the "number" field, and didn't see any scenario where the stored data would be > 255 chars. – Dan Kelly Mar 09 '17 at 11:10

score 24 · Answer 4 · edited Jun 11 '13 at 10:13

24

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.

edited Jun 11 '13 at 10:13

fedorqui 'SO stop harming'

228,878
81
465
523

answered Sep 12 '10 at 15:57

Curon

249
2
2

I'm surprised this answer didn't get more votes -- I also chose BIGINT unsigned. I agree that E164 never starts with 0, always starts with + which my application adds, contains only numeric digits, and most importantly it uses substantially less space than character types. – andy magoon Feb 16 '12 at 15:48
Thank you for pointing out the lack of numbers beginning with 0. I was not previously aware of that. So assuming full E.164 format, a leading zero should only be part of the international dialing prefix, i.e., the dial-out code, which varies by region, and is not part of the number per se. That prefix would actually be a function of the location of the app or person using the number, not the number itself, and thus generally should not be stored. In future designs I will consider bigint as an option. – Chad N B Feb 05 '13 at 20:35

score 7 · Answer 5 · answered Dec 04 '09 at 11:06

7

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.

answered Dec 04 '09 at 11:06

kon

544
3
11

1

@KON，thanks， i prefer to use varchar for telephone numbers now. – SUN Jiangong Dec 04 '09 at 11:38

score 5 · Answer 6 · answered Dec 04 '09 at 11:06

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.

answered Dec 04 '09 at 11:06

nfechner

16,559
7
43
63

@nfechner, Thanks for providing the case, it's practical. – SUN Jiangong Dec 04 '09 at 11:39
we use an 'x' here.. 999 9999 x1234 – ShoeLace Feb 03 '10 at 06:55
It may be wise at times to store an extension separately. For example, in an automatic voice messaging application, the app must know to dial the number, wait for the call to be answered, pause, and then dial the extension. In other words, the two parts have separate meaning to the app and may require different code. In my opinion it is cleaner to handle that using two columns than using a delimiter in one column. I am not saying either way is right or wrong; it depends on the design tradeoffs at hand. I merely mean to point out that there are other ways of handling extensions. – Chad N B Feb 05 '13 at 20:43

score 3 · Answer 7 · answered Feb 24 '13 at 14:28

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.

score 3 · Answer 8 · answered Feb 06 '16 at 02:09

3

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.

answered Feb 06 '16 at 02:09

00500005

2,988
2
27
37

score 2 · Answer 9 · answered Mar 09 '16 at 11:06

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.

score 1 · Answer 10 · answered Aug 04 '15 at 05:37

1

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

answered Aug 04 '15 at 05:37

abhinawbharat04

13
4

mysql datatype for telephone number and address

10 Answers10

Linked

Related