Хранение номера телефона mysql

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.

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.

Главная / Web / MySQL / Хранение номеров телефонов в Б…

Хранение номеров телефонов в БД

Хранение номеров телефонов в базе данных 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.

Хранение номеров телефонов, зачастую, подразумевает и сопутствующую их привязку к персональной информации — ФИО, название организации и т.д., а для этого, в свою очередь во многих странах требуются специальные разрешения, регистрации баз данных и т.п.

Хранение номеров телефонов в виде хеша

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

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

Опубликовано: 2013/03/20

27668

1. Learning About MySQL Data Types
2. Use CHAR to Store Phone Numbers in MySQL
3. Use TINYTEXT to Store Phone Numbers in MySQL
4. Use VARCHAR to Store Phone Numbers in MySQL

MySQL offers various convenient data types for storing expected input to its databases. Knowledge of the correct datatype for storing peculiar data is key to ensuring an optimized and efficient database.

This tutorial illustrates the formatting and storage of phone numbers in a MySQL database.

Learning About MySQL Data Types

MySQL generally has three data types:

1. Numeric (INT, BIT, FLOAT, etc.)
2. String (CHAR, VARCHAR, TEXT, etc.)
3. Date and Time (DATE, DATETIME, TIMESTAMP, etc.)

These generalized datatypes further have sub-types for specific cases of data handling in the database.

Check out this reference from w3schools on various RDBMS (Relational Database Management System) datatypes for further reading.

In handling phone numbers, one may be quick to consider using a numeric data type. However, problems could arise as phone numbers come in various formats (country codes, delimiters, etc.) and have special characters.

Since phone numbers are at a maximum of fifteen (15) digits in length, according to the International Telecommunications Union, a string type of CHAR, VARCHAR, or TINYTEXT are the best considerations.

CHAR and TINYTEXT have an upper limit of two-hundred-and-fifty-five (255) characters, while VARCHAR uses dynamic memory allocation for storing data up to a defined limit between 0 and 65535.

Let us take a few examples of phone number storage with each data type to decide the most appropriate choice.

Use CHAR to Store Phone Numbers in MySQL

The CHAR datatype (short for character) can store strings of fixed length between 0 and 255 characters. A column implementing CHAR can specify an upper limit constraint between 0 and 255, and MySQL expects every string in that column to be of the same size.

MySQL pads the remainder of the space with blanks when inputting a lower-length string than the specified constraint.

For example, if a table column specifies a CHAR data type with a size constraint of thirty characters (30), passing a value of 10 characters still takes up the space of 30 characters (10 data characters and 20 blanks).

A further discussion on this is available via this MySQL official documentation.

To illustrate, let us create a sample registration system database.

-- Initializing
CREATE DATABASE new_registration_db;
USE new_registration_db;

-- CREATING TABLES
CREATE TABLE registered_users (
	id INT AUTO_INCREMENT,
    username VARCHAR (255) NOT NULL,
    email VARCHAR(255),
    phone CHAR (15) NOT NULL,
    PRIMARY KEY(id)
);

-- POPULATING THE TABLE WITH SAMPLE REGISTRATION DATA
INSERT INTO registered_users(username, email, phone) Values
	('Mark Laurent', 'MarkRLaurent@teleworm.us','+1 908-204-0495'),
    ('Patricia Todd', 'PatriciaJTodd@teleworm.us','+1 801-752-2367'),
    ('Victoria McDonald', 'VictoriaAMcDonald@dayrep.com', '+1 608-299-8640'),
	('Vin Petrol', 'vin_not_diesel@crudemail.com','+1 870-381-6967');

Output:

1 row(s) affected
0 row(s) affected
0 row(s) affected
4 row(s) affected Records: 4  Duplicates: 0  Warnings: 0

Now, let us preview the resulting table.

SELECT * FROM registered_users;    -- Checking the table

Output:

id	username			email							phone
1	Mark Laurent		MarkRLaurent@teleworm.us		+1 908-204-0495
2	Patricia Todd		PatriciaJTodd@teleworm.us		+1 801-752-2367
3	Victoria McDonald	VictoriaAMcDonald@dayrep.com	+1 608-299-8640
4	Vin Petrol			vin_not_diesel@crudemail.com	+1 870-381-6967
-----------------------------------------------------------------------------------------
4 row(s) returned

Here, since the phone number is of a fixed length of 15 characters, the CHAR datatype will offer efficient storage. Also, it is indexable for filtering purposes.

