Какой тип данных использовать для номера телефона sql

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.

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.

Главная / 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

27514

When dealing with SQL databases, users will have to interact with different data types and schemas depending on the project’s requirements. Since databases are used in many different applications and support multiple platforms, devices, and users, there may be differences in how data is entered, even in a simple field like a phone number.

For example, one set of users will enter the phone number with the country code, while the others may simply ignore the country code. Therefore, it is essential for database users to know how to clean data in a SQL database. In this post, we will see how to utilize SQL string operations on phone number fields to properly clean, format, and extract data.

Why Do We Care About Data Cleaning?

Data comes in all forms and sizes. Most of the time, it will be raw data, without proper formatting. Data cleaning allows users to filter, sort, and format this raw data to extract meaningful information from it. Data cleaning also makes data analysis a much more efficient process as an additional benefit.

Let’s assume we have a text field that captures user information and asks users to enter valuable information like phone numbers. We need to clean this text field to make the phone numbers usable. How would we go about doing that? To begin with, we need to understand SQL string operations.

What are SQL String Operations?

String operations are functions that can be used to manipulate strings. These string operators (also called string functions) take a string as input, process it according to the specified function, and return a string as the output. The availability of these functions will differ depending on the database. For example, the following is a list of commonly used string functions in MySQL:

Common Types of String Operations

The full list of available string functions and operators is available in the official documentation.

How to use String Operations with Phone Numbers

Now we have a basic understanding of string operations and related functions in SQL! So, let’s look at how exactly we can utilize some of these functions on phone numbers to extract and format data. For all the examples provided here, we will be using the Arctype SQL client with a MySQL database in a Windows environment with the following dataset.

If you don’t have Arctype installed, you can use the link below to download it and follow along:

The fast and easy-to-use
SQL client for developers and teams

Properly Formatting a Phone Number

We can utilize the SUBSTR command to format phone numbers. It will extract parts of the number and use the CONCAT to join the specific parts together. In the following example, we have broken down the phone number into three distinct sections and combined them together with the formatting as a new field called num_formatted:

SELECT
    phone_num,
    CONCAT(
        '(',
        SUBSTR(phone_num, 1, 3),
        ') ',
        SUBSTR(phone_num, 4, 3),
        '-',
        SUBSTR(phone_num, 7)
    ) AS num_formatted
FROM
    EmpDetails

The result of the operation is show below:

You can create a new column to save the formatted number using the ALTER TABLE command. It will create a new column and update it with the formatted numbers.

ALTER TABLE EmpDetails
ADD formatted_number VARCHAR(255);
    
UPDATE
    EmpDetails
SET
    formatted_number = (
        SELECT
            CONCAT(
                '(',
                SUBSTR(phone_num, 1, 3),
                ') ',
                SUBSTR(phone_num, 4, 3),
                '-',
                SUBSTR(phone_num, 7)
            ) AS num_formatted
    )

The result of the above operation is shown below:

We can modify our SQL command to include the REPLACE function if there are additional characters in the phone number field, such as dashes and brackets. This function can be used to remove unnecessary characters and whitespaces in a string. Now let’s further format the formmated_number field to include the country code.

SELECT
    formatted_number,
    CONCAT(
        '+1 '
        '(',
        SUBSTR(num_cleansed, 1, 3),
        ') ',
        SUBSTR(num_cleansed, 5, 3),
        '-',
        SUBSTR(num_cleansed, 7)
    ) AS num_formatted
FROM
    (
        SELECT
            formatted_number,
            REPLACE(REPLACE(REPLACE(REPLACE(formatted_number, '(', ''), ')', ''), '-', ''), ' ','') as num_cleansed
        FROM
            EmpDetails
    ) FormattedPhoneNumbers

In the above statement, the REPLACE function is used to remove the brackets, dashes, and whitespaces from the formatted_number field before undergoing the formatting process.

We can use the LEFT and RIGHT functions to specify and extract different parts of the phone number. In the following example, we will extract the area code of the phone number using the LEFT function and the last four digits using the RIGHT function based on the num_formatted field.

SELECT
    REPLACE(LEFT(formatted_number, 4), '(', '') As 'Area Code',
    RIGHT(formatted_number, 4) AS 'Last Four Digits'
FROM
    EmpDetails

Here, the REPLACE function is used to remove the bracket from the selected number block. The result of the query is shown below.

