MySQL – Best Datatype for Phone Numbers: VARCHAR, INT, or BIGINT?

datatypesmariadbMySQL

So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition:

Take a look at the column from_number which is a VARCHAR(45) right now but it will hold a phone number. Since I don't know how many numbers a phone could have all over the world then I am trying to cover almost all of them. I want to keep database integrity as much as possible so I think VARCHAR is not a proper type for hold this kind of information – maybe I am wrong, you tell me – so I am thinking in change to INT or even BIGINT.

When I am defining a column in Workbench I should specify the number between parentheses () not in all the cases but in those I mention previous I had to. So if I do this: BIGINT() I got this error:

Which guide me to read a bit about this MySQL type here. Basically the info is this:

A large integer. … The unsigned range is 0 to 18446744073709551615.

Which make me ask: what value I should set for parentheses when I am defining a BIGINT() type. (I am using BIGINT because I don't know if INT can hold as many numbers as a phone could have – perhaps I am wrong too). Which is the right way to create|design a column in MariaDB/MySQL databases?

Anyway I would like to know your opinion, experience and of course I would like to get an answer

Note: I am using MySQL Workbench latest edition for create the ER diagram. I am using also MariaDB 10.0.x

Best Answer

How would you handle a phone number with an extension, such as "+1-000-000-0000 ext 1234" ?

Note, the "+" indicates international dialing rules should be applied; so from North America, the system automatically knows "011" in front of international calls, etc.

Also, what about phone numbers such as "1-800-DBA-HELP"?

I would typically store phone numbers as text. Having said that, it really depends how critical your phone number column is. If you are running automated dialers from that column, then you'd really want to ensure that only numbers are included, and the data represents well-formed phone numbers.

You could have separate columns for extensions, and phone numbers that have text, such as the "1-800-DBA-HELP" example I provided.

Related Solutions

Mysql – With MyISAM is there any index size savings when using INT vs BIGINT

Decided this was easy enough to investigate myself, even though I still do not understand the underlying specifics of why (feel free to elaborate?)

The answer is: YES int most definitely creates smaller indexes than BIGINT

I made two tables, first with four unsigned INT columns, second with four unsigned BIGINT

I made a compound index across all four columns for each table.

Then I added a million rows of random unsigned smallints 0-65535 to each table.

(each table has identical data, both numbers and row order)

Then I optimized and flushed both tables just to be certain.

INT

Data    17,000  KiB
Index   31,610  KiB
Total   48,610  KiB

BIGINT

Data    33,000  KiB
Index   56,921  KiB
Total   89,921  KiB

17,408,000 int.MYD
32,368,640 int.MYI

33,792,000 bigint.MYD
58,287,104 bigint.MYI

added:

I was concerned the random data repeated (I found some cases).

So I added a primary column with auto-increment to each table and emptied them. Then I filled each with the numbers from 1 to 1,000,000 in each column, incrementing for each row sequentially.

INT

Data    20,508  KiB
Index   32,301  KiB
Total   52,809  KiB

BIGINT

Data    40,039  KiB
Index   54,178  KiB
Total   94,217  KiB

So not quite 50% savings but definitely adds up, even for index storage.

Why Unsigned Integer Types Are Unavailable in Top Databases

Microsoft's Jim Hogg has responded to this issue with the following:

There are pros and cons. On the pro side, it seems like a good way to avoid some errors - having to check a (signed) int has value > 0. And I would also venture that many uses of int in fact relate to counts that should never be negative anyway. On the question of doubling max row count? - true, but I would say this is less compelling.

On the cons side ... mixing signed/unsigned types in C or C++ seems like it should be simple enough. It's not. It opens a small tarpit of hard-to-find mistakes - most due to the complex rules for implicit promotions/widenings. SQL, alas, already has an even more complex set of implicits casting rules. Adding unsigned ints, I fear, would confuse us all even more.

I'll keep this suggestion on the books. But, among all the features we could/should be adding, this one, with respect, is not near the top of that list.

Source: Microsoft Connect

I would add significantly to the pro list, and reiterate that their SQL engine is already doing FAR more complex things than this, and so their team can handle the added complexity. While I don't agree with their summation, this is Why SQL Server doesn't support unsigned types.

The Connect link was originally posted by Martin Smith in the question comments.

Best Answer

Related Solutions

Mysql – With MyISAM is there any index size savings when using INT vs BIGINT

Why Unsigned Integer Types Are Unavailable in Top Databases

Related Question