MySQL Data Types
A data type represents the type of data that can be stored and processed. Some common data types include integer, floating point, boolean, etc. A data type also specifies the type of operations that can be performed on that type.
MySQL supports a lot of the standard data types of SQL. These data types are divided into various categories, but most of them fall into three main categories viz. Numeric type, date and time, and string type.
Integer Data Types:
There are five integer data types that MySQL supports. These are:
| Data Type | Range | Maximum width | Description |
| INT | -2147483648 to 2147483647 (signed)
0 to 4294967295 (unsigned) |
11 digits | Normal-sized integer value. |
| TINYINT | -128 to 127(signed)
0 to 255(unsigned) |
4 digits | Very small integer values. |
| SMALLINT | -32768 to 32767 (signed)
0 to 65535 (unsigned) |
5 digits | Small integer value. |
| MEDIUMINT | -8388608 to 8388607 (signed)
0 to 16777215 (unsigned) |
9 digits | Medium-sized integer value. |
| BIGINT | -9223372036854775808 to 9223372036854775807 (signed)
0 to 18446744073709551615 (unsigned) |
20 digits | Large integer value. |
Floating-point Data Types:
There are mainly three floating-point data types that MySQL supports. These are:
| Data Type | Description |
| FLOAT(m,d) | A floating point number whose decimal precision can go up to 24 places. The display length (m) and the number of decimals (d) can be defined but they have a default value of m=10 and d = 2.
It cannot be unsigned. |
| DOUBLE(m,d) | A floating point number whose decimal precision can go up to 53 places. The display length (m) and the number of decimals (d) can be defined but they have a default value of m=16 and d = 4.
It cannot be unsigned. |
| DECIMAL(m,d) | An unpacked floating point number where each decimal is represented by one byte. Here defining m (display length) and d (decimals) is necessary. |
Date and Time Data Types:
There are mainly five date and time data types that MySQL supports. These are:
| Data Type | Range | Format |
| DATE | ‘1000-01-01’ to ‘9999-12-31’ | yyyy-mm-dd |
| DATETIME | ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ | yyyy-mm-dd hh:mm:ss |
| TIMESTAMP(m) | ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC | YYYY-MM-DD HH:MM:SS |
| TIME | ‘-838:59:59’ to ‘838:59:59’ | HH:MM:SS |
| YEAR[(2|4)] | 2 digits or 4 digits value | 4 digits by default |
String Data Types:
There are eight string data types that MySQL supports. These are:
| Data Type | Maximum Size | Description |
| CHAR(size) | 255 characters | Here, size is equal to the number of characters to store. It is used for fixed-length strings with space padded on the right to equal-size characters. |
| VARCHAR(size) | 255 characters | Here, size is equal to the number of characters to store. It is used for variable-length strings. |
| TINYTEXT(size) | 255 characters | Here, size is equal to the number of characters to store. |
| TEXT(size) | 65,535 characters | Here, size is equal to the number of characters to store. |
| MEDIUMTEXT(size) | 16,777,215 characters | Here, size is equal to the number of characters to store. |
| LONGTEXT(size) | 4GB or 4,294,967,295 characters | Here, size is equal to the number of characters to store. |
| BINARY(size) | 255 characters | Here, size is equal to the number of binary characters to store. It is used for fixed-length strings with space padded on the right to equal-size characters. |
| VARBINARY(size) | 255 characters | Here, size is equal to the number of binary characters to store. It is used for variable-length strings. |
Large Object (LOB) Data Types:
There are mainly four large object or LOB data types that MySQL supports. These are:
| Data Type | Maximum Size |
| TINYBLOB | 255 bytes |
| BLOB(size) | 65,535 bytes |
| MEDIUMBLOB | 16,777,215 bytes |
| LONGTEXT | 4GB or 4,294,967,295 characters |