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.
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.
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 Type | Range | Description |
|---|---|---|
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 64 | An 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 Type | Range | Description |
|---|---|---|
DATETIME(p)(8 bytes) | 1000-01-01 00:00:00 to 9999-12-31 23:59:59 | Date 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:07 | Date 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-31 | Date value (YYYY-MM-DD).Example: 1999-12-31 |
TIME(p) (3 bytes) | -838:59:59 to 838:59:59 | Time 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 Type | Range | Description |
|---|---|---|
CHAR(n)( n*w bytes) | 0 to 28-1 characters | A 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 bytes | A 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 characters | A 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 bytes | A 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 bytes | Stores the smallest BLOB value. Uses a 1 byte length prefix. |
BLOB(L+2 bytes) | 0 to 216-1 bytes | A general-purpose binary string. Uses a 2-byte length prefix. |
MEDIUMBLOB(L+3 bytes) | 0 to 224-1 bytes | Stores a large binary string. Uses a 3-byte prefix. |
LONGBLOB(L+4 bytes) | 0 to 232-1 bytes | Stores large binary string. Uses a 4-byte prefix. |
TINYTEXT | 0 to 28-1 characters | Stores short textual values. |
TEXT | 0 to 216-1 characters | A general-purpose textual value storage. |
MEDIUMTEXT | 0 to 224-1 characters | Stores medium-length textual values. |
LONGTEXT | 0 to 232-1 characters | Stores long textual values. |
ENUM | 216-1 elements | Stores a single value from a predefined elements list. |
SET | Up to 64 values | Stores 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 Type | Description |
|---|---|
GEOMETRY | Any spatial object (point, line, polygon). |
POINT | Single point with two coordinate values, X and Y. |
LINESTRING | A series of connected points that form a line or curve. |
POLYGON | A series of connected points that form a closed area or surface. |
GEOMETRYCOLLECTION | A collection of spatial data types. |
MULTIPOINT | A group of points. |
MULTILINE | A set of lines. |
MULTIPOLYGON | A 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];
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.