However, the CHAR datatype may not efficiently manage memory for applications that take phone numbers of variable size (a global application) due to the padding of blanks, as earlier discussed.

Use TINYTEXT to Store Phone Numbers in MySQL

The TINYTEXT datatype is the smallest of the TEXT-type datatypes. It has the same memory constraints as a CHAR datatype between 0 and 255 characters.

However, unlike CHAR, it can dynamically allocate space to a passed value according to its character length. Hence, it offers better memory efficiency than the CHAR for this example of storing phone numbers.

It does have the downside of not having a default value making it non-indexable for sorting or aggregation.

Now, let us redo the previous example using TINYTEXT.

-- CREATING TABLES
CREATE TABLE registered_users2 (
	id INT AUTO_INCREMENT,
    username VARCHAR (255) NOT NULL,
    email VARCHAR(255),
    phone TINYTEXT NOT NULL,
    PRIMARY KEY(id)
);

-- POPULATING THE TABLE WITH SAMPLE REGISTRATION DATA
INSERT INTO registered_users2(username, email, phone) Values
	('Mark Laurent', 'MarkRLaurent@teleworm.us','+1 908-204-0495'),
    ('Patricia Todd', 'PatriciaJTodd@teleworm.us','+1 801-752-2367'),
    ('Victoria McDonald', 'VictoriaAMcDonald@dayrep.com', '+1 608-299-8640'),
	('Vin Petrol', 'vin_not_diesel@crudemail.com','+1 870-381-6967');

SELECT * FROM registered_users2;    -- Checking the table

Output:

id	username			email							phone
1	Mark Laurent		MarkRLaurent@teleworm.us		+1 908-204-0495
2	Patricia Todd		PatriciaJTodd@teleworm.us		+1 801-752-2367
3	Victoria McDonald	VictoriaAMcDonald@dayrep.com	+1 608-299-8640
4	Vin Petrol			vin_not_diesel@crudemail.com	+1 870-381-6967
-----------------------------------------------------------------------------------------
0 row(s) affected
4 row(s) affected Records: 4  Duplicates: 0  Warnings: 0
4 row(s) returned

We get an expected result. Check this reference for extra information on the MySQL TINYTEXT and other TEXT datatypes.

Use VARCHAR to Store Phone Numbers in MySQL

The final suggested method for handling phone numbers in MySQL is by using the VARCHAR data type. VARCHAR offers the flexibility of dynamic memory allocation when the phone number length will vary across database users.

It typically allocates two (2) extra bytes for storing length information. Hence, if a character of length six (6) is stored, a total memory allocation of 8 bytes is required for VARCHAR.

Columns specifying VARCHAR datatypes are also indexable for implementing sorting, aggregation, and primary/foreign key constraints.

Let us create a third table implementing VARCHAR for phone number allocation.

-- CREATING TABLES
CREATE TABLE registered_users3 (
	id INT AUTO_INCREMENT,
    username VARCHAR (255) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR (15) NOT NULL,
    PRIMARY KEY(id)
);

-- POPULATING THE TABLE WITH SAMPLE REGISTRATION DATA
INSERT INTO registered_users3(username, email, phone) Values
	('Mark Laurent', 'MarkRLaurent@teleworm.us','+1 908-204-0495'),
    ('Patricia Todd', 'PatriciaJTodd@teleworm.us','+1 801-752-2367'),
    ('Victoria McDonald', 'VictoriaAMcDonald@dayrep.com', '+1 608-299-8640'),
	('Vin Petrol', 'vin_not_diesel@crudemail.com','+1 870-381-6967');

SELECT * FROM registered_users3;    -- Checking the table

Output:

id	username			email							phone
1	Mark Laurent		MarkRLaurent@teleworm.us		+1 908-204-0495
2	Patricia Todd		PatriciaJTodd@teleworm.us		+1 801-752-2367
3	Victoria McDonald	VictoriaAMcDonald@dayrep.com	+1 608-299-8640
4	Vin Petrol			vin_not_diesel@crudemail.com	+1 870-381-6967
-----------------------------------------------------------------------------------------
0 row(s) affected
4 row(s) affected Records: 4  Duplicates: 0  Warnings: 0
4 row(s) returned

Implementing either of the three described datatypes is sufficient for handling phone numbers in a MySQL database.

However, the choice of the most appropriate data type, based on memory efficiency and speed, is dependent on the intended database application.