Тип данных для записи номера телефона - Каталог телефонных номеров компаний России и СНГ

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.

Источник

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
;

Источник

I need to store phone numbers in a table. Please suggest which datatype should I use?
Wait. Please read on before you hit reply..

This field needs to be indexed heavily as Sales Reps can use this field for searching (including wild character search).

As of now, we are expecting phone numbers to come in a number of formats (from an XML file). Do I have to write a parser to convert to a uniform format? There could be millions of data (with duplicates) and I dont want to tie up the server resources (in activities like preprocessing too much) every time some source data comes through..

Any suggestions are welcome..

Update: I have no control over source data. Just that the structure of xml file is standard. Would like to keep the xml parsing to a minimum.
Once it is in database, retrieval should be quick. One crazy suggestion going on around here is that it should even work with Ajax AutoComplete feature (so Sales Reps can see the matching ones immediately). OMG!!

asked Sep 16, 2008 at 17:57

JohnJohn

9631 gold badge6 silver badges8 bronze badges

Does this include:

International numbers?
Extensions?
Other information besides the actual number (like «ask for bobby»)?

If all of these are no, I would use a 10 char field and strip out all non-numeric data. If the first is a yes and the other two are no, I’d use two varchar(50) fields, one for the original input and one with all non-numeric data striped and used for indexing. If 2 or 3 are yes, I think I’d do two fields and some kind of crazy parser to determine what is extension or other data and deal with it appropriately. Of course you could avoid the 2nd column by doing something with the index where it strips out the extra characters when creating the index, but I’d just make a second column and probably do the stripping of characters with a trigger.

Update: to address the AJAX issue, it may not be as bad as you think. If this is realistically the main way anything is done to the table, store only the digits in a secondary column as I said, and then make the index for that column the clustered one.

answered Sep 16, 2008 at 18:02

KearnsKearns

1,0797 silver badges10 bronze badges

We use varchar(15) and certainly index on that field.

The reason being is that International standards can support up to 15 digits

Wikipedia — Telephone Number Formats

If you do support International numbers, I recommend the separate storage of a World Zone Code or Country Code to better filter queries by so that you do not find yourself parsing and checking the length of your phone number fields to limit the returned calls to USA for example

answered Sep 16, 2008 at 18:03

Use CHAR(10) if you are storing US Phone numbers only. Remove everything but the digits.

answered Sep 16, 2008 at 18:00

Joseph BuiJoseph Bui

1,69116 silver badges22 bronze badges

I’m probably missing the obvious here, but wouldn’t a varchar just long enough for your longest expected phone number work well?

If I am missing something obvious, I’d love it if someone would point it out…

answered Sep 16, 2008 at 18:00

coricori

8,5967 gold badges45 silver badges80 bronze badges

I would use a varchar(22). Big enough to hold a north american phone number with extension. You would want to strip out all the nasty ‘(‘, ‘)’, ‘-‘ characters, or just parse them all into one uniform format.

Alex

answered Sep 16, 2008 at 18:00

Alex FortAlex Fort

18.2k5 gold badges42 silver badges51 bronze badges

nvarchar with preprocessing to standardize them as much as possible. You’ll probably want to extract extensions and store them in another field.

answered Sep 16, 2008 at 17:59

John SheehanJohn Sheehan

76.8k30 gold badges159 silver badges194 bronze badges

SQL Server 2005 is pretty well optimized for substring queries for text in indexed varchar fields. For 2005 they introduced new statistics to the string summary for index fields. This helps significantly with full text searching.

answered Sep 16, 2008 at 18:02

Joseph DaigleJoseph Daigle

47.2k10 gold badges50 silver badges73 bronze badges

using varchar is pretty inefficient. use the money type and create a user declared type «phonenumber» out of it, and create a rule to only allow positive numbers.

if you declare it as (19,4) you can even store a 4 digit extension and be big enough for international numbers, and only takes 9 bytes of storage. Also, indexes are speedy.

answered May 3, 2011 at 16:12

fjleonfjleon

3413 silver badges10 bronze badges

Normalise the data then store as a varchar. Normalising could be tricky.

That should be a one-time hit. Then as a new record comes in, you’re comparing it to normalised data. Should be very fast.

answered Sep 16, 2008 at 17:59

Iain HolderIain Holder

14.1k10 gold badges65 silver badges86 bronze badges

Since you need to accommodate many different phone number formats (and probably include things like extensions etc.) it may make the most sense to just treat it as you would any other varchar. If you could control the input, you could take a number of approaches to make the data more useful, but it doesn’t sound that way.

Once you decide to simply treat it as any other string, you can focus on overcoming the inevitable issues regarding bad data, mysterious phone number formating and whatever else will pop up. The challenge will be in building a good search strategy for the data and not how you store it in my opinion. It’s always a difficult task having to deal with a large pile of data which you had no control over collecting.

answered Sep 16, 2008 at 18:05

unicorn.ninjaunicorn.ninja

Use SSIS to extract and process the information. That way you will have the processing of the XML files separated from SQL Server. You can also do the SSIS transformations on a separate server if needed. Store the phone numbers in a standard format using VARCHAR. NVARCHAR would be unnecessary since we are talking about numbers and maybe a couple of other chars, like ‘+’, ‘ ‘, ‘(‘, ‘)’ and ‘-‘.

answered Sep 16, 2008 at 18:09

Magnus JohanssonMagnus Johansson

27.8k19 gold badges104 silver badges164 bronze badges

Use a varchar field with a length restriction.

agf

167k42 gold badges283 silver badges234 bronze badges

answered Sep 16, 2008 at 18:00

It is fairly common to use an «x» or «ext» to indicate extensions, so allow 15 characters (for full international support) plus 3 (for «ext») plus 4 (for the extension itself) giving a total of 22 characters. That should keep you safe.

Alternatively, normalise on input so any «ext» gets translated to «x», giving a maximum of 20.

answered Jul 22, 2013 at 9:34

Rob GRob G

744 bronze badges

It is always better to have separate tables for multi valued attributes like phone number.

As you have no control on source data so, you can parse the data from XML file and convert it into the proper format so that there will not be any issue with formats of a particular country and store it in a separate table so that indexing and retrieval both will be efficient.

Thank you.

answered Aug 12, 2017 at 14:36

I realize this thread is old, but it’s worth mentioning an advantage of storing as a numeric type for formatting purposes, specifically in .NET framework.

.DefaultCellStyle.Format = "(###)###-####" // Will not work on a string

ΩmegaMan

28.4k10 gold badges98 silver badges117 bronze badges

answered Mar 23, 2017 at 1:14

Mr. TripodiMr. Tripodi

8091 gold badge6 silver badges7 bronze badges

Use data type long instead.. dont use int because it only allows whole numbers between -32,768 and 32,767 but if you use long data type you can insert numbers between -2,147,483,648 and 2,147,483,647.

answered Apr 2, 2020 at 20:31

For most cases, it will be done with bigint

Just save unformatted phone numbers like: 19876543210, 02125551212, etc.

Check the topic about bigint vs varchar

answered Dec 19, 2022 at 15:11

job.js.orgjob.js.org

2,4272 gold badges18 silver badges29 bronze badges

Источник

I need to store phone numbers in a table. Please suggest which datatype should I use?
Wait. Please read on before you hit reply..

This field needs to be indexed heavily as Sales Reps can use this field for searching (including wild character search).

Any suggestions are welcome..

asked Sep 16, 2008 at 17:57

JohnJohn

9631 gold badge6 silver badges8 bronze badges

Does this include:

International numbers?
Extensions?
Other information besides the actual number (like «ask for bobby»)?

answered Sep 16, 2008 at 18:02

KearnsKearns

1,0797 silver badges10 bronze badges

We use varchar(15) and certainly index on that field.

The reason being is that International standards can support up to 15 digits

Wikipedia — Telephone Number Formats

answered Sep 16, 2008 at 18:03

Use CHAR(10) if you are storing US Phone numbers only. Remove everything but the digits.

