MySQL Data Types Explained

February 5, 2024

Introduction

MySQL supports many basic data types. Data types are an essential part of working with databases. Knowing which different data types exist ensures each column gets an appropriate type assigned, leading to an optimized and error-free database.

In this tutorial, you will learn about the different MySQL data types.

MySQL Data Types.

What Are the Data Types in MySQL?

Every database column has a name and a data type. The specified data type tells MySQL what kind of values the column stores, how much space it requires, and what type of operations it can perform with the data.

The sections below explain data types in detail.

Note: Check out our MySQL Cheat Command Sheet and find the most important MySQL commands in one place.

MySQL Data Types Explained

A MySQL table can store many different data types. The most used types include:

  • Numeric. Integer and decimal values of varying precision.
  • Date and time. Time-based data using various formats.
  • String. Textual data with different lengths.
  • Spatial. Geometric-based information for geographic data.
  • JSON. Stores JSON documents.
MySQL data types subtypes

Read on to learn more about each group and see which data types each category includes.

Note: The information applies to MySQL version 8.0.

Numeric Types in MySQL

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 more specific groups. The table below shows different numeric data types, their signed and unsigned ranges, and a short description.

Data TypeRangeDescription
TINYINT(m)
(1 byte)
Signed: -128 to 127
Unsigned: 0 to 28-1
The smallest whole numbers.
Example: TINYINT(2) is an integer up to 99.
SMALLINT(m)
(2 bytes)
Signed: -32,768 to 32,767
Unsigned: 0 to 216-1
A medium-sized whole number.
Example: SMALLINT(4) is an integer up to 9,999.
MEDIUMINT
(3 bytes)
Signed: -8,388,608 to 8,388,607
Unsigned: 0 to 232-1
A moderate-sized whole number.
Example: MEDIUMINT(6) is an integer up to 999,999.
INT/INTEGER
(4 bytes)
Signed: -2,147,483,648 to 2,147,483,647
Unsigned: 0 to 216-1
A general purpose whole number type.
Example: INT(8) is an integer up to 99,999,999.
BIGINT
(8 bytes)
Signed: -263 to 263-1
Unsigned: 0 to 264-1
A very large whole number.
Example: BIGINT(10) is an integer up to 9,999,999,999.
FLOAT(m,d)
(4 bytes)
-3.402823466E+38 to -1.175494351E-38,
0,
1.175494351E-38 to 3.402823466E+38
A small floating-point number (single precision).
Example: FLOAT(3,2) is a floating-point number up to 9.99.
DOUBLE(m,d)
(8 bytes)
-1.7976931348623157E+308 to -2.2250738585072014E-308, 
0,
2.2250738585072014E-308 to 1.7976931348623157E+308
A normal-sized floating-point number (double precision).
Example: DOUBLE(6,3) is a floating-point number up to 999.999.
DECIMAL(m,d)
(m+1 bytes)
Depends on the specified digits (m) and decimals (d).
The maximum
m is 65 (default 10).
The maximum d is 30 (default 0).
Decimal values with user-defined precision.
Example: DECIMAL(8,3) is a fixed-point number up to 99,999.999.
BIT(m)
((m+7)/8 bytes)
1 to 64An m-bit binary value consisting of 0s and 1s.
Example: BIT(3) is a bit sequence up to 111.

Note: The FLOAT(p) syntax is also valid, where p specifies the precision. Different values of p result in different behavior:

  • 0 to 23 is a single-precision column.
  • 24 to 53 is a double-precision column.

Date and Time Types in MySQL

Date and time are data types for storing various time-based data. Common examples include the time of a data entry, a date of birth, elapsed time, or the current timestamp.

Date and time data types include various date and time formats. The table below goes into greater detail, including the type name, minimum, maximum, and description:

Data TypeRangeDescription
DATETIME(p)
(8 bytes)
1000-01-01 00:00:00 to 9999-12-31 23:59:59Date and time values (YYYY-MM-DD HH:MM:SS).
Use p (0-6) for fractional second precision.
Example: DATETIME(3) can be 1812-01-01 01:01:00.999
TIMESTAMP(p)
(4 bytes)
1970-01-01 00:00:01 to 2038-01-19 03:14:07Date and time values (YYYY-MM-DD HH:MM:SS).
Use p (0-6) for fractional second precision.
Example: TIMESTAMP(2) can be 2024-01-01 10:59:59.13
DATE
(3 bytes)
1000-01-01 to 9999-12-31Date value (YYYY-MM-DD).
Example: 1999-12-31
TIME(p)
(3 bytes)
-838:59:59 to 838:59:59Time value (HH:MM:SS or HHH:MM:SS). Use p (0-6) for fractional second precision.
Example: TIME(4) can be 120:10:45.9887
YEAR
(1 byte)
1901 to 2155 (and 0000)Year value (YYYY).
Example: 1903

Note: See our in-depth MySQL date and time function tutorial.

