When setting up a database in MySQL, the user must assign a name and a data type to each column in a table. This process helps the system understand exactly what kind of data will be stored and how it will be processed, as well as determines the amount of memory required.
There are several main classes of data types in MySQL tables, including numeric, character, date and time, and others. Each class contains several unique data types. It's important to define them correctly during the initial stage of table creation to avoid issues once the full database structure is complete.
This article describes the main MySQL data types, their structure, valid values, and recommended usage.
MySQL supports all standard SQL numeric data types, which are used to store numbers. This article covers:
Each of these will be described in more detail below.
Used to store signed or unsigned integers. Signed types can store both positive and negative numbers, while unsigned types store only positive numbers, effectively doubling the range.
There are five main integer types: TINYINT
, SMALLINT
, MEDIUMINT
, INT
, and BIGINT
. The primary difference is the range of values they can store—the larger the range, the more memory is required. It's important to select the right type to efficiently allocate memory when creating tables.
Type |
Memory (bytes) |
Signed Range |
Unsigned Range |
TINYINT |
1 |
-2⁷ to 2⁷-1 |
0 to 2⁸-1 |
SMALLINT |
2 |
-2¹⁵ to 2¹⁵-1 |
0 to 2¹⁶-1 |
MEDIUMINT |
3 |
-2²³ to 2²³-1 |
0 to 2²⁴-1 |
INT |
4 |
-2³¹ to 2³¹-1 |
0 to 2³²-1 |
BIGINT |
8 |
-2⁶³ to 2⁶³-1 |
0 to 2⁶⁴-1 |
Specifying size (e.g., MEDIUMINT(6)
) does not affect the range. It's purely cosmetic and used by some tools to pad shorter numbers with spaces. You can also use the ZEROFILL
attribute to pad with zeros instead of spaces, e.g., SMALLINT(8) ZEROFILL
will display 256 as 00000256.
Used to store approximate numeric values. MySQL lets you define floating-point precision as:
FLOAT(p)
Where p is the precision from 0 to 53. If p is less than 25, FLOAT()
(single precision) is used; otherwise, DOUBLE()
(double precision) is used.
Type |
Memory (bytes) |
Value Range |
FLOAT |
4 |
~±3.4028 × 10³⁸ |
DOUBLE |
8 |
~±1.7976 × 10³⁸ |
Used to store exact values with a specified precision. The DECIMAL
or NUMERIC
types are used, typically for financial calculations where rounding is unacceptable.
Defined as:
DECIMAL(M, D)
Example:
DECIMAL(6, 3)
Stores values like -999.999 to 999.999. DECIMAL
and NUMERIC
are functionally equivalent.
Designed for storing short texts, the character data types in MySQL include CHAR
and VARCHAR
. They are similar to each other, with the main difference being how the data is stored and retrieved.
CHAR
stores a fixed-length string (from 0 to 2⁸−1 characters), which is defined at the time of table creation. If we insert a string shorter than the specified length, the remaining characters are padded with spaces.VARCHAR
data type in MySQL stores a variable-length string (from 0 to 2¹⁶−1 characters), also defined during table creation. Unlike CHAR
, it stores only the specified number of characters and allocates 1 byte to store the length of the string.If the input string exceeds the defined length for either CHAR
or VARCHAR
, it is truncated to fit the allowed length.
During table creation, if CHAR
and VARCHAR
columns are combined, MySQL will convert the CHAR
column to VARCHAR
.
To illustrate the difference, here's a table showing how strings of various lengths are stored using CHAR(5)
and VARCHAR(5)
:
String |
CHAR(5) |
VARCHAR(5) |
'A' |
'A ' |
'A' |
'Hello' |
'Hello' |
'Hello' |
'Example' |
'Examp' |
'Examp' |
The TEXT
data type in MySQL is used for storing large text-based data, while the BLOB
type is designed for binary data, such as images, compiled code, or audio. These two types are similar in many ways, but the main difference lies in how data is stored and processed:
BLOB
, sorting and comparisons are case-sensitive.TEXT
, sorting and comparisons are case-insensitive.MySQL cannot index the full length of TEXT
or BLOB
fields and does not support sorting using full-field indexes for these types.
If the length of a string exceeds the maximum allowed by a TEXT
or BLOB
type, the input will be truncated to fit the allowed size.
Below is a table showing all variants of the TEXT
type, the required memory size, and the maximum number of characters allowed:
Type |
Memory Size (bytes) |
Max Characters |
TINYTEXT |
2⁸−1 |
Up to 2⁸−1 |
TEXT |
2¹⁶−1 |
Up to 2¹⁶−1 |
MEDIUMTEXT |
2²⁴−1 |
Up to 2²⁴−1 |
LONGTEXT |
2³²−1 |
Up to 2³²−1 |
The BLOB
types follow the same structure and size limits as their TEXT
counterparts. Here's the equivalent table for BLOB
types:
Type |
Memory Size (bytes) |
Max Bytes |
TINYBLOB |
2⁸−1 |
Up to 2⁸−1 |
BLOB |
2¹⁶−1 |
Up to 2¹⁶−1 |
MEDIUMBLOB |
2²⁴−1 |
Up to 2²⁴−1 |
LONGBLOB |
2³²−1 |
Up to 2³²−1 |
MySQL provides several data types for storing date and time information. The table below summarizes each type, along with memory usage and key details:
Type |
Memory (bytes) |
Description |
DATE |
3 |
Stores only the date in the format 'YYYY-MM-DD'. Separators can be any non-numeric character (not just dashes). Range: January 1, 1000 to December 31, 9999. |
DATETIME |
8 |
Stores both date and time in the format 'YYYY-MM-DD HH:MM:SS'. Range: 00:00:00 on January 1, 1000 to 23:59:59 on December 31, 9999. |
TIMESTAMP |
4 |
Also stores date and time in the same format as DATETIME, but uses half the memory. Range: 00:00:01 on January 1, 1970 to 03:14:07 on January 9, 2038. |
TIME |
3 |
Stores only time in 'HH:MM:SS' or 'HHH:MM:SS' format for large hour values. Range: −838:59:59 to 838:59:59. |
YEAR |
1 |
Stores only the year in 'YYYY' format. Range: 1901 to 2155, plus 0000 is allowed. |
MySQL supports a native JSON
(JavaScript Object Notation) data type, introduced in version 5.7.8, offering the following advantages over storing JSON
as plain text:
JSON
.JSON
data is internally converted to a binary representation for faster access and querying.The memory required for JSON
storage is approximately equivalent to LONGTEXT
.
MySQL provides special string data types that store values from a predefined, fixed set. These include ENUM
and SET
.
ENUM
stores a single value from the defined list. It requires up to 2 bytes of memory.SET
, on the other hand, can store up to 26 values simultaneously from the list and uses up to 8 bytes of memory.For example, if a user defines the following list for ENUM
:
ENUM('a', 's', 'd')
Then the column can only contain one of the following values: 'a'
, 's'
, or 'd'
.
If the same list is defined for SET
:
SET('a', 's', 'd')
Then the column can contain any combination of the listed values, including:
'a'
's'
'd'
'a,s'
'a,d'
's,d'
'a,s,d'
''
)If a user inserts a value like ('a'
, 's'
, 'a'
) into a SET
, MySQL will automatically deduplicate the entries and sort them based on the original declaration order. So the stored value will be 'a,s'
.
This article covered the most commonly used MySQL data types for defining table columns. With this knowledge, users can select the appropriate types and design their database structure effectively. For more detailed and less frequently used data types, refer to the official MySQL documentation.
You can use Hostman cloud databases to practice working with MySQL and its different data types. Key advantages of using MySQL in the cloud include: