Different Datatypes in MySQL

Datatype in MySQL is nothing different than the datatype we study in other languages like Python and Javascript which have datatypes like float, integer, boolean, etc.

In this tutorial on MySQL datatypes, we will learn about different datatypes of MySQL like numeric, date and time, string types, spatial types, and JSON data types.

Datatypes in MySQL

We are now going to discuss the MySQL datatypes that are used to store different types of data in the table of MySQL, following points in the below postulates will describe in brief the datatypes of MySQL.

Numeric Datatypes:

The most widely used and most common datatypes in MySQL are Numeric datatypes. MySQL is capable to store all possible numeric data. Numeric Datatypes are defined in two categories, one is “signed” and another is “unsigned“.

The only difference between the signed and unsigned datatypes is that Signed datatypes use negative values and positive values. Whereas unsigned datatypes stored only positive numbers but not negative numbers.

The following numeric datatypes are mentioned below with their properties.

Datatype SyntaxProperties
TINYINTTINYINT is the smallest numeric datatype which consists of small integer values, it can be both signed and unsigned. When signed it ranges from -127 to 127.
SMALLINTSMALLINT also denotes a small integer value that is signed or unsigned. If signed it ranges from -327 to 327 and ranges from 0 to 635 in case the integer is unsigned. It is extendable up to 5 digits and holds space of only 2 bytes.
INTINT is a commonly used datatype that is of the normal size it ranges from -214 to -214 if the integer is signed and ranges from 0 to 429 in case the integer is unsigned.
BIGINTWhen we have to store whole numbers with big integer values we use BIGINT. If the integer is signed it ranges from -922 to 922 and when unsigned ranges from 0 to 184.
Float (m,d)In FLOAT (m,d) m stands for the number of digits in the value, and d stands for the number of decimals. For example, a column defined as FLOAT (7,4) is displayed as -99.999. MySQL performs rounding when storing values, so if you insert 999.0009 into a FLOAT (7,4) the approximate result is 9999.0001.
DOUBLEIt is a Double precision floating number that cannot be unsigned. Likewise float here also m represents the length of digits and m represents the no of decimals.
It takes 8 bytes for storage.
DECIMALDecimal is as same as NUmeric which gives the exact value of an integer. It can not be unsigned.
BOOLEANIt returns only True and False so when the value is 0 it will return False and when the value is 1 it will return True.
Numeric Data type

Date and Time Datatypes:

A DATE, DATETIME, and TIMESTAMP datatype are related to each other. In this section, we will learn the similarities and functions of these data types. In the below list types of DATE and TIME datatypes are mentioned.

Datatype SyntaxProperties
DATEIn DATE datatype data is displayed in the format of ‘yyyy-mm-dd’. Its value ranges from ‘1000-01-01’ to ‘999-12-31’. It takes 5 bytes for storage.
TIMEIn TIME format is displayed as ‘HH:MM:SS’ and the value ranges from ‘-838:59:59’ to ‘838:59:59’.
DATETIMEIn the DATETIME field, it includes both date and time and is displayed as ‘yyyy-mm-dd hh:mm:ss’ it takes 5 bytes for storage and the value ranges from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
TIMESTAMP()This datatype offers automatic initialization and conversion to the current date and time. It does not accept values that are not valid for date and time. It is displayed as ‘yyyy-mm-dd hh:mm:ss’ it takes 4 bytes for storage.
Date and Time Datatype

String Data types:

String datatypes are used to represent data in text format and binary format, for example, files, images, etc. MySQL consists of various types of String Datatypes which are mentioned in the below table.

