Data Types in MySQL

MySQL uses many different data types, divided into three categories:

• Numeric

• Date and Time

• String Types

Numeric Data Types

MySQL uses all the standard ANSI SQL numeric data types. The following list shows the common numeric data types and their descriptions

1. INT - A normal-sized integer that can be signed or unsigned. You can specify a width upto 11 digits.

2. TINYINT - A very small integer that can be signed or unsigned. You can specify a width upto 4 digits.

3. SMALLINT - A small integer that can be signed or unsigned. You can specify a width upto 5 digits.

4. MEDIUMINT - A medium-sized integer that can be signed or unsigned.You can specify a width upto 9 digits.

5. BIGINT - A large integer that can be signed or unsigned. You can specify a width upto 11 digits.

6. FLOAT(M,D) - A floating-point number that cannot be unsigned. You can define display length (M) and number of decimal places (D). Decimal places can go upto 24 places for FLOAT.

Date and Time Types

The MySQL date and time datatypes are:

1. DATE - It stores data in format YYYY-MM-DD, between 1000-01-01 and 9999-12-31. Eg. 17th Dec 2019 would be stored as '20191217'.

2. DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format (24-hour format) between 1000–01–01 00:00:00 and 9999-12-30 23:59:59. Eg. 3:45 in the afternoon on December 13, 1974 would be stored as '19741213154500'.

3. TIME - Stores the time in HH:MM:SS format.

String/Text Types

Most commonly used datatype in MySQL is in string format. Here are some of the most common datatypes in MySQL:

1. CHAR - A fixed-length string between 1 and 255 characters in length (Eg. CHAR(5)). The example shows a specified length while storing.

2. VARCHAR(M) - A variable-length string between 1 and 255 characters in length (Eg. VARCHAR(25)).

NOTE: You must specify the length while using VARCHAR.

3. BLOB/TEXT - BLOB corresponds to Binary Large OBjects. This field has a maximum length of 65535 characters. This datatype is used to store large amounts of binary data, such as images, files, etc.

Fields defined as TEXT are used to store large amounts of data like Address, Description, etc.

NOTE: You need not specify length with BLOB/TEXT.

More to Know:  

Similarly TINYBLOB/TINYTEXT, MEDIUMBLOB/MEDIUMTEXT and LONGBLOB/LONGTEXT exist for maximum length of 255, 16777215 & 4294967295 characters respectively.