One of the most common tasks in the world of data cleaning is extracting phone numbers from a text block. The complexity and feasibility of this task will mostly depend on the composition of the text.

The easiest way to extract phone numbers is to utilize regular expressions to target the specific phone number formats. Extracting data has become far simpler with the introduction of functions like REGEXP_SUBSTR in MySQL 8.0. We will be populating the details column with some phone numbers in different formats, as shown below.

Identifying Rows with Matching Phone Numbers

First, we need to figure out which rows consist of data matching our requirements. In this case, the following regular expressions will be used on the details field.

• Any consecutive 10 digits
• Formatted number (XXX-XXX-XXXX)

SELECT
    *
FROM
    EmpDetails
WHERE
    # Any 10 Digits
    details REGEXP '[[:digit:]]{10}'
    # Formatted Number (XXX-XXX-XXXX)
    OR details REGEXP '[0-9]{3}-[0-9]{3}-[0-9]{4}';

Results of the query can be seen below:

Since we have identified the rows, the next step is to extract the phone numbers. It can be done using the REGEXP_SUBSTR function to extract the substring which matches the given regular expression. As we need to query two different regular expressions, we will be using the CONCAT_WS function to combine the results of both expressions into a single column.

SELECT
    emp_id,
    name,
    email,
    CONCAT_WS(
        '',
        REGEXP_SUBSTR(details, '^[0-9]+$', 1, 1, 'm'),
        REGEXP_SUBSTR(details, '[0-9]{3}-[0-9]{3}-[0-9]{4}', 1, 1, 'm')
    ) AS extracted_phone_numbers
FROM
    (
        SELECT
            *
        FROM
            EmpDetails
        WHERE
            details REGEXP '[[:digit:]]{10}'
            OR details REGEXP '[0-9]{3}-[0-9]{3}-[0-9]{4}'
) ExtractedDetails

The result of this operation can be seen below:

Handling Multiple Phone Numbers in a Single Field

To query results from a single field with multiple numbers, we need to create a stored procedure that loops through the desired field to capture all matching regex patterns. For instance, let’s see how to extract multiple phone numbers from the details field of emp_id 1702 (Dan).

DELIMITER $$
CREATE PROCEDURE get_number_of_matches_full()
BEGIN
  DECLARE regex_match INT DEFAULT 1;
  DECLARE current_number INT DEFAULT 1;
  WHILE regex_match >= 1 DO
    CREATE TABLE IF NOT EXISTS extracted_numbers_table (
        `emp_id` int NOT NULL,
        `name` varchar(255) DEFAULT NULL,
        `email` varchar(255) DEFAULT NULL,
        `extracted_phone_num` varchar(255) DEFAULT NULL
    );
    INSERT INTO extracted_numbers_table (emp_id, name, email, extracted_phone_num)
    SELECT emp_id, name, email, REGEXP_SUBSTR(details, '[0-9]{3}-[0-9]{3}-[0-9]{4}', 1, current_number, 'm') FROM EmpDetails WHERE emp_id = 1702;
    SET current_number = current_number + 1;
    IF ((SELECT REGEXP_SUBSTR(details, '[0-9]{3}-[0-9]{3}-[0-9]{4}', 1, current_number, 'm') FROM EmpDetails WHERE emp_id = 1702) IS NULL) THEN
        SET regex_match = 0;
    END IF;
  END WHILE;
END $$
    
DELIMITER;

The result of this operation is shown below.

In the above code block, we have created a stored procedure called get_number_of_matches_full, which loops through the details field until all the regex matches are found on the specified row of the EmpDetails table. We are using the REGEXP_SUBSTR function with the position argument to extract different matches. It updates by one at each loop run and saves the results on a newly created extracted_numbers_table. Finally, the loop exits when a NULL result is found.

We can call this procedure and view the results by querying the extracted_numbers_table as shown below.

CALL get_number_of_matches_full;

SELECT * FROM extracted_numbers_table;

And, shown again in Arctype:

Conclusion

String operations in SQL are vital functions to clean and format data within a database. Moreover, string operations are the core component when dealing with valuable information such as phone numbers as they allow users to extract or manipulate phone numbers according to their requirements. However, it’s important to remember that the exact functions available will change depending on the database type and version. Therefore, always remember to refer to the documentation to find out the available string operations and implement them to your heart’s content.

Ready to get started with string operations in SQL? If so, be sure to download Arctype and give it a try. You can use the link below to install Arctype for free and follow along with the examples seen here. Happy querying!