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:
VARCHAR (M)
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.
CHAR (M)
The CHAR datatype stores fixed length strings ranging from 1 to 255 characters. The CHAR is much faster than VARCHAR.
FLOAT [(M,D)]
The FLOAT datatype stores decimal numbers. For example, length (5,2). Here, 5 represents up to 5 characters and 2 decimal places.
TEXT or BLOB
These datatypes are used to store strings of 255 – 65535 characters. TEXT data is compared case insensitively, and BLOB is compared case sensitively.
SET or ENUM
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”
“apple, pear, orange”
“pear, orange”
and so on…
… , and if fruits ENUM (“apple”, “pear”, “orange”), than:
“apple”
“pear”
“orange”
… only, because with ENUM only one value may be chosen.
DATE
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:
PRIMARY KEY
Use this option if you need that no two records could have the same values. For example, phone INT PRIMARY KEY.
AUTO_INCREMENT
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.
NOT NULL
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.