Хранение номеров телефонов в БД
Хранение номеров телефонов в базе данных MySQL не такая уж тривиальная задача, как может показаться на первый взгляд. Один из первых вопросов при проектировании БД: какой тип ячейки БД использовать? Цифровое поле, например INT в MySQL не подходит для этих целей, так как номера некоторых телефонов (например мобильные номера в Украине) могут начинаться с нуля, а следовательно, если попытаться сохранить такой номер как число — ведущий ноль (нули) будут потеряны. Для хранения номеров телефонов лучше всего использовать текстовый тип столбца: CHAR.
Чтобы ускорить поиск по текстовым данным имеющим различную длину, в отдельном индексируемом цифровом столбце (TINYINT) можно хранить длину номера телефона и использовать её в условиях поиска — отсекать лишние номера при поиске. Но такая реализация базы данных нужна только при очень больших объемах телефонных номеров и в большинстве случаев её можно не использовать.
Если планируется хранение номеров телефонов в месте с кодом города и страны — лучше всего разделить эти данные на 4 отдельных столбца: код1, код2, номер телефона, полный номер телефона (код1, код2, номер).
Код1 — это, в основном, код страны (+20 — Египет, +380 — Украина и т.д.), но некоторые первичные коды телефонов могут и не иметь привязки к стране, например: 388 — European Telephony Numbering Space (Europe-wide services); 870 — Inmarsat «SNAC» service и т.д. Поэтому называть такую колонку country_code или подобным образом логически не правильно. Помимо всего прочего один первичный код может принадлежать нескольким странам: +1 — Америка, Канада и другие; 7 — Россия, Казахстан и т.д. Подробнее о таких кодах можно прочитать тут: Географические коды телефонных номеров . Хранить данные о первичном коде телефонного номера можно и в цифровом формате, так как все такие коды не могут начинаться с нуля, но исходя из соображения, что стандарт в будущем может поменяться — безопаснее сразу хранить такую информацию в текстовом виде. Максимальное количество символов Код1 — пять.
Код2 может быть кодом города, мобильного оператора или чего-нибудь еще, соответственно mob_code, city_code не являются логичными названиями для таких данных. Следует отметить, что в некоторых странах такие коды, возможно, могут начинаться и с нуля, поэтому выбор типа данных такого столбца — очевиден. Максимальное количество символов в таком столбце, скорее всего, не будет более пяти.
Номер телефона — об этом столбце написано в начале статьи. Кол-во символов в таком номере, обычно не превышает 7 штук.
Полный номер телефона содержит в себе информацию столбцов БД MySQL Код1, Код2 и Номер телефона. Этот столбец существенно упростит проверку на дублирующиеся номера. Тип и размер данных в нем высчитывается на основании колонок Код1, Код2 и Номер телефона.
Если планируется привязка нескольких номеров телефонов к одной организации, в таблице базы данных следует предусмотреть колонку с идентификатором фирмы (firm_id) и колонку порядковый номер телефона (number). Это решение очень поможет в составлении легковесных SQL запросов, когда нужно выводить список многих организаций с одним (например: основным) номером телефона.
Колонка порядковый номер должна содержать последовательные номера, начинающиеся с единицы, без разрывов: 1,2,3.
Хранение номеров телефонов, зачастую, подразумевает и сопутствующую их привязку к персональной информации — ФИО, название организации и т.д., а для этого, в свою очередь во многих странах требуются специальные разрешения, регистрации баз данных и т.п.
Хранение номеров телефонов в виде хеша
Если номер телефона нужен только для подтверждения регистрации пользователя — как средство защиты от ботов и мультиаккаунтов можно использовать следующую схему. Номер хранится в открытом виде только до подтверждения регистрации. После подтверждения создается и записывается в БД взломоустойчивый хеш номера телефона, а сам номер удаляется:
При необходимости восстановления пароля пользователя через мобильный телефон, отослав на него код активации, нужно запросить через форму у пользователя его номер телефона, вычислить хеш, сравнить с тем что в БД и выслать на него инструкции по активации. RFL_HABR22032013
Полезная информация
Мне вот интересно, при условии хранения в базе только хеша телефонного номера и получения от пользователя только, собственно, этого номера — откуда Вы собрались взять $id_phone_number для вычисления хеша и поиска его в базе?
Тут логично использовать $user_id, его можно брать по логину, указанному в той же форме, что и номер телефона.
Datatype for phone number: VARCHAR, INT or BIGINT?
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
4 Answers 4
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.
Previously it was written:
«With MariaDB you could use a computed field to extract just the digits for an auto-dialer. Also works for MySQL 5.7.»
In response to the OP’s question about this («can you explain a bit what are you telling me?»), here is an explanation.
Many database systems have now introduced this feature. These are fields which are known variously as » computed «, » virtual » or » generated » which are derived from values in other fields. The power of this feature will vary depending on your RDBMS. I know that Oracle, Firebird, MariaDB and now MySQL 5.7 have them. Others probably also do.
An easy example would be to have a surname column and have a computed column which «stores» (remember, they can be virtual — i.e. calculated on the fly, or they can be physically stored on disk) the surname as all capitals, thereby making searching easier. That way you only have to search on CAP s (using, say, LIKE ), knowing that the data being searched in the [ computed | virtual | generated ] field is in capitalised text.
The concept for MySQL 5.7 is explained here and here. It has been in MariaDB for a bit longer and the concept is also explained here. Some possible uses are suggested here, but you are really only limited by your imagination. They can be seen as a convenient (and less error-prone) substitute for triggers.
For your particular use case, you could derive a dialable number from a text field «+» —> «00» (or whatever your international dialling code is). Just a thought.
mysql datatype for telephone number and address
If tel_number is larger than 15 bit, which datatype can I use, I’d better use Bigint(20) ?
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.
11 Answers 11
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
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.
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
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.
etc. would be stored as 441234567890 .
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.
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.
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:
or with processing/splitting before insert (2+2+4+1 = 9 bytes)
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.
Как лучше сохранять в базу данных номера телефонов?
Форматы номеров могут быть разные, 6, 7, 11 цифр.
Как именно
123-45-67 или 1234567
+7 (912)-345-67-89 — +7 или 8?
(Во втором случае, я догадываюсь, что можно использовать код страны, из отдельной таблицы)
varchar тяжелее int‘а
- Вопрос задан более трёх лет назад
- 48145 просмотров
- Вконтакте
- Вконтакте
Последний вопрос:
Вы зарекнулись про ключ, действительно, лучше ли сделать его уникальным и выкинуть ID? Тогда в таблице ВЛАДЕЛЬЦЫ (например), будет связь по самому номеру, тогда будут уходить лишние байты.
Это нормально, можно так сделать? Или всё же оставить ID? Как считаете?
Вот что-то у меня не получилось сохранить номер 0550 используя целый тип.
Хотя вряд ли есть такие номера.
- Вконтакте
- Вконтакте
- Вконтакте
Как насчёт разделителей, лучше их просто удалять?
из 8 (912) 12
будет 891212
Просто тогда мы лишаемся одной вещи.
Может пользователь хочет чтобы его номер видели как:
89-89-11-222-11
Господа, как вы все на это смотрите?
- Вконтакте
- Вконтакте
В принципе самый лучший, слегка идеализированный вариант — разделять код страны, код зоны и собственно сам номер телефона. Ну и на уровне стран и зон стран хранить маски.
Тогда получится универсальная схема, способная показывать номера в привычном пользователям формате по маске и не захламленная спецификой межзоновых, междугородних и международных кодов.
То есть для РФ +7 (900) 100-00-00 , +7 (8765) 22-99-99, + 7 (876-55) 5-25-25, а эти же номера для немца окажутся 007 900 1000000, 007 876 5229999, 007 876 5552525,
а для усть-урюпинска — могут быть как:
8 900 100-00-00, 8-2 22-99-99 и 5-25-25
хотя в базе — это будет 3+ поля
в принципе хранить имеет смысл как int, а для «хитростей» поиска по частичным маскам — просто держать вычисляемые поля, где номер кастится к [n]varchar