MySQL Data Types

This post provides a list of all the data types available in MySQL with the limitations where applicable.

The data types supported by MySQL include Numeric, Date and Time, String, Spatial Data, and JSON.

Numeric Types

The numeric data types include Integer, Fixed-Point, Floating-Point, and Bit-Value. This section provides a list of all the numeric data types with their range.

Integer Types(Exact Value)

The standard SQL integer types available in MySQL include INT and SMALLINT. Additional integer types supported by MySQL include TINYINT, MEDIUMINT, and BIGINT.

While creating the Table or Column, we can optionally specify the maximum display width within the parenthesis. The display width has no impact on the minimum and maximum values.

Notes: Defining the display width within the parenthesis is deprecated for integer types since MySQL 8.0.17 and it will be removed in future releases. Ex: INT(10). One must not specify the display width for all the new tables and columns.

The below-mentioned table provides the list of all the integer types with size, minimum value, and maximum value.

Type	Size	Min Signed	Max Signed	Max Unsigned
TINYINT	1 Byte	-128	127	255
SMALLINT	2 Bytes	-32768	32767	65535
MEDIUMINT	3 Bytes	-8388608	8388607	16777215
INT	4 Bytes	-2147483648	2147483647	4294967295
BIGINT	8 Bytes	-2⁶³	2⁶³ - 1	2⁶⁴ - 1

Fixed-Point Types(Exact Value)

Standard SQL types available in MySQL include DECIMAL and NUMERIC. These data types store the exact numeric value preserving the exact precision.

NUMERIC is implemented as DECIMAL, hence both follow the same rules as mentioned below.

DECIMAL - Maximum number of digits is 65 including precision(M), and scale(D) and the maximum number of supported decimals or scale(D) is 30.

The DECIMAL type can be declared as listed below.

DECIMAL(M, D) - With precision and scale - M is to specify precision and D is for scale.

DECIMAL(M) - Without scale - DECIMAL(M) or DECIMAL(M, 0).

DECIMAL - Without precision and scale - The precision(M) will be 10 by default.

Floating-Point Types(Approximate Value)

Standard SQL types available in MySQL include FLOAT or FLOAT(p) where p is precision.

MySQL also supports nonstandard FLOAT(M, D). It's deprecated since MySQL 8.0.17 and it will be removed in future releases.

FLOAT and DOUBLE data types represent approximate numeric data values. MySQL supports 4 bytes for single-precision FLOAT values and 8 bytes for double-precision DOUBLE values.

FLOAT(p) - MySQL uses the optional precision value to only determine the actual storage size. The precision value from 0 to 23 results in 4-byte single-precision FLOAT column, and value from 24 to 53 results in 8-byte double-precision DOUBLE column.

Bit-Value Type

The BIT data type is being used to store binary values. For example - values b'111' and b'10000000' represent 7 and 128, respectively.

BIT(M) - Stores M-bit values where M can range from 1 to 64.

Date and Time Types

The date and time data types include DATE, DATETIME, TIMESTAMP, TIME, and YEAR types. MySQL supports either 2 digits (relaxed format for string values) or 4 digits values for the Year part. I have used the 4 digits Year format in this section which is also the standard format.

DATE - The DATE type follows the standard format YYYY-MM-DD and used for date values without time part.

DATETIME - The DATETIME type follows the standard format YYYY-MM-DD hh:mm:ss and used for date values with time part.

TIMESTAMP - The TIMESTAMP type also follows the same format of DATETIME i.e. YYYY-MM-DD hh:mm:ss and used for date values with time part. MySQL converts TIMESTAMP value from current timezone to UTC before storing and convert back to current timezone from UTC while retrieving. The current timezone for each connection will be system timezone unless specified while creating the connection. The same timezone must be used while storing and retrieving to avoid change in the value.

TIME - The TIME type follows the standard format hh:mm:ss OR hhh:mm:ss and used for time values. The large hour values can be used for elapsed time or larger time difference.

YEAR - 1-byte type used to represent the year values.

The below-mentioned table provides the list of all the date and time data types with the minimum value, and maximum value.

