MySQL Data Types
A Data Type specifies a particular type of data, like integer, floating points, Boolean, etc. It also identifies the possible values for that type, the operations that can be performed on that type, and the way the values of that type are stored. In MySQL, each database table has many columns and contains specific data types for each column.
We can determine the data type in MySQL with the following characteristics:
 The type of values (fixed or variable) it represents.
 The storage space it takes is based on whether the values are a fixedlength or variable length.
 Its values can be indexed or not.
 How MySQL performs a comparison of values of a particular data type.
MySQL supports a lot number of SQL standard data types in various categories. It uses many different data types that can be broken into the following categories: numeric, date and time, string types, spatial types, and JSON data types.
Numeric Data Type
MySQL has all essential SQL numeric data types. These data types can include the exact numeric data types (For example, integer, decimal, numeric, etc.), as well as the approximate numeric data types (For example, float, real, and double precision). It also supports BIT datatype to store bit values. In MySQL, numeric data types are categories into two types, either signed or unsigned except for bit data type.
The following table contains all numeric data types that support in MySQL:
Data Type Syntax 
Description 
TINYINT 
It is a very small integer that can be signed or unsigned. If signed, the allowable range is from 128 to 127. If unsigned, the allowable range is from 0 to 255. We can specify a width of up to 4 digits. It takes 1 byte for storage. 
SMALLINT 
It is a small integer that can be signed or unsigned. If signed, the allowable range is from 32768 to 32767. If unsigned, the allowable range is from 0 to 65535. We can specify a width of up to 5 digits. It requires 2 bytes for storage. 
MEDIUMINT 
It is a mediumsized integer that can be signed or unsigned. If signed, the allowable range is from 8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. We can specify a width of up to 9 digits. It requires 3 bytes for storage. 
INT 
It is a normalsized integer that can be signed or unsigned. If signed, the allowable range is from 2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. We can specify a width of up to 11 digits. It requires 4 bytes for storage. 
BIGINT 
It is a large integer that can be signed or unsigned. If signed, the allowable range is from 9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. We can specify a width of up to 20 digits. It requires 8 bytes for storage. 
FLOAT(m,d) 
It is a floatingpoint number that cannot be unsigned. You can define the display length (m) and the number of decimals (d). This is not required and will default to 10,2, where 2 is the number of decimals, and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a float type. It requires 2 bytes for storage. 
DOUBLE(m,d) 
It is a doubleprecision floatingpoint number that cannot be unsigned. You can define the display length (m) and the number of decimals (d). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a double. Real is a synonym for double. It requires 8 bytes for storage. 
DECIMAL(m,d) 
An unpacked floatingpoint number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte. Defining the display length (m) and the number of decimals (d) is required. Numeric is a synonym for decimal. 
BIT(m) 
It is used for storing bit values into the table column. Here, M determines the number of bit per value that has a range of 1 to 64. 
BOOL 
It is used only for the true and false condition. It considered numeric value 1 as true and 0 as false. 
BOOLEAN 
It is Similar to the BOOL. 
Date and Time Data Type:
This data type is used to represent temporal values such as date, time, datetime, timestamp, and year. Each temporal type contains values, including zero. When we insert the invalid value, MySQL cannot represent it, and then zero value is used.
The following table illustrates all date and time data types that support in MySQL:
Data Type Syntax 
Maximum Size 
Explanation 
YEAR[(24)] 
Year value as 2 digits or 4 digits. 
The default is 4 digits. It takes 1 byte for storage. 
DATE 
Values range from '10000101' to '99991231'. 
Displayed as 'yyyymmdd'. It takes 3 bytes for storage. 
TIME 
Values range from '838:59:59' to '838:59:59'. 
Displayed as 'HH:MM:SS'. It takes 3 bytes plus fractional seconds for storage. 
DATETIME 
Values range from '10000101 00:00:00' to '99991231 23:59:59'. 
Displayed as 'yyyymmdd hh:mm:ss'. It takes 5 bytes plus fractional seconds for storage. 
TIMESTAMP(m) 
Values range from '19700101 00:00:01' UTC to '20380119 03:14:07' TC. 
Displayed as 'YYYYMMDD HH:MM:SS'. It takes 4 bytes plus fractional seconds for storage. 
String Data Types:
The string data type is used to hold plain text and binary data, for example, files, images, etc. MySQL can perform searching and comparison of string value based on the pattern matching such as LIKE operator, Regular Expressions, etc.
The following table illustrates all string data types that support in MySQL:
Data Type Syntax 
Maximum Size 
Explanation 
CHAR(size) 
It can have a maximum size of 255 characters. 
Here size is the number of characters to store. Fixedlength strings. Space padded on the right to equal size characters. 
VARCHAR(size) 
It can have a maximum size of 255 characters. 
Here size is the number of characters to store. Variablelength string. 
TINYTEXT(size) 
It can have a maximum size of 255 characters. 
Here size is the number of characters to store. 
TEXT(size) 
Maximum size of 65,535 characters. 
Here size is the number of characters to store. 
MEDIUMTEXT(size) 
It can have a maximum size of 16,777,215 characters. 
Here size is the number of characters to store. 
LONGTEXT(size) 
It can have a maximum size of 4GB or 4,294,967,295 characters. 
Here size is the number of characters to store. 
BINARY(size) 
It can have a maximum size of 255 characters. 
Here size is the number of binary characters to store. Fixedlength strings. Space padded on the right to equal size characters.
(introduced in MySQL 4.1.2) 
VARBINARY(size) 
It can have a maximum size of 255 characters. 
Here size is the number of characters to store. Variablelength string.
(introduced in MySQL 4.1.2) 
ENUM 
It takes 1 or 2 bytes that depend on the number of enumeration values. An ENUM can have a maximum of 65,535 values. 
It is short for enumeration, which means that each column may have one of the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string values. 
SET 
It takes 1, 2, 3, 4, or 8 bytes that depends on the number of set members. It can store a maximum of 64 members. 
It can hold zero or more, or any number of string values. They must be chosen from a predefined list of values specified during table creation. 
Binary Large Object Data Types (BLOB):
BLOB in MySQL is a data type that can hold a variable amount of data. They are categories into four different types based on the maximum length of values can hold.
The following table shows all Binary Large Object data types that support in MySQL:
Data Type Syntax 
Maximum Size 
TINYBLOB 
It can hold a maximum size of 255 bytes. 
BLOB(size) 
It can hold the maximum size of 65,535 bytes. 
MEDIUMBLOB 
It can hold the maximum size of 16,777,215 bytes. 
LONGBLOB 
It can hold the maximum size of 4gb or 4,294,967,295 bytes. 
Spatial Data Types
It is a special kind of data type which is used to hold various geometrical and geographical values. It corresponds to OpenGIS classes. The following table shows all spatial types that support in MySQL:
Data Types 
Descriptions 
GEOMETRY 
It is a point or aggregate of points that can hold spatial values of any type that has a location. 
POINT 
A point in geometry represents a single location. It stores the values of X, Y coordinates. 
POLYGON 
It is a planar surface that represents multisided geometry. It can be defined by zero or more interior boundary and only one exterior boundary. 
LINESTRING 
It is a curve that has one or more point values. If it contains only two points, it always represents Line. 
GEOMETRYCOLLECTION 
It is a kind of geometry that has a collection of zero or more geometry values. 
MULTILINESTRING 
It is a multicurve geometry that has a collection of linestring values. 
MULTIPOINT 
It is a collection of multiple point elements. Here, the point cannot be connected or ordered in any way. 
MULTIPLYGON 
It is a multisurface object that represents a collection of multiple polygon elements. It is a type of twodimensional geometry. 
JSON Data Type
MySQL provides support for native JSON data type from the version v5.7.8. This data type allows us to store and access the JSON document quickly and efficiently.
The JSON data type has the following advantages over storing JSONformat strings in a string column:
 It provides automatic validation of JSON documents. If we stored invalid documents in JSON columns, it would produce an error.
 It provides an optimal storage format.
