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 Syntax	Properties
TINYINT	TINYINT 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.
SMALLINT	SMALLINT 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.
INT	INT 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.
BIGINT	When 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.
DOUBLE	It 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.
DECIMAL	Decimal is as same as NUmeric which gives the exact value of an integer. It can not be unsigned.
BOOLEAN	It 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.

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 Syntax	Properties
DATE	In 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.
TIME	In TIME format is displayed as ‘HH:MM:SS’ and the value ranges from ‘-838:59:59’ to ‘838:59:59’.
DATETIME	In 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.

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 Syntax	Properties
CHAR	CHAR 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.
VARCHAR	Values 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.
TINYTEXT	The 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.
TEXT	The 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.
MEDIUMTEXT	This 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.
LONGTEXT	LONGTEXT 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.
BINARY	It 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).
VARBINARY	The 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.
SET	A 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.
ENUM	ENUM 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.

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 Syntax	Properties
TINYBLOB	It can hold values ranging from 0 to 255 bytes
BLOB	It can hold values ranging from 0 to 65,535 bytes
MEDIUMBLOB	It can hold values ranging from 0 to 16,777,215 bytes
LARGEBLOB	It 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 Syntax	Properties
GEOMETRY	Geometry 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.
POINT	A 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.
POLYGON	Polygon is a planner surface that represents a multisided geometry. It is defined by a single exterior boundary and zero or more exterior boundaries.
LINESTRING	A Linestring is a curve with linear interpolation between points. It is considered a line if it consists of exactly two points.
GEOMETRYCOLLECTION	Geometry collection is a kind of geometry that contains a collection of zero or more geometry values.
MULTILINESTRING	A Multilinestring is a Multicurve geometry collection composed of line string objects.
MULTIPOINT	Multipoint is a zero-dimensional geometry and it’s a geometry collection composed of point elements. Its boundary is an empty set.
MULTIPOLYGON	A 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.

Conclusion:

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:

• How to create a new User in MySQL
• How to Install MySQL on Windows
• How to Create Table in MySQL