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.
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.
|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.
|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.
|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.
|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:
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.
|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.|
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:
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.