Как лучше хранить телефонный номер в БД?

Question

[Cyapa]

Приветствую, друзья. Подскажите, как лучше хранить номер телефона в БД: в виде строки или в виде числа? Второй вариант выигрывает в весе, но следует учесть, что очень часто придется делать выборку номеров по определенным условиям. К примеру:

79[4-8][2-3]*

Нужно найти номера по такому шаблону (Начинаются на 79, дальше цифра от 4 до 8, дальше цифра от 2 до 3, остальные цифры могут быть любыми).

Что-бы такое провернуть с цифрами, шаблон можно парсить и превращать его в условия типа больше-меньше. Со строками ситуация выглядит проще, взять хотя бы тот же LIKE, который сам может обработать "звездочку" в шаблоне (конечно, если ее предварительно заменить на %).

А может есть какой-нибудь альтернативный вариант? Хранить какие-нибудь предподготовленные данные о номере, которые помогут сделать выборки быстрее?

Answer 1

[Дмитрий Энтелис]

"Обожаю" такие вопросы.
Начнем с того - сколько у Вас номеров в базе, что Вам актуальна экономия места?
По теме - строки.

Comments

[Cyapa]

Это основная информация, которую придется хранить, так что экономить место смысл есть. На 100 000 это может и не будет заметно, но на 1 000 000, думаю, разница будет значительной.

[Дмитрий Энтелис]

@Cyapa: ok. 1 000 000 номеров.
Храним текстом: 11 байт. 1kk номеров = 10 МЕГАБАЙТ
Я не понимаю о чем тут вообще говорить.

[Cyapa]

@DmitriyEntelis: вообще байт будет как минимум 32 (16 символов в юникоде), но вообще, да. Не так уж и много оно занимает. А будет ли это достаточно производительно? Операции со строками в MySQL не самые быстрее.

[Дмитрий Энтелис]

@Cyapa: зачем вам хранить цифры в юникоде? :) Не понял откуда взялось 32 байта, при длине номера в 11 символов.

быстродействие сильно зависит от ваших запросов. Если искать regexp - будет ад.
Простые like % - скорее всего более менее приемлимо, но лучше протестировать самостоятельно.

[Дмитрий Энтелис]

@Cyapa: вот не поленился, сделал. Тестовая табличка id (int 11), title (varchar 11)
1 000 000 записей. Не стал заморачиваться, все в utf. Таблица myisam. Размер таблицы 42.9Mb.
Построение полнотекстового индекса 6 секунд

SELECT *  FROM `phones` WHERE `title` LIKE '790309%996'

0.20 sec

SELECT *  FROM `phones` WHERE `title` REGEXP '7903099[1-3][0-9]9[1-9]'

0.24 sec
В принципе достаточно быстро.

[Cyapa]

@DmitriyEntelis: хранить цифры в юникоде, действительно, ни к чему, но ведь приятно, когда вся база в одной кодировке, верно? По поводу цифр - их может быть и 16. Номера будут не только русские.

Тесты интересные, но таблица будет InnoDB (точнее, XtraDB). К тому же, не сказать, что 0.24 sec это достаточно быстро. Нужно это сравнить хотя-бы с обычным статическим запросом, типа `title` = 'номер', какое он выдаст время? Что-бы иметь мало мальское представление о производительности вашего железа в целом.

[Дмитрий Энтелис]

@Cyapa: у меня нет перконы под рукой, предлагаю вам проверить это самостоятельно :)
Подумал что эксперименты на офисной машине не корректны, перенес на дев сервер hetzner px60, 5.5.37-MariaDB, innodb, кеши sql отключены.

SELECT * FROM `phones` WHERE `title` LIKE '790309%996' - 0.0050 sec
SELECT * FROM `phones` WHERE `title` REGEXP '7903099[1-3][0-9]9[1-9]' - 0.1791 sec
SELECT * FROM `phones` WHERE `title` = '79030991193' - 0.0002 sec

[Дмитрий Энтелис]

Собственно результаты логичны - выборка по индексу мгновенна.
Запросы с like индекс так же используют.
Регэксп индекс не использует и тормозит.

[Дмитрий Энтелис]

на 5 миллионах, те же запросы:
0.0418
0.1832
0.0002

[Cyapa]

@DmitriyEntelis: хм. Результаты с лайком довольно неплохие, но, похоже, прирост времени у лайка гораздо больше, чем у регэкспа. Вероятно, на 20 миллионах, он обойдет лайк. И это не считая того, что для реализации шаблона лайка недостаточно, а использование чего-нибудь типа SUBSTR или LEFT, RIGHT видится сущим адом.

Интересно было бы взглянуть на производительность тех же запросов на BIGINT место VARCHAR. Для реализации первого лайка, пойдет что-нибудь такое, я думаю:

... WHERE `title` BETWEEN 7903090996 AND 7903099996

Хотя, следует заметить, что лайк искал по большему диапазону. Эквивалентом было бы '790309_996'.

[Дмитрий Энтелис]

@Cyapa: первый лайк не возможно реализовать простым BETWEEN.
Я бы думал куда то в сторону генерации из языка возможных вариантов и их проверкой.

[Philipp]

Можно еще так делать

SELECT * FROM `phones` WHERE `title` LIKE '7903099%' AND `title` REGEXP '7903099[1-3][0-9]9[1-9]'

Answer 2

[Максим]

Только строки.
Попробуйте вставить номер телефона 001235489 в поле INT.

Comments

[Cyapa]

Забыл уточнить. Номера только мобильные. Таких как вы привели в пример в базе нет и не будет.

[Lynn «Кофеман»]

Не верьте в «нет и не будет». Когда вы решите выйти на международный рынок, то будет всё.

[Cyapa]

@Lynn: Нет, не будет. Рынок и так международный, но все мобильные номера представляют из себя довольно четкий BIGINT.

[Максим]

@Cyapa у меня мобильный начинается с 0, о чем речь?))

[Cyapa]

Максим: Спустя два года мне таки стало интересно, а какой страны ваш мобильный номер?

[Trigular]

Cyapa, в Англии с нулей начинают номера, если набирать на местном уровне, типо у нас +7 = 8, у них +44 = 0

Answer 3

[vnpp]

Если следовать рекомендация по связи (см. Телефонные коды стран, Телефонный план нумерации), целесообразно хранить полный номер (+добавочный). Полный номер состоит из кода зоны и внутренних номеров, итоговая длина 11 цифр, для отдельной страны (локализованных систем) код зоны от 1 до 3 цифр хранить смысла нет. Итого, под полный номер подходит int8, под локальные может и int4 подходить.
Для добавочных номеров - отдельное поле, тут кто во что горазд.
Номера начинающиеся с нолей, как правило, местные (открытый план нумерации), хранить их нет смысла, т.к. позвонить по ним возможно только находясь в том же регионе.
Корректнее хранить полный номер и, при необходимости, отдельно, правила преобразования полного номера в местные и обязательно ограничения где это работает.
Хранение в int позволяет на уровне БД исключить ошибки ввода без использования regexp на каждый insert/update, более удобно хранить непрерывные диапазоны.
Однако, если не хранить "весь мир", слабо ощутимая выгода от перехода со string на int при хранении справочника номеров.