MySQL Data Types
A data type specifies the type of data to be stored, the possible values, the type of operations that can be performed, and the way to process that data. A data type can vary from integer, floating point, boolean, etc., to many more and thus are divided into various categories.
Numeric Data Types:
There are mainly eight numeric 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 | Minimal 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. |
| 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 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 |