Data Type for MySQL

This tutorial will tell you about data type which is used in MySQL server. The version of MySQL which I used to write this tutorial is  5.7.15. So there might be some chance that some of the data type not supported in your MySQL version.

So first thing is that what is Data type ?

In general as we know that there are some number and characters.

Number represent the some values and values further can be divided into categories like integer, real.

In computer science there must be some equivalent representation to represent these values in computer or in a program. so every computer language have its equivalent representation to represent these number.

so now we look into MySQL data type.


we can categorized the MySQL data type -

1. Numeric Data Type
2. Date and Time Data Type
3. String (characters and bytes) Data Type
4. Spatial Data Type

Numeric Data Type :

This data type is used for storing the number value.

This can be further categorized-

TINYINT -

size : A 1-byte integer. 
range : signed integer is -128 to 127 and for un-signed is 0 to 255.

SMALLINT -

size : A 2-byte integer.
range : signed integer is -32,768 to 32,767 and for un-signed is 0 to 65,535.

MEDIUMINT -

size : A 3-byte integer.
range : for this signed integer is -8,388,608 to 8,388,607 and for un-signed is 0 to 16,777,215.

INT -

size : A 4-byte integer.
range : signed integer is -2,147,483,648 to  -2,147,483,647 and for un-signed is 0 to 4,294,967,295.

BIGINT -

size : A 4-byte integer.
range : for this signed integer is -9,223,372,036,854,775,808 to
 9,223,372,036,854,775,807  and for un-signed is 0 to 18,446,744,073,709,551,615

 

DECIMAL -

A fixed point number (M,D).

M is the maximum number of digits is 65 and default 10

D is the maximum number of decimal points is 30  and default 0

FLOAT -

A small floating-point-number

DOUBLE  -

A double precision floating-point-number.

BIT -

A bit  data type is used to store bit-field value field Type (M), storing M of bits per value. default is 1 and maximum 64

BOOLEAN -

Like tinyint , a value zero considered as false and non-zero considered as true 

Date and Time Data Type

DATE -

format : YYYY-MM-DD

size: 3 bytes storage required

range : '1000-01-01' to '9999-12-31'

DATETIME - 

format : YYYY-MM-DD HH:MI:SS
size : 8 bytes storage required 
range : '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIMESTAMP - 

format : YYYY-MM-DD HH:MI:SS 
size : 4 bytes storage required
range : '1970-01-01 00:00:00' to mid-year 2037

YEAR - 

format : YYYY or YY 
size : 1 byte storage required
range : 1901 to 2155 (for YEAR(4)), 1970 to 2069 (for YEAR(2))