answered Sep 16, 2008 at 18:00

Joseph BuiJoseph Bui

1,69116 silver badges22 bronze badges

I’m probably missing the obvious here, but wouldn’t a varchar just long enough for your longest expected phone number work well?

If I am missing something obvious, I’d love it if someone would point it out…

answered Sep 16, 2008 at 18:00

coricori

8,5967 gold badges45 silver badges80 bronze badges

Alex

answered Sep 16, 2008 at 18:00

Alex FortAlex Fort

18.2k5 gold badges42 silver badges51 bronze badges

nvarchar with preprocessing to standardize them as much as possible. You’ll probably want to extract extensions and store them in another field.

answered Sep 16, 2008 at 17:59

John SheehanJohn Sheehan

76.8k30 gold badges159 silver badges194 bronze badges

answered Sep 16, 2008 at 18:02

Joseph DaigleJoseph Daigle

47.2k10 gold badges50 silver badges73 bronze badges

using varchar is pretty inefficient. use the money type and create a user declared type «phonenumber» out of it, and create a rule to only allow positive numbers.

if you declare it as (19,4) you can even store a 4 digit extension and be big enough for international numbers, and only takes 9 bytes of storage. Also, indexes are speedy.

answered May 3, 2011 at 16:12

fjleonfjleon

3413 silver badges10 bronze badges

Normalise the data then store as a varchar. Normalising could be tricky.

That should be a one-time hit. Then as a new record comes in, you’re comparing it to normalised data. Should be very fast.

answered Sep 16, 2008 at 17:59

Iain HolderIain Holder

14.1k10 gold badges65 silver badges86 bronze badges

answered Sep 16, 2008 at 18:05

unicorn.ninjaunicorn.ninja

answered Sep 16, 2008 at 18:09

Magnus JohanssonMagnus Johansson

27.8k19 gold badges104 silver badges164 bronze badges

Use a varchar field with a length restriction.

agf

167k42 gold badges283 silver badges234 bronze badges

answered Sep 16, 2008 at 18:00

Alternatively, normalise on input so any «ext» gets translated to «x», giving a maximum of 20.

answered Jul 22, 2013 at 9:34

Rob GRob G

744 bronze badges

It is always better to have separate tables for multi valued attributes like phone number.

Thank you.

answered Aug 12, 2017 at 14:36

I realize this thread is old, but it’s worth mentioning an advantage of storing as a numeric type for formatting purposes, specifically in .NET framework.

.DefaultCellStyle.Format = "(###)###-####" // Will not work on a string

ΩmegaMan

28.4k10 gold badges98 silver badges117 bronze badges

answered Mar 23, 2017 at 1:14

Mr. TripodiMr. Tripodi

8091 gold badge6 silver badges7 bronze badges

answered Apr 2, 2020 at 20:31

For most cases, it will be done with bigint

Just save unformatted phone numbers like: 19876543210, 02125551212, etc.

Check the topic about bigint vs varchar

answered Dec 19, 2022 at 15:11

job.js.orgjob.js.org

2,4272 gold badges18 silver badges29 bronze badges

Источник

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

asked Dec 24, 2015 at 18:58

ReynierPMReynierPM

1,69010 gold badges29 silver badges45 bronze badges

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.

answered Dec 24, 2015 at 19:19

Hannah Vernon♦Hannah Vernon

