Mysql – Storing phone number in E.164

database-designdatatypesMySQLnumber formattingstorage

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field?

Have been reading up on format/storage of international phone numbers. In the past for various reasons it was suggested to store as a string due to this data being an identifier not an actual number(along with issues like leading 0's). I'm aware that I should not attempt to parse/validate phone numbers via regex, and that libphonenumber developed by Google is generally recommended for handling this and producing E.164 formatted values.

I'm assuming it's safe to say E.164 is the best suited storage format for phone numbers today. As the leading + does not seem required and that leading 0's would not occur in this format? I've seen BIGINT(15) suggested for storing this in MySQL databases. This would require an additional field(string?) to support phone numbers with extension numbers. It's not stated on the Wikipedia article but I've seen many mentions in blog posts that the E.164 format supports extensions by appending ";ext=12345" so would it be more advisable to store into a VARCHAR(32) field? "+19995556789;ext=12345", 32 should be the maximum length with this format, so the length of 32 is appropriate? <+><;ext=>

EDIT: I'm not sure exactly what the extension value should be, according to this it is 11, however the article linked to has been modified since and archived by Apple, there is no clear information on extension length there. Would it be best just to a higher limit such as VARCHAR(50), or go with BIGINT(15) and a additional field for extensions that is nullable?

Best Answer

BIGINT(15) or even BIGINT(18) might not be long enough to store all possible phone numbers. Depending which country and if a PBX is involved 18 digits are not going to be enough. I have seen quite a number of longer ones already. Even so E.164 recommends to not assign phone numbers with more than 15 digits to phone providers world wide, not all of them adhere to these standards.

I am not sure where exactly you are heading with your database, but if you want to store phone numbers I would strongly suggest to use a character based format. In most applications that really use phone numbers you might not only want to store them but also use them. For using a phone number you might need some transformations to add, change or replace prefixes, or maybe filter for certain prefixes, since international phone numbers do not have all the same length it is much easier and faster on the database to use a character based format like CHAR(32) or VARCHAR(32). If speed of character based filter operations (number pattern, prefixes, areacodes, etc) is important for you the CHAR typ will usually outperform VARCHAR but you will have to clean up trailing spaces. Internationally there are two common ways to realize direct in dial lines. I would call them "direct dial" and "post connection dial". Any direct dial number can be put into the "regular" number field since they can be dialed in one succession with the carrier assigned number. The other type usually requires a pause before continuing to dial the extension (like in most smaller US legacy PBX systems) these "post dial digits" should go into a separate field, which for better design should be nullable. I would not use the E.164 extension format that you mentioned, it is so rarely used that in myself working in the industry for well over 10 Years I have never seen it in production.

Related Question