String Types in MySQL

When storing textual data, use one of the string data types available in MySQL. They can contain letters, numbers, images, or files.

There are several different string data types. The table below provides in-depth information for each type:

Data TypeRangeDescription
CHAR(n)
(n*w bytes)
0 to 28-1 charactersA fixed-length string of n characters, where w is the size of the largest character.
For lengths less than n, the value is right-padded.
Example: CHAR(4) can be some text, such as "abcd".

BINARY(n)
(n bytes)
0 to 28-1 bytesA fixed-length binary string of up to n bytes. For lengths less than n, the value is right-padded.
Example: BINARY(2) can be a BLOB object, such as "0x31".
VARCHAR(n)
(L+1 up to 255 bytes, L+2 for over 255 bytes)
0 to 216-1 charactersA variable-length string of n characters. Storage size depends on the length (L).
Example: VARCHAR(5) can be some text, such as "abcde".
VARBINARY(n)
(L+1 up to 255 bytes, L+2 for over 255 bytes)
0 to 216-1 bytesA variable-length binary string of n bytes. Storage size depends on the length (L).
Example: VARBINARY(3) can be some BLOB, such as "0x123".
TINYBLOB
(L+1 bytes)
0 to 28-1 bytesStores the smallest BLOB value. Uses a 1 byte length prefix.
BLOB
(L+2 bytes)
0 to 216-1 bytesA general-purpose binary string. Uses a 2-byte length prefix.
MEDIUMBLOB
(L+3 bytes)
0 to 224-1 bytesStores a large binary string. Uses a 3-byte prefix.
LONGBLOB
(L+4 bytes)
0 to 232-1 bytesStores large binary string. Uses a 4-byte prefix.
TINYTEXT0 to 28-1 charactersStores short textual values.
TEXT0 to 216-1 charactersA general-purpose textual value storage.
MEDIUMTEXT0 to 224-1 charactersStores medium-length textual values.
LONGTEXT0 to 232-1 charactersStores long textual values.
ENUM216-1 elementsStores a single value from a predefined elements list.
SETUp to 64 valuesStores zero or more values from a predefined list.

Note: Learn how to manipulate data strings using MySQL string function.

Spatial Types

Use one of the many different spatial data types that MySQL supports when storing geometric or geographic data. They are utilized to represent information about geometric shapes and physical location.

We can divide them into two groups

  • Single values. A basic single element.
  • Collections. Groups of several elements.

The table below provides a detailed overview of spatial data types in MySQL:

Data TypeDescription
GEOMETRYAny spatial object (point, line, polygon).
POINTSingle point with two coordinate values, X and Y.
LINESTRINGA series of connected points that form a line or curve.
POLYGONA series of connected points that form a closed area or surface.
GEOMETRYCOLLECTIONA collection of spatial data types.
MULTIPOINTA group of points.
MULTILINEA set of lines.
MULTIPOLYGONA group of polygons.

JSON Data Type

Since version 5.7.8, MySQL has included support for the JSON data type. This feature allows users to store and manage JSON documents through a database. Earlier versions stored JSON as one of the string data types.

MySQL ensures that the JSON documents are error-free through automatic validation. The storage format is optimized to ensure quick read access, with a maximum storage size similar to LONGBLOB or LONGTEXT.

How to Change Data Type in MySQL

Use the ALTER TABLE statement to change a MySQL column data type. The MODIFY keyword enables changing the column definition, which includes the data type.

Warning: Changing the column data type may result in data loss or alteration. To prevent this, use strict SQL server mode. Alter the server mode with one of the following statements:

SET GLOBAL sql_mode = 'strict';
SET SESSION sql_mode = 'strict';

Use the following syntax to change a column's data type:

ALTER TABLE [table_name] MODIFY [column_name] [data_type];
ALTER TABLE MySQL output

The statement alters the column's type. Use the DESCRIBE [table_name] statement to view the resulting column type change.

Conclusion

After reading this guide, you learned about different MySQL data types. Having a wide variety may seem confusing at first, but finding the optimal choice also optimizes the database.

Next, see how to optimize MySQL tables and improve database speeds.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is a technical writer at phoenixNAP with a passion for programming. With a background in Electrical Engineering and Computing, coupled with her teaching experience, she excels at simplifying complex technical concepts in her writing.
Next you should read
MySQL Date Function Guide with Examples
January 28, 2021

Find a list of all MySQL date functions in this helpful article. Learn what each function does and how to use...
Read more
MySQL Commands Cheat Sheet
January 20, 2021

Need a reference sheet for all the important MySQL commands? Check out this MySQL Commands article which...
Read more
MongoDB vs. MySQL
December 31, 2020

The rise of NoSQL databases caused a stir in the database community, with many companies deciding to adopt...
Read more
How to Drop a Table in MySQL
July 4, 2024

The DROP statement is a simple method to remove entire tables from your databases. It provides several...
Read more