68.4k22 gold badges164 silver badges303 bronze badges

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 CAPs (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.

answered Dec 26, 2015 at 21:00

VéraceVérace

28.5k8 gold badges64 silver badges80 bronze badges

Hmm. Phone numbers are made of numbers. Using varchar allows user to store any type of formatting, with ( or not, with — or . and it quickly creates a mess with your data. A phone # format is «country» dependent, the mask should be tied to the country. Extension is an extension and is optional, so it should be stored in a «extension field». (int also).
For 1-800-DBA-HELP, i would convert that on the fly and store actual number. If you really need these human readable phone #, store it in separate varchar field.

answered May 1, 2017 at 20:24

I usually stores the Phone Numbers in simple text. Formatting and display leave it to the client code.

Here, more than, how you store? what you are going to do with that phone number is really important.

If your business wants to perform outbound calls from your system, application will extract only numbers. If your business wants to make international calls, store country code and area code in separate columns.

If your business wants for reporting, the application will format and display with extension and numbers separately.

From my understanding, designing universal data model for phone number is not a good idea. Each country has different numbers, extensions and area code apart from country code. Also, I came to know, some countries does not have area code.

This may not answer your question but it will help to widen our understanding. Thank you.

answered May 3, 2017 at 11:05

Rathish Kumar BRathish Kumar B

2,1345 gold badges20 silver badges34 bronze badges

Источник

Learning About MySQL Data Types
Use CHAR to Store Phone Numbers in MySQL
Use TINYTEXT to Store Phone Numbers in MySQL
Use VARCHAR to Store Phone Numbers in MySQL

Format and 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:

Numeric (INT, BIT, FLOAT, etc.)
String (CHAR, VARCHAR, TEXT, etc.)
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.

Источник

тип данных mysql для номера телефона и адреса

Если tel_number больше 15 бит, какой тип данных я могу использовать, мне лучше использовать Bigint(20) ?

например, если у меня есть код страны для Канады я могу использовать +2 или 002. Что лучше для обработки?

Спасибо за совет.

10 ответов

ну, лично я не использую числовой тип данных для хранения телефонных номеров и другая информация.

Как вы храните номер, скажем, 001234567? Это закончится как 1234567, потеряв ведущие нули.

конечно, вы всегда можете оставить его, но это при условии, что вы точно знаете, сколько цифр должно быть.

Это не ответ на весь ваш пост,
Просто мои 2 цента

на самом деле вы можете использовать varchar для телефонного номера. Вам не нужен int, потому что вы не собираетесь выполнять арифметику на числах.

храните их как два поля для телефонных номеров — » номер «и» маска » как TinyText типы которые не нуждаются в более чем 255 пунктов.

прежде чем хранить файлы, мы анализируем номер телефона, чтобы получить форматирование, которое было использовано, и которое создает маску, мы затем храним число только цифры, например

вход: (0123) 456 7890
Номер: 01234567890
Маска: (nnnn)_nnn_nnnn

теоретически это позволяет нам выполнять поиск по числовое поле, такое как получение всех телефонных номеров, которые начинаются с определенного кода области, без необходимости беспокоиться о том, как это было введено пользователями

Я обычно храню телефонные номера как BIGINT в формате E164.

E164 никогда не начинается с 0, причем первые несколько цифр являются кодом страны.

etc. будет храниться как 441234567890 .

Я бы использовал varchar для телефонных номеров. таким образом, вы также можете хранить + и (), что иногда видно в номерах tel (как вы сами упомянули). и вам не придется беспокоиться об использовании всех битов в целых числах.

Я не уверен, что это хорошая идея использовать целые числа вообще. Некоторые числа могут содержать специальные символы (например, # как часть расширения), которые вы также можете обрабатывать. Поэтому я бы предложил использовать varchars.

если хранение менее 1 млн записей, а высокая производительность не является проблемой для varchar (20) / char(20) в противном случае я обнаружил, что для хранения даже 100 миллионов глобальных бизнес-телефонов или личных телефонов int лучше всего. Причина: меньший ключ — > более высокая скорость чтения / записи, также форматирование может допускать дубликаты.

1 телефон в char (20) = 20 байт против 8 байт bigint (или 10 против 4 байт int для местных телефонов, до 9 цифр), меньше записей может ввести блок индекса => больше блоков = > дополнительные поиски, см. этой для получения дополнительной информации (writen для Mysql, но это должно быть верно для других реляционных баз данных).

вот пример телефонных таблиц:

или с обработкой/разделение перед вставкой (2+2+4+1 = 9 байт)

также «номер телефона не является номером», на мой взгляд, относительно типа телефонных номеров. Если мы говорим о внутренней мобильной телефонной книге, то строки в порядке, как пользователь может пожелать магазин GSM хэш-коды. Если хранить E164 телефоны, bigint-лучший вариант.

посмотреть рекомендация Twilio для получения дополнительной информации о локализации телефоны.

INT (10) не означает 10-значное число, это означает целое число с шириной отображения 10 цифр. Максимальное значение для INT в MySQL-2147483647 (или 4294967295, если без знака).

вы можете использовать BIGINT вместо INT для хранения его как числового. С помощью BIGINT сохранит вам 3 байта в строке над VARCHAR (10).

хранить «страна + область + номер отдельно». Вы можете попробовать использовать VARCHAR (20), это позволяет вам хранить международные номера телефонов, если возникнет необходимость.

varchar или текст должны быть лучшими типами данных для хранения мобильных номеров, я думаю.

SQL Мобильный телефон — char или int?

В каком типе поля SQL лучше хранить мобильный телефон — char или int?

в начале было слово «+»

Chukcha, а зачем хранить +? Например, я захочу получить номера определенного оператора, LIKE для CHAR будет настолько же производителен, как и диапазон для INT?

Ок. Храните в двух полях — форматированное по вашему желанию

и чистое от формата 🙂

Если хотите использовать для поиска — char без формата номера, для быстрого доступа — отформатировнный

А еще. отдельным полем код оператора

лучше всего в varchar

будет смотреться гармоничнее, нежели

да и пользоваться тоже удобнее

Храните номера в соответствии со стандартом E.164. Символы, отличные от цифр, не нужны.

Хранить в varchar. Сами так храним.

Попробуйте для теста записать 9999999999 в поле int

unsigned int имеется в виду.

Я бы масштабируемость сразу бы заложил.

3 поля: регион, код сети, номер телефона.

Допустим расширяемость, отчеты, да многое можно будет оптимизировать потом.

И выводить проще, если в отдельных полях будет )

Друзья, объясните, когда можно использовать один KEY для двух значений? Вот у меня есть номер телефона, который разбит на FOREIGN KEY `country` и непосредственно сам номер телефона. Я не вижу смысл создавать отдельные ключи для страны и номера, могу ли я сделать один ключ? Или для выборки это не катит? Нужно будет выбирать конкретный номер телефона, то-есть, выборка за `phone`, но использовать форматирование вывода в зависимости от `country`.

Если я хочу, чтобы целый номер country + phone был уникальным, по аналогии, нужно создавать один ключ UNIQUE?

Какой тип данных лучше всего подходит для телефонного номера в MySQL и каким должно быть сопоставление типов Java для него?

Я использую MySQL с шаблоном Spring JDBC для своего веб-приложения. Мне нужно сохранить номер телефона только цифрами (10). Я немного смущен типом данных с использованием типа данных.

Какой тип данных для него предпочтительнее в MySQL?
Каким должен быть тип данных Java в классах Bean (POJO) для этого?
Как я могу проверить этот тип данных с помощью проверок / ограничений javax для длины, а также разрешенной только цифры?

Строки и VARCHAR.

Не пытайтесь сохранять телефонные номера как настоящие. это испортит форматирование, удалит предыдущие 0 и другие нежелательные вещи.

Вы можете, если захотите, ограничить ввод данных пользователем только числовыми значениями, но даже в этом случае сохраняйте резервные данные в виде символов / строк, а не чисел.

Прежде чем пытаться вводить какие-либо ограничения длины, проверки или маски (например, XXX-XXXX-XX), узнайте о мире в целом и о том, как различаются их длина и форматирование номеров.

В телефонных номерах могут использоваться нечисловые символы. Яркий пример + — замена 00 в начале международного номера.

Источник

I am using MySQL with the Spring JDBC template for my web application. I need to store phone numbers with only digits (10). I am a little bit confused about data type using data type.

What is the preferable data type for it in MySQL?
What should be the Java data type in Bean (POJO) classes for that?
How can I validate that datatype with javax validations/constraints for length and also only digit allowed?

asked Jun 22, 2014 at 17:25

Vishal ZanzrukiaVishal Zanzrukia

4,8234 gold badges37 silver badges80 bronze badges

Strings & VARCHAR.

Do not try storing phone numbers as actual numbers. it will ruin the formatting, remove preceding 0s and other undesirable things.
You may, if you choose to, restrict user inputs to just numeric values but even in that case, keep your backing persisted data as characters/strings and not numbers.
Be aware of the wider world and how their number lengths and formatting differ before you try to implement any sort of length restrictions, validations or masks (eg XXX-XXXX-XX).
Non numeric characters can be valid in phone numbers. A prime example being + as a replacement for 00 at the start of an international number.

Edited in from conversation in comments:

It is one of the bigger UI mistakes that phone numbers have anything to do with numerics. It is much better to think of and treat them like addresses, it is closer to what they actually are and represent than phone «numbers».

answered Jun 22, 2014 at 17:28

In MySQL -> INT(10) does not mean a 10-digit number, it means an integer with a display width of 10 digits. The maximum value for an INT in MySQL is 2147483647 (or 4294967295 if unsigned).

You can use a BIGINT instead of INT to store it as a numeric. Using
BIGINT will save you 3 bytes per row over VARCHAR(10).

If you want to Store «Country + area + number separately». Try using a VARCHAR(20). This allows you the ability to store international phone numbers properly, should that need arise.

answered Mar 9, 2016 at 10:36

Irshad KhanIrshad Khan

5,5422 gold badges42 silver badges39 bronze badges

Consider using the E.164 format. For full international support, you’d need a VARCHAR of 15 digits.

See Twilio’s recommendation for more information on localization of phone numbers.

answered Feb 6, 2016 at 2:07

0050000500500005

3,5972 gold badges30 silver badges38 bronze badges

VARCHAR with probably 15-20 length would be sufficient and would be the best option for the database. Since you would probably require various hyphens and plus signs along with your phone numbers.

answered Jun 22, 2014 at 17:31

NcDreamyNcDreamy

7656 silver badges14 bronze badges

you can use var-char,String,and int ,it depends on you, if you use only country code with mobile number than you can use int,if you use special formate for number than use String or var-char type, if you use var-char then must defile size of number and restrict from user.

answered Feb 21, 2015 at 6:22

damondamon

657 bronze badges

It’s all based on your requirement. if you are developing a small scale app and covers only specific region (target audience), you can choose BIGINT to store only numbers since VARCHAR consumes more byte than BIGINT ( having optimal memory usage design matters ). but if you are developing a large scale app and targets global users and you have enough database capabilities to store data, you can definitely choose VARCHAR.

answered Aug 29, 2018 at 6:30

best data type for phone number is ulong
why ?
varchar and string type is for character and when you want doing search on it sql will do that on bit on bit
long is good but not best because conains negative numbers .
so the ulong type is numbers model and not contains negative numbers.

answered Aug 5, 2022 at 5:57

String
Varchar

This is my opinion for my database as recommended by my mentor

answered Jan 13, 2020 at 3:31

My requirement is to display 10 digit phone number in the jsp. So here’s the setup for me.
MySQL: numeric(10)

Java Side:

@NumberFormat(pattern = "#")  
private long mobileNumber;

and it worked!

answered Aug 15, 2018 at 5:47

SureshSuresh

1,3072 gold badges20 silver badges26 bronze badges

In mysql: BIGINT.
In java: Long.

answered Oct 13, 2017 at 8:51

Источник

Learning About MySQL Data Types

Use CHAR to Store Phone Numbers in MySQL

Use TINYTEXT to Store Phone Numbers in MySQL

Use VARCHAR to Store Phone Numbers in MySQL

тип данных mysql для номера телефона и адреса

10 ответов

SQL Мобильный телефон — char или int?

Какой тип данных лучше всего подходит для телефонного номера в MySQL и каким должно быть сопоставление типов Java для него?

Use `CHAR` to Store Phone Numbers in MySQL

Use `TINYTEXT` to Store Phone Numbers in MySQL

Use `VARCHAR` to Store Phone Numbers in MySQL