Type	Format	Min Value	Max Value
DATE	YYYY-MM-DD	1000-01-01	9999-12-31
DATETIME	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	YYYY-MM-DD hh:mm:ss	1970-01-01 00:00:01	2038-01-19 03:14:07
TIME	hh:mm:ss OR hhh:mm:ss	-838:59:59	838:59:59
YEAR	4 Digit	1901	2155
YEAR	1 Or 2 Digit	1	99

Notes:

The TIMESTAMP values will be stored in UTC.
The data types DATETIME, TIMESTAMP, and TIME also support 6 digits fractional values ranging from 000000 to 999999.
MySQL converts 1 Or 2 Digit YEAR values in the ranges from 1 to 69 and 70 to 99 to YEAR values in the ranges from 2001 to 2069 and 1970 to 1999 respectively.

String Types

The string data types include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET types.

CHAR and VARCHAR Types

CHAR and VARCHAR data types can be used to store string values.

CHAR - Length can be any value from 0 to 255 characters. The length of CHAR column will be fixed to the length specified while declaring the column. In case the value occupies fewer characters, it will be right-padded with spaces to fully consume the length of 255 characters. The value will also be truncated if it consists of more than 255 characters.

VARCHAR - Length can be any value from 0 to 65,535 characters subject to a maximum row size of 65,535 characters shared among all the columns. Values are stored as a 1-byte or 2-byte length prefix plus data. The value won't be padded by space and it will be truncated if it occupies more characters as compared to the column limit.

Comparison - Storing the string i.e. 'ab' in CHAR(4) will consume 4 bytes whereas VARCHAR(4) will consume only 3 bytes. The column length of CHAR remains same irrespective of actual value.

BINARY and VARBINARY Types

BINARY and VARBINARY data types can be used to store binary string values as compared to CHAR and VARCHAR which stores nonbinary string values. Both BINARY and VARBINARY data types will have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.

BINARY - Length can be any value from 0 to 255 bytes. Similar to CHAR, the length of the BINARY column will be fixed to the length specified while declaring the column.

VARBINARY - Length can be any value from 0 to 65,535 bytes.

BLOB and TEXT Types

TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB are the BLOB data types available in MySQL and used to store the variable amount of data. The BLOB values are treated as binary or byte strings.

TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT are the TEXT data types and used to store character strings.

The maximum length of these data types are as listed below:

Binary Type	Nonbinary Type	Max Length	Max Space
TINYBLOB	TINYTEXT	255
BLOB	TEXT	65,535	~64kb
MEDIUMBLOB	MEDIUMTEXT	16,777,215	~16MB
LONGBLOB	LONGTEXT	4,294,967,295	~4GB

ENUM Type

ENUM represents string object having a value from the possible values specified in the column specification while creating the table or table column.

The ENUM data type can be used to store data in a compact form where the actual value will be stored as encoded in number form. The corresponding string value will be returned while retrieving the value.

For example: Column definition - size ENUM('x-small', 'small', 'medium', 'large', 'x-large') - The size column having data type ENUM allows only the pre-defined values. We have to pass either of these values while adding or updating the column value.

SET Type

SET can be used to store comma separated values from a definite set specified while creating the table or table column.

For example - A column specified as SET( 'Apple', 'Ball', 'Cat' ) can have below listed values:

'Apple'

'Ball, Apple'

Spatial Data Types

The spatial data types include single geometry value and collection of geometry value.

Single geometry values

GEOMETRY - Stores geometry values of any type.

POINT - Stores point values of a single point.

LINESTRING - Collection of points to form line having multiple points.

POLYGON - Collection of points to form a polygonal surface.

Collections of values

MULTIPOINT - Stores collection of multiple points.

MULTILINESTRING - Stores collection of multiple lines.

MULTIPOLYGON - Stores collection of multiple polygons.

GEOMETRYCOLLECTION - Stores collection of geometry values of any type.

JSON Data Type

The native JSON data type supported by MySQL can be used to directly store the JSON object. MySQL ensures that a valid JSON document gets stored in the JSON column.

These are all the data types available in MySQL relational database. It also supports document storage and operations on the stored documents using the JSON data type.

Write a Comment