MySQL Data Types

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.

MySQL Data Types

There many different data types you can store in a MySQL table.

They are grouped into five main categories:

1. Numeric data types
2. Date and time data types
3. String data types
4. Spatial data types
5. 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.

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.

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.

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 and VARCHAR
• BINARY and VARBINARY
• BLOB and TEXT
• 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.

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.