Introduction
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 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 Types
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:
FLOAT
represents single-precision values that use 4 bytes and include up to 6 or 7 significant digits.DOUBLE
represent double-precision values that use 8 bytes and include up to 15 or 16 significant digits.
The basic syntax for defining FLOAT
/DOUBLE
data types is FLOAT(M,D)
/DOUBLE(M,D)
.
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.
Fixed-Point Types
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 DECIMAL(P,D)
, where 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-Value Types
The 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:
DATETIME
,TIMESTAMP
DATE
TIME
YEAR
DATETIME, TIMESTAMP
To store date and time values, use either DATETIME
or 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:
DATETIME
values range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.TIMESTAMP
values 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 DATETIME(p)
/TIMESTAMP(p)
.
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
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
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
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
andVARCHAR
BINARY
andVARBINARY
BLOB
andTEXT
ENUM
SET
CHAR and VARCHAR
CHAR
and 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 CHAR(N)
syntax. 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
BINARY
and VARBINARY
data types are similar to the previously mentioned CHAR
and VARCHAR
. The main difference between these two groups is that BINARY
and 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 BINARY(N)
, where 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
Both BLOB
and 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
The 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
Like ENUM
, the 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.
Note: Learn how to manipulate data strings using MySQL string function.
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 GEOMETRY
, POINT
, LINESTRING
, and POLYGON
.
GEOMETRY
stores any type of a geometry/spatial value.POINT
is used for a single point value.LINESTRING
stores a collection of multiple points that form a line.POLYGON
is 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 GEOMETRYCOLLECTION
, MULTIPOINT
, MULTILINE
, and MULTIPOLYGON
.
GEOMETRYCOLLECTION
stores a collection of geometry/spatial values.MULTIPOINT
is used for storing a collection of multiple point values.MULTILINE
stores a collection of multiple lines.MULTIPOLYGON
is 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.
Conclusion
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.
Đăng ký liền tay Nhận Ngay Bài Mới
Subscribe ngay
Cám ơn bạn đã đăng ký !
Lỗi đăng ký !
Add Comment