How to Format and Store Phone Numbers in MySQL

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 SQL Data Types for MySQL, SQL Server, and MS Access (w3schools.com)") 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.