MySQL data types are important to understand before you start creating and working with a MySQL database. If you properly assign each column, you ensure that the database is optimized and data is safely stored.
In this tutorial you will learn about the different MySQL data types.
What are Data Types in MySQL?
A name and a data type define each column in a database table. The specified data type tells MySQL what kind of values it will store, how much space they require, and what type of operations it can perform with this type of data.
Note: Check out our MySQL Cheat Command Sheet and find the most important MySQL commands in one place.
MySQL Data Types
There many different data types you can store in a MySQL table.
They are grouped into five main categories:
- Numeric data types
- Date and time data types
- String data types
- Spatial data types
- JSON data types
Read on to learn more about each group and see which data types they include.
Numeric Data Types
When storing numbers in a database column, use one of the numeric data types. MySQL supports both exact and approximate numeric data types.
The numeric category is further subdivided into the following groups:
- Integer data types
- Floating-point data types
- Fixed-point data types
- Bit-value data types
Integer data types are used for whole numbers (integers). They include both positive and negative values. However, they do not handle fractional numbers.
Therefore, numbers such as 30 and -5435 can be stored as integer data types, while 5,3 or 1/5 cannot.
Integer types are signed or unsigned. They are further subdivided based on their size, differing by their length and range.
|Bytes||Range (unsigned)||Range (signed)|
|TINYINT||1||from 0 to 255||from -128 to 127|
|SMALLINT||2||from 0 to 65535||from -32768 to 32767|
|MEDIUMINT||3||from 0 to 16777215||from -8388608 to 8388607|
|INT||4||from 0 to 4294967295||from -2147483648 to 2147483647|
|BIGINT||8||from 0 to 18446744073709551615||from -9223372036854775808 to 9223372036854775807|
TINYINT is a very small integer that uses 1 byte of storage. It consists of up to 4 digits. Its unsigned range is from 0 to 255. If it is signed, it has a range from -128 to 127.
SMALLINT is a small integer that uses 2 bytes of storage. It consists of up to 5 digits. Its unsigned range is from 0 to 65535. When signed, it has a range from -32768 to 32767.
MEDIUMINT is a medium-sized integer that uses 3 bytes of storage. It has up to 9 digits. If unsigned, it has a range from 0 to 16777215. Signed, its minimum value is -8388608, while its maximum value is 8388607.
INT is an integer that uses 4 bytes of storage. It uses up to 11 digits. When unsigned, it ranges from 0 to 4294967295. If it is signed, it has the range from -2147483648 to 2147483647.
BIGINT is a big integer that uses 8 bytes of storage. It has up to 20 digits. Its minimum signed value is 0, while its maximum signed value is 18446744073709551615. If signed, it has the range from -9223372036854775808 to 9223372036854775807.
Floating-point numeric data types are rational numbers used for representing approximate values. Use floating-point data types for high-precision calculations.
Floating-point types include:
FLOATrepresents single-precision values that use 4 bytes and include up to 6 or 7 significant digits.
DOUBLErepresent double-precision values that use 8 bytes and include up to 15 or 16 significant digits.
The basic syntax for defining
DOUBLE data types is
M represents the total number of digits, while
D is the number of decimals. For example, the value 5143,234 would be defined as 7,3 as it has 7 digits in total and 3 digits after the decimal point.
You can also use the
FLOAT(P) syntax to specify a floating-point data type, where
P specifies the precision. If
P has a value from 0 to 23, it is a single-precision column. If the precision is between 24 and 53, it is a double-precision column.
|Bytes||Range (unsigned)||Range (signed)|
|FLOAT||4||from 1.175494351E-38 to 3.402823466E+38||from -3.402823466E+38 to -1.175494351E-38|
|DOUBLE||8||from 0 and 2.22507385850720 14E- 308 to 1.797693134862315 7E+ 308||from -1.7976931348623 157E+ 308 to -2.22507385850720 14E- 308|
Note: Do not use floating-point numbers when comparing values, especially when dealing with monetary data. Instead, use the
DECIMAL data type for such use cases.
To store exact numeric values, use the fixed-point data type –
DECIMAL. As it represents an exact number, this data type is mostly used for data that relies on precise values (such as monetary data).
The basic syntax is
P stands for precision (the number of significant digits) and
D stands for scale (the number of digits after the decimal point).
The maximum number of digits for precision is 65, while the maximum value for scale is 30.
If you do not define the precision and scale, the column uses default values. By default, the values for P,D are 10,0.
BIT data type stores binary values. When creating a column that will store such values, you define the number of bit values ranging from 1 to 64.
The syntax for this MySQL data type is
BIT(N). If you do not specify
N, the default value is 1.
Date and Time Data Types
Date and time are commonly used data types. Whether you are storing a time of a data entry, a date of birth or the current timestamp, you use one of the following columns.
Date and time data types include:
To store date and time values, use either
TIMESTAMP. Both data types store information in the YYYY-MM-DD HH:MM:SS format. It includes the year, month, day, hour, minutes, and seconds.
The main difference between the two is their range:
DATETIMEvalues range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
TIMESTAMPvalues range from 1970-01-01 00:00:01 to 2038-01-19 03:14:07.
You can include fractional seconds for both options. To do so, specify the precision following the syntax
For example, to store the timestamp 10:53 PM on March 1st, 2021, with three fractional seconds, specify the data type
TIMESTAMP(3). The entry is stored as: 2021-03-01 22:53:35.346.
Note: Learn how to work with date/time timestamps in MySQL with our in-depth tutorial on using the MySQL Date/Time Function.
DATE is used for storing date values in the format YYYY-MM-DD (year, month, date).
The data type supports the range 1000-01-01 to 9999-12-31.
TIME is used to store time values as HH-MM-SS (hours, minutes, seconds) or HHH-MM-SS. Entries showing elapsed time or time differences are stored and retrieved in a longer format (if they need more digits for hours).
The data type supports the range 1000-01-01 to 9999-12-31.
YEAR stores year values in the format YYYY. It supports values within the range 1901-2155.
While versions older than MySQL 5.7.5 supported both 2 and 4 digit entries for
YEAR, there has been no 2-digit support since the 5.7.5 release.
String Data Types
When storing strings of data, use one of the string data types. They can contain letters, numbers, images, or files.
Accordingly, there are several different string data types:
CHAR and VARCHAR
VARCHAR are data types used to store non-binary strings. The main difference between the two is how they store data.
CHAR stores fixed-length strings (up to 255 characters). When creating a
CHAR column, you specify the length using the
N is the number of characters you want to take up. If you do not define the length, it uses the default value 1.
These values are stored right-padded with the specified length. Therefore, if you set up a
CHAR(5) column and store a three-character entry into it, it still takes up five characters.
VARCHAR stores variable-length strings. While the length has to be defined when creating a column, the values are not right-padded. They have a maximum limit, but the length is not fixed and varies depending on the data.
Before, the range for entries was from 0 to 255. After the release of MySQL 5.0.3 ,
VARCHAR range is up to 65 535 characters.
BINARY and VARBINARY
VARBINARY data types are similar to the previously mentioned
VARCHAR. The main difference between these two groups is that
VARBINARY are used for binary strings.
BINARY is used for fixed-length binary strings, up to 255 bytes. The main syntax for defining such a column is
N is the number of bytes.
VARBINARY stores variable-length binary strings. MySQL version 5.0.3 and newer stores up to 65 535 bytes.
BLOB and TEXT
TEXT are used to store large amounts of data.
BLOB handles Binary Large Objects (that is, large sets of binary data such as images, audio or PDF files).
There are 4 kinds of BLOB data types to use, depending on the size your data requires:
TINYBLOB(0 – 255; 255 bytes)
BLOB(0 – 65,535; 16 KB)
MEDIUMBLOB(0 – 16,777,215; 16 MB)
LONGBLOB(0 – 4,294,967,295; 4 GB)
TEXT data types are for storing longer strings of text. According to the amount of data required, there is:
TINYTEXT(0 – 255; 255 bytes)
TEXT(0 – 65,535; 16 KB)
MEDIUMTEXT(0 – 16,777,215; 16 MB)
LONGTEXT(0 – 4,294,967,295; 4 GB)
ENUM data type is used to store one of the predefined possible values in a column. The column can have up to 65535 distinct values.
When creating an
ENUM table column in MySQL, you specify a list of all the permitted values.
SET data type has a predefined list of possible values stored in the column.
The main difference between the two is that
SET allows an entry to have more than one value.
For example, if the column is defined as
SET('Red','Orange','Yellow','Green') and has four possible values in the list, one entry could have the value ‘Red’, while another could have the value ‘Red’,’Yellow’.
The maximum number of permitted values is 64.
Spatial Data Types
When storing spatial data, you can use one of the many different spatial data types that MySQL supports. They are utilized to represent information about geometric shapes and physical location.
We can divide them into two groups:
- Single geometry values
- Collections of values
Single Geometry Values
Single geometry data types include
GEOMETRYstores any type of a geometry/spatial value.
POINTis used for a single point value.
LINESTRINGstores a collection of multiple points that form a line.
POLYGONis a data type used to store a collection of multiple points that form a polygonal surface.
Collection of Values
Collection of values data types include
GEOMETRYCOLLECTIONstores a collection of geometry/spatial values.
MULTIPOINTis used for storing a collection of multiple point values.
MULTILINEstores a collection of multiple lines.
MULTIPOLYGONis used for a collection of multiple polygons.
JSON Data Types
Since version 5.7.8, MySQL included support for the native
JSON data type, allowing users to store and manage JSON documents through a database.
MySQL makes sure that the JSON documents are valid and stores them into the JSON column.
As the article outlines, there are many different MySQL data types to choose from. Having a wide variety may seem confusing at first. However, the diversity of data types will definitively help you find the one that best suits your data and optimize the database to operate at its best.