Datatype SyntaxProperties
CHARCHAR datatype is declared with a length that indicates a maximum number of characters. For example, Char(20) will hold up to 20 characters. Its length can be any value from 0 to 225.
VARCHARValues in the VARCHAR datatype are variable-length strings. Its length can be any value from 0- 225. In contrast to CHAR, VARCHAR values are stored in 1-byte, 2-byte space.
TINYTEXTThe TINYTEXT data object is the smallest of the TEXT and short information strings can be easily stored in it. This data type can store 255 bytes or 255 characters and store 1 byte overhead.
TEXTThe TEXT datatype is similar to a TINYTEXT but the only difference is the amount of data it can store as compared to TINYTEXT. It can store a maximum size of 65,535 characters.
MEDIUMTEXTThis data type is mostly preferred for larger string texts like research papers and code backups. It takes 3-Bytes overhead. LONGTEXT can store the maximum characters among all four, up to 4,294,967,295 characters or 4GB.
LONGTEXTLONGTEXT consists of more than enough storage for any long-form text strings. It can store the maximum number of characters among all three, up to 4,294,967,295 bytes or 4GB.
BINARYIt can have a maximum of 255 characters. Like CHAR holds fixed-length strings. we still have to specify the width of the column, e.g. BINARY(30).
VARBINARYThe VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. Comparing and sorting are based on the numeric values of the bytes. It can have a maximum of 255 characters.
SETA SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. It takes 1 byte, 2 bytes depending upon the number of members. It can store up to 64 members.
ENUMENUM is a string object that allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. An ENUM can have a maximum of 65,535 characters.
String Data types

Binary Large Object Data Types :

Binary Large Object Data Type which is also denoted as BLOB. There are four types of BLOB datatype that are TINYBLOB, BLOB, MEDIUMBLOB, and LARGEBLOB. They are similar to each the only difference is the maximum length of values each of them can hold.

Datatype SyntaxProperties
TINYBLOBIt can hold values ranging from 0 to 255 bytes
BLOBIt can hold values ranging from 0 to 65,535 bytes
MEDIUMBLOBIt can hold values ranging from 0 to 16,777,215 bytes
LARGEBLOBIt can hold values ranging from 0 to 4,294,967,295 bytes

JSON Data Type:

JSON (JavaScript Object Notation) data type is supported by MySQL, which enables efficient access to data in JSON documents. The space required to store a JSON data type is nearly as same as LARGEBLOB. Along with the JSON data type, a set of SQL functions is available to enable operations on JSON values, such as creation, manipulation, and searching.

Read How to create database using MySQL

Spatial Data Type:

Spatial Datatypes consist of Geometric and geographic information, including points, lines, polygons, and other shapes, and are stored using spatial data types. These data types are intended to represent spatial data.

Datatype SyntaxProperties
GEOMETRYGeometry is the root class of Spatial Data Type. It can have dimensions of 0,1,-1, or 2. 0 for geometry with no area and no length,1 for nonzero length and nonzero area, -1 for an empty geometry, and 2 for a nonzero area.
POINTA Point is a geometry that represents a single location in coordinate space. It is defined as zero-dimensional geometry and its boundary is the empty set. It holds values of y-coordinate and x-coordinate.
POLYGONPolygon is a planner surface that represents a multisided geometry. It is defined by a single exterior boundary and zero or more exterior boundaries.
LINESTRINGA Linestring is a curve with linear interpolation between points. It is considered a line if it consists of exactly two points.
GEOMETRYCOLLECTIONGeometry collection is a kind of geometry that contains a collection of zero or more geometry values.
MULTILINESTRINGA Multilinestring is a Multicurve geometry collection composed of line string objects.
MULTIPOINTMultipoint is a zero-dimensional geometry and it’s a geometry collection composed of point elements. Its boundary is an empty set.
MULTIPOLYGONA Multipolygon is a two-dimensional geometry. Its boundaries are a set of closed curves corresponding to the boundaries of its polygon element. Each curve in the boundary of the multi-polygon is in the boundary of exactly one polygon element.
Spatial Data Types


In this MySQL Database tutorial, We have learned all possible and widely used datatypes. In this tutorial, we learned about the properties of Datatype classes including Numeric datatype, Date and Time data type, String Datatype, Spatial Datatype, JSON Datatype, and in the end Geometry Datatype.

You may also like to read the following article: