Mysql data types: an overview

Bits and Bytes


1 bit can represent 2 distinct arrangements as in (1, 0).

2 bits can represent 4 distinct arrangements.

In short n bits can represent 2n distinct arrangements.

And 8 bits make 1 byte so 28 (256) distinct arrangements.

1 string character requires 1 byte. 

Numeric data types


 INT   :  Integer type is stored in 4 bytes (28x4  = 4,294,967,296  bit ) by mysql. It means that it can store values from 0 up to  4,294,967,295 or from -2147483648 to 2147483648.  Display digit numbers of the INT numbers can be set , let say, when you are creating tables. For example; If you would like a table column to contain numbers up to 3 digit, then you need to create a table column by setting the data type as INT(3). Display size should be confused with the storage capacity of INT. No matter how big or small you have display size set, INT can only store in a single cell up to the number 4,294,967,295 maximum. 

TINYINT : stores 1 byte  so it can store up to 28 distinct arrangements .

BIGINT :  has 8 byte storage so the maximum number can be stored with BIGINT is 28x8= 18446744073709551615.

DECIMAL : can store precise numbers with decimal part. 

Example: 

DECIMAL (8,2)  declares storage up to 8 digits and 2 of which is allocated for decimal points.  Values can be stored with this declaration are ranging from -999999.99 up to 999999.99. So 6 digits for integer part and 2 digits for fractional part.

FLOAT : stores the approximate values. Float when storing data rounds values. 

Example: 

FLOAT(3,2) declares storage up to 3 digits and 2 of which are the fractional part. If a user enters a value such as 1.009 it will be stored as 1.01.

String data types


VARCHAR and CHAR : store string data. Char stores data in a fixed lenght and padds zeros for values having less character than what is set. For example is a column is set with CHAR(3), values that will be stored there will have 3 characters. If a value with 2 characters typed, then it will get a space padding on right in order to have the fixed length. And CHAR can take at most 255 characters. VARCHAR on the other hand does not have a fixed length. When you set let say VARCHAR(3), it can take values up to 3 character but it will not be padding zeros to the values. And VARCHAR can be set up to 4000 characters.

TEXT : stores string data up to 65,535 characters long.

Date data types
DATE : stores the date in YYYY-MM-DD format

DATETIME : stores the date and the time YYYY-MM-DD HH:MM:SS format

TIMESTAMP : stores the time stamp