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 fixed-length 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 medium-sized 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 normal-sized 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 floating-point 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 double-precision floating-point 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 floating-point 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[(2|4)] |
Year value as 2 digits or 4 digits. |
The default is 4 digits. It takes 1 byte for storage. |
| DATE |
Values range from '1000-01-01' to '9999-12-31'. |
Displayed as 'yyyy-mm-dd'. 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 '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. |
Displayed as 'yyyy-mm-dd hh:mm:ss'. It takes 5 bytes plus fractional seconds for storage. |
| TIMESTAMP(m) |
Values range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' TC. |
Displayed as 'YYYY-MM-DD 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. Fixed-length 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. Variable-length 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. Fixed-length 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. Variable-length 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 multi-curve 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 two-dimensional 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 JSON-format 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.
|