Specifying MySQL data field datatypes

Well, choosing proper datatypes greatly influence the performance of a database, that’s why it is important to understand and choose the correct datatype for each database field. And as you remember, a group of fields form a record.

There are lots of different datatypes, and we are going to cover some of the most used:


The VARCHAR stores data in variable length format from 1 to 255 characters. For example, firstname VARCHAR (25). Here, firstname is the name of the database field.

INT (M) [Unsigned]

The INT stores integers from -2147483648 to 2147483647. “Unsigned” is optional, and if used, the range changes from 0 to 4294967295. For example, distance INT unsigned.


The CHAR datatype stores fixed length strings ranging from 1 to 255 characters. The CHAR is much faster than VARCHAR.


The FLOAT datatype stores decimal numbers. For example, length (5,2). Here, 5 represents up to 5 characters and 2 decimal places.


These datatypes are used to store strings of 255 – 65535 characters. TEXT data is compared case insensitively, and BLOB is compared case sensitively.


These datatypes allow you to specify a set of up to 64 values that can be chosen. For example, fruits SET (“apple”, “pear”, “orange”). This means that fruits field can hold:

“apple, pear, orange”
“pear, orange”
and so on…

… , and if fruits ENUM (“apple”, “pear”, “orange”), than:


… only, because with ENUM only one value may be chosen.


This datatype represents data related information that ranges from 0000-00-00 to 9999-12-31.

There are several options that can be placed after any datatype:


Use this option if you need that no two records could have the same values. For example, phone INT PRIMARY KEY.


When a new record (row) is inserted into a database, field value with this datatype is automatically incremented by 1. For example, ID INT AUTO_INCREMENT.


Tells that the field value can never be assigned a NULL value. For example, ID INT NOT NULL AUTO_INCREMENT.

Well, lets go to the next tutorial Manipulating the database.