JIYIK CN >

MySQL provides a variety of convenient data types for storing expected input into its database. Knowing the correct data type to use to store special data is key to ensuring an optimized and efficient database.

This tutorial explains the format and storage of telephone numbers in a MySQL database.

Understanding MySQL Data Types

MySQL generally has three data types:

1. Numbers ( INT, BIT, FLOATetc.)
2. String ( CHAR, VARCHAR, TEXTetc.)
3. Date and time ( DATE, DATETIME, TIMESTAMPetc.)

These general data types also have subtypes for specific cases of data processing in the database.

Check out this reference on various RDBMS (relational database management system) data types from w3schools for further reading.

When dealing with phone numbers, you might quickly consider using the Number data type. However, because phone numbers come in various formats (country codes, separators, etc.) and have special characters, this can be problematic.

Since a phone number can be up to fifteen (15) digits in length, according to the International Telecommunication Union, a string type CHAR, VARCHARor TINYTEXT, is the best consideration.

CHARand TINYTEXTare limited to two hundred fifty-five (255) characters, while VARCHARuses dynamic memory allocation to store data, up to a defined limit between 0 and 65535.

Let's take the example of storing phone numbers for each data type to determine the most appropriate choice.

CHARStoring phone numbers in MySQL using

CHARThe data type (short for character) can store fixed-length strings between 0 and 255 characters. A column that implements CHAR can specify an upper constraint between 0 and 255, and MySQL expects each string in the column to be the same size.

When you enter a string shorter than the specified constraint, MySQL fills the remaining space with spaces.

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

This can be discussed further in this official MySQL documentation.

To illustrate, let's 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's 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 length of the phone number is fixed to 15 characters, the CHAR data type will provide efficient storage. Also, it can be used as an index for filtering purposes.

However, the CHAR data type might not be efficient in managing memory for applications that use variable-sized phone numbers (global applications) because of the padding with spaces, as described previously.

TINYTEXTStoring phone numbers in MySQL using

TINYTEXTCHARThe data type is the smallest TEXT type data type. It has the same memory limit as the data type between 0 and 255 characters .

However, unlike CHAR, it can dynamically allocate space for the passed value based on its character length. Therefore, for this example of storing phone numbers, it provides better memory efficiency than CHAR.

It does have the disadvantage of having no default value, making it not indexable for sorting or aggregation.

Now, let's 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 the expected result. Check out this reference for more information about MySQL TINYTEXT and other TEXT data types.

VARCHARStoring phone numbers in MySQL using

The final recommended way to handle phone numbers in MySQL is to use VARCHARthe VARCHAR data type. VARCHAR provides the flexibility of dynamic memory allocation when the length of the phone number varies among database users.

It usually allocates two (2) additional bytes to store the length information. Therefore, if a length of six (6) characters is stored, VARCHAR needs to allocate a total of 8 bytes of memory.

Columns that specify the VARCHAR data type are also indexable for sorting, aggregation, and primary/foreign key constraints.

Let's create a third table implementing VARCHAR for phone number assignments.

-- 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 any of the three data types above is sufficient to handle phone numbers in a MySQL database.

However, choosing the most appropriate data type in terms of memory efficiency and speed depends on the intended database application.