MySQL Data Types- Scaler Topics

Overview

MySQL offers various unique features to its users and one such feature is its ability to handle various types of data. Data types are a building block of any programming language. In MySQL, data types determine the type of value you can store in a column of a table so it is very important to understand the basics of the vast variety of data types that MySQL offers.

In MySQL, there are several data types, including string, numeric, date and time, spatial, and JSON. In this article, we will provide an overview of each of these data types, including their description, default values, storage requirements, and how to choose the right type for a column.

Introduction

One of the key features of MySQL is its ability to handle various data types. Data types define the type of data that can be stored in a particular column of a table. Each data type has its characteristics, such as size, precision, and range, which determine the kind of data that can be stored in that column and that is why it is very important to choose the right data type for our table columns. When we create a table, we need to specify a name for the table and a data type that not only tells us and defines the kind of data that can be stored in the table column but also tells us what influence it may have on the database efficiency and performance.

We can define the data type in MySQL with the following characteristics:

The type of data (fixed or variable) it represents.
The storage space it requires is based on whether the values are a fixed-length or variable length.
If the values that it represents can be indexed or not.
How does MySQL compare the values of a specific data type?

As we already discussed, MySQL supports various data types such as numerical, string, time and data, and many more. Let us try to understand each of these data types in detail.

MySQL Data Types

Data Types define what kind of data you will store in a column and how different SQL operations would be performed on that column.

We can broadly define the different data types in MySQL in five categories.

String Data Types
Numeric Data Types
Date and Time Data Types
Spatial Data types
The JSON Data Type

String Data Types

In MySQL, the String Data Types are mostly used to store data as long strings of text.

The MySQL String Data Types can be divided into these 6 categories:

TEXT
BLOB
CHAR and VARCHAR
BINARY and VARBINARY
ENUM
SET

The following table summarizes each string data type in MySQL and will give you a basic understanding of each of them.

Data Type	Maximum Size	Description
CHAR(size)	255 characters	Stores fixed-length strings with a length of up to 255 characters. If the length is less than 255, then the remaining space is padded with spaces.
VARCHAR(size)	255 characters	Stores variable-length strings with a length of up to 255 characters.
TINYTEXT(size)	255 characters	Stores a small text string with a length of up to 255 characters.
TEXT(size)	65,535 characters	Stores a large text string with a length of up to 65,535 characters.
MEDIUMTEXT(size)	16,777,215 characters	Stores a medium text string with a length of up to 16,777,215 characters.
LONGTEXT(size)	4GB or 4,294,967,295 characters	Stores a very large text string with a length of up to 4GB or 4,294,967,295 characters.
BINARY(size)	255 characters	Stores fixed-length binary strings with a length of up to 255 characters. If the length is less than 255, then the remaining space is padded with zeros.
VARBINARY(size)	255 characters	Stores variable-length binary strings with a length of up to 255 characters.
ENUM	65,535 values	Stores a value from a predefined list of values. Each value is assigned a numeric index from 1 to 65,535.
SET	64 members	Stores one or more values from a predefined list of up to 64 members.

Text Data type

The TEXT data type has a storage capacity that ranges from 1 byte to 4 GB and contrary to numeric data types, the TEXT data type in the table column does not require you to provide a length.

The four TEXT data types in MySQL are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

BLOB Datatype

The BLOB data types are binary strings as opposed to the non-binary string data type, TEXT. Binary media files such as audio or video links, photos, or files, can be stored using the BLOB data type in MySQL, which stands for a binary large object data type.

Data Type Syntax	Maximum Size
TINYBLOB	It can hold a maximum size of 255 bytes.
BLOB(size)	It can hold a maximum size of 65,535 bytes.
MEDIUMBLOB	It can hold a maximum size of 16,777,215 bytes.
LONGBLOB	It can hold a maximum size of 4 GB or 4,294,967,295 bytes.

CHAR and VARCHAR datatype

In MySQL, non-binary strings with fixed lengths up to 255 characters can store in the CHAR data type, on the other hand, non-binary strings with variable lengths up to 65535 characters can be stored in the VARCHAR data type.

When adding a column, you must specify a size parameter in characters (in brackets) for both data types. The size option specifies the minimum and maximum column sizes for CHAR and VARCHAR data types, respectively.

BINARY and VARBINARY datatype

BINARY and VARBINARY data types are quite similar to the CHAR and VARCHAR data types, but they differ in a few ways. Binary strings are stored in the variables BINARY and VARBINARY, whose length is expressed in bytes.

ENUM Datatype

MySQL ENUM data types represent the strings with enumeration values. With ENUM, you can specify a list of predetermined values and then select from it. You will receive an empty string if you add an invalid value that is not on the list.

SET datatype

The MySQL SET data types enable you to store one or more values that you provided in a list of preset values when creating a table, separated by commas.

Numeric Data Types

In MySQL, there are several numeric data types that can be used to store numerical values with varying levels of precision and range. Here are some of the most common numeric data types in MySQL:

TINYINT
SMALLINT
INT
MEDIUMINT
BIGINT
DECIMAL
FLOAT
DOUBLE
BIT
BOOL
BOOLEAN

The following table summarizes each numeric data type in MySQL and will give you a basic understanding of each of them.

Data Type	Syntax	Description
TINYINT	TINYINT(n)	A small-sized integer that can be signed or unsigned. If signed, the allowable range is from $-2^{(n-1)}$ to $2^{(n-1)}-1$ . If unsigned, the allowable range is from $0~ to~ 2^{n-1}$ . The "n" parameter specifies the maximum display width in digits and can range from 1 to 3. TINYINT requires 1 byte for storage.
SMALLINT	SMALLINT(n)	A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -2^(n-1) to 2^(n-1)-1. If unsigned, the allowable range is from 0 to 2^n-1. The "n" parameter specifies the maximum display width in digits and can range from 1 to 5. SMALLINT requires 2 bytes for storage.
MEDIUMINT	MEDIUMINT(n)	A larger-sized integer that can be signed or unsigned. If signed, the allowable range is from $-2^{(n-1)}~ to~ 2^{(n-1)}-1$ . If unsigned, the allowable range is from $0~ to~ 2^{n-1}$ . The "n" parameter specifies the maximum display width in digits and can range from 1 to 9. MEDIUMINT requires 3 bytes for storage.
INT	INT(n)	A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from $-2^{(n-1)}~ to~ 2^{(n-1)-1}$ . If unsigned, the allowable range is from $0~ to~ 2^{n-1}$ . The "n" parameter specifies the maximum display width in digits and can range from 1 to 11. INT requires 4 bytes for storage.
BIGINT	BIGINT(n)	A large-sized integer that can be signed or unsigned. If signed, the allowable range is from $-2^{(n-1)}~ to~ 2^{(n-1)}-1$ . If unsigned, the allowable range is from $0~ to~ 2^{n-1}$ . The "n" parameter specifies the maximum display width in digits and can range from 1 to 20. BIGINT requires 8 bytes for storage.
FLOAT	FLOAT(m,d)	A floating-point number that cannot be unsigned. The "m" parameter specifies the maximum display width in digits and the "d" parameter specifies the number of digits after the decimal point. If not specified, "m" defaults to 10, and "d" defaults to 2. FLOAT can store up to 24 decimal places and requires 4 bytes for storage.
DOUBLE	DOUBLE(m,d)	A double-precision floating-point number that cannot be unsigned. The "m" parameter specifies the maximum display width in digits and the "d" parameter specifies the number of digits after the decimal point. If not specified, "m" defaults to 16, and "d" defaults to 4. DOUBLE can store up to 53 decimal places and requires 8 bytes for storage. The term "REAL" can also be used instead of "DOUBLE".
DECIMAL	DECIMAL(m,d)	An unpacked floating-point number that cannot be unsigned. The "m" parameter specifies the maximum display width in digits and the "d" parameter specifies the number of digits after the decimal point. Both parameters are required. DECIMAL stores each digit of the number as one byte,
BIT	BIT(m)	Used for storing bit values into a table column. The number of bits per value is determined by the value of m, which can range from 1 to 64. It requires 1, 2, 3, 4, 5, 6, 7, or 8 bytes, depending on the number of bits specified by m.
BOOL	BOOL	Used only for the true and false conditions. It is considered a numeric value, with 1 being true and 0 being false. It requires 1 byte for storage.
BOOLEAN	BOOLEAN	Similar to BOOL, used only for the true and false condition. It is considered a numeric value, with 1 being true and 0 being false. It requires 1 byte for storage.

Integer Data Types

Integer data types are used to store whole numbers without any fractions. There are different integer data types available in MySQL, such as TINYINT, SMALLINT, INT, MEDIUMINT, and BIGINT.

UNSIGNED integer data types allow only zero and positive numbers, while SIGNED integer data types allow zero, positive, and negative numbers.

Boolean data types

Boolean data types, unlike integer data types, can only store true or false values. In MySQL, boolean values are converted into integer data types (TINYINT), where 0 represents false and 1 represents true.

Float data types
Float data types are used to represent single-precision numeric values, which are approximate values that require 4 bytes for storage. They can be set as either SIGNED or UNSIGNED, and have a minimum and maximum storage size depending on their attribute. If you're working with MySQL version 8.0.17 or later, note that UNSIGNED is deprecated for the FLOAT and DOUBLE data types.

Double data types

Double data types are used to represent double-precision numeric values, which are also approximate but require 8 bytes for storage. Similar to float data types, they can be set as either SIGNED or UNSIGNED and have a minimum and maximum storage size depending on their attribute.

Decimal data types
Decimal data types are used to store exact and fixed numeric values. The precision and scale of the data type can be set when creating a table column using the DECIMAL(p,s) syntax.

BIT data type
Lastly, the BIT data type is used to store binary values and can only accept values of either 0 or 1. The range of bit values for the column can be set, with the default value being 1 if no range is specified.

Date and Time Data Types

MySQL provides different data types for managing date and time information in databases.

These data types are:

DATE
TIME
DATETIME
TIMESTAMP
YEAR

The following table summarizes the usage, data type format, and range of each type:

Type	Usage	Data Type Format	Range
DATE	Stores only date information in the table column	YYYY-MM-DD (year-month-day)	From ‘1000-01-01’ to ‘9999-12-31’
TIME	Displays only time	HH:MM:SS (hours:minutes)	From ‘-838:59:59’ to ‘838:59:59’
DATETIME	Stores both date and time in the column	YYYY-MM-DD HH:MM (year-month-day hours:minutes)	From ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
TIMESTAMP	Stores both date and time values in the column	YYYY-MM-DD HH:MM:SS (year-month-day hours:minutes)	From ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC
YEAR	Stores only year values in the column	YYYY (year)	From ‘1901’ to ‘2155’

Spatial Data Types

MySQL supports spatial data types that store geometry and geography values in the table column. These data types include GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION. Each data type is used to store a specific type of spatial data.

Here is a new table that describes spatial data type:

Type	Description	Examples
GEOMETRY	Stores any type of geometry value	A point, line, or polygon with any number of vertices.
POINT	Stores a single X and Y coordinate value	A single point with an X and Y coordinate.
LINESTRING	Stores a set of points that form a curve	A line that connects two or more points.
POLYGON	Stores a set of points in the multisided geometry	A polygon with at least three points.
MULTIPOINT	Stores a set of multiple point values	Two or more points that are not connected.
MULTILINESTRING	Stores a set of multiple LINESTRING values	Two or more line segments that are not connected.
MULTIPOLYGON	Stores a set of multiple POLYGON values	Two or more polygons that are not connected.
GEOMETRYCOLLECTION	Stores a set of multiple GEOMETRY values.	Two or more geometries of different types.

JSON Data Type

MySQL has introduced JSON data types that store JSON documents in the JSON column starting from version 5.7.8. JSON format provides a quicker way to access the document elements by searching values within the document with a key or array index. It also optimizes storage and enhances performance. JSON format has a built-in validation feature that can identify and flag invalid values. However, the maximum size of the JSON document is limited to 1GB.

Data Type Default Values

Default values in MySQL are values that are assigned to a column if no value is provided for that column during an INSERT operation. They can be explicitly specified using a DEFAULT value clause in a data type specification. The DEFAULT clause explicitly indicates the default value for a column. MySQL also handles the default value for a column implicitly if not done explicitly.

Explicit Default Handling

Explicit default handling is done when we define the default values for our columns while creating our table. to create a table t1 with default values for columns i, c, and price, we could use the following SQL statement:

In this case, if no value is provided for i during an INSERT operation, it will be assigned a value of -1. If no value is provided for c, it will be assigned an empty string, and if no value is provided for price, it will be assigned a value of 0.00.

In addition to literal constant default values, expressions can also be used as default values. For example:

In this case, the default value for column f is a random float value, the default value for column b is a UUID value converted to binary, the default value for column d is the current date plus one year, the default value for column p is a Point with coordinates (0,0), and the default value for column j is an empty JSON array.

It is also possible to use the DEFAULT keyword to insert default values during an INSERT operation. For example:

In this case, the default value for column i will be inserted as -1, the default value for column c will be inserted as an empty string, and the default value for column price will be inserted as 0.00.

Implicit Default Handling

If a column definition includes no explicit DEFAULT value, MySQL determines the default value. Here is how the default value is handled for various versions of MySQL:

Before MySQL 5.0.2, columns that were not explicitly assigned a DEFAULT value were assigned NULL as the default value.
MySQL 5.0.2 through MySQL 5.0.12 assigned a default value of '' (the empty string) to character-based columns, 0 to integer-based columns, 0.0 to floating-point columns, and the '0000-00-00 00:00:00' timestamp to TIMESTAMP and DATETIME columns.
Starting with MySQL 5.0.13, the default value for TIMESTAMP and DATETIME columns was changed to CURRENT_TIMESTAMP.
Starting with MySQL 5.0.24, the default value for boolean columns was changed to NULL.

In MySQL 8.0.13 and later versions, the behavior of implicit default values has changed. If a column has no explicit DEFAULT clause, the default value is determined based on the data type of the column:

For numeric columns (INT, BIGINT, FLOAT, etc.), the default value is 0.
For boolean columns, the default value is NULL.
For date and time columns (DATE, TIME, DATETIME, TIMESTAMP), the default value is NULL.
For string columns (CHAR, VARCHAR, TEXT, etc.), the default value is '' (the empty string).
For binary and blob columns (BINARY, VARBINARY, BLOB, etc.), the default value is NULL.

It is important to note that the behavior of default values can change depending on the version of MySQL being used and the SQL mode being used.

Data Type Storage Requirements

In this section, we will provide information on the storage requirements for each data type supported by MySQL, including the internal format and size for storage engines that use a fixed-size representation for data types.

Numeric Type Storage Requirements

Numeric types in MySQL have fixed storage requirements. For example, TINYINT requires 1 byte, SMALLINT requires 2 bytes, and BIGINT requires 8 bytes. The storage required for DECIMAL and NUMERIC types vary, depending on the number of digits and the number of excess digits.

Data Type	Storage Required
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT , INTEGER	4 bytes
BIGINT	8 bytes
FLOAT(p)	4 bytes if $0 <= p <= 24, 8~ bytes~ if~ 25 <= p <= 53$
FLOAT	4 bytes
DOUBLE PRECISION, REAL	8 bytes
DECIMAL(M,D), NUMERIC(M,D)	Varies; see following discussion
BIT(M)	approximately $(M+7)/8$ bytes

Values for decimal (or numeric) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given in the following table.

Leftover Digits	Number of Bytes
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

The storage required for date and time types in MySQL depends on the version of MySQL being used. For tables created before MySQL 5.6.4, the storage required for TIME, DATETIME, and TIMESTAMP columns differ from tables created from 5.6.4. As of MySQL 5.6.4, TIME, DATETIME, and TIMESTAMP are represented differently, with all three parts having a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values. DATETIME(0), DATETIME(2), DATETIME(4), and DATETIME(6) require 5, 6, 7, and 8 bytes, respectively, for the nonfractional part of the value. The storage required for the fractional part of the value is the same as for TIME values with equivalent fractional seconds precision.

Data Type	Storage Required Before MySQL 5.6.4	Storage Required as of MySQL 5.6.4
YEAR	1 byte	1 byte
DATE	3 bytes	3 bytes
TIME	3 bytes	3 bytes + fractional seconds storage
DATETIME	8 bytes	5 bytes + fractional seconds storage
TIMESTAMP	4 bytes	4 bytes + fractional seconds storage

Here is the table that tells us the fractional-storage requirements.

Fractional Seconds Precision	Storage Required
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

String Type Storage Requirements:

Here is the table that tells us the String Data Type Storage Requirements.

Data Type	Storage Required
CHAR(M)	The compact family of InnoDB row formats optimizes storage for variable-length character sets. Otherwise, M × w bytes, <= M<= 255, where w is the number of bytes required for the maximum-length character in the character set.
BINARY(M)	M bytes, $0<= M <= 255$
VARCHAR(M), VARBINARY(M)	$L + 1$ byte if column values require KaTeX parse error: Expected 'EOF', got '−' at position 3: 0 −̲ 255 bytes, $L+ 2$ bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXT	L+ 1 bytes, where L< 28
BLOB,TEXT	L + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT	L+ 3 bytes, where L < 224
LONGBLOB, LONGTEXT	L+ 4 bytes, where L< 232
ENUM(value1,value2,...)	1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET(value1,value2,...)	1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

For CHAR and VARCHAR columns, the storage required for each character in the column depends on the column character set.

For the BINARY and VARBINARY columns, the storage required is the same as the declared column length.

For BLOB and TEXT columns, the storage required depends on the column length and the storage engine used. For the MyISAM storage engine, the length of each BLOB or TEXT column is stored separately.MYD file, outside of the main data file contains the row data.

Type	Blob Part Size
BLOB, TEXT	2000
MEDIUMBLOB, MIDTEXT	4000
LONGBLOB, LONGTEXT	13948
JSON	8100

For ENUM and SET columns, the storage required depends on the maximum number of members the column can have. An ENUM column can have a maximum of 65,535 distinct members, while a SET column can have a maximum of 64 members. The storage required for ENUM and SET columns is 1 byte for up to 8 members, 2 bytes for up to 16 members, 3 bytes for up to 24 members, 4 bytes for up to 32 members, and 8 bytes for up to 64 members.

Spatial Type Storage Requirements

All spatial data types use the same storage format, and storage requirements are determined by the maximum size of any given instance of the data type. For example, the maximum size for a GEOMETRY value is 65,535 bytes. For a POINT value, the maximum size is 16 bytes.

JSON Type Storage Requirements

The storage requirement for a JSON column is roughly equivalent to that of a LONGBLOB or LONGTEXT column. However, there is additional overhead due to the binary encoding, which includes metadata and dictionaries required for the lookup of the values stored in the JSON document. When storing a string in a JSON document, extra storage of 4 to 10 bytes is needed, depending on the length of the string and the size of the object or array in which it is stored.

Choosing the Right Type for A Column

Choosing the right data type for a column is important for several reasons. First, it can affect the amount of storage space required to store the data, which can impact the performance of the database. Second, it can affect the accuracy and precision of the data being stored.

When choosing a data type for a column, consider the following:

The range of values that will be stored in the column.
The precision and accuracy required for the data being stored.
The amount of storage space required for the data.
The performance implications of using a particular data type.
The compatibility of the data type with other database engines or applications that may need to access the data.

Using Data Types from Other Database Engines

MySQL supports a wide range of data types that are compatible with other database engines. For example, MySQL supports the BIT data type, which is compatible with Oracle's NUMBER data type.

When using data types from other database engines, it is important to ensure that the data is stored in a compatible format. This may require converting the data to a different data type before storing it in MySQL.

MySQL maps the data types as shown in the following table.

Other Vendor Type	MySQL Type
BOOL	TINYINT
BOOLEAN	TINYINT
CHARACTER VARYING(M)	VARCHAR(M)
FIXED	DECIMAL
FLOAT4	FLOAT
FLOAT8	DOUBLE
INT1	TINYINT
INT2	SMALLINT
INT3	MEDIUMINT
INT4	INT
INT8	BIGINT
LONG VARBINARY	MEDIUMBLOB
LONG VARCHAR	MEDIUMTEXT
LONG	MEDIUMTEXT
MIDDLEINT	MEDIUMINT
NUMERIC	DECIMAL

Conclusion

MySQL offers a wide range of data types to choose from, each with its own set of advantages and disadvantages.
When choosing a data type for a column, consider the range of values that will be stored, the precision and accuracy required, the amount of storage space required, and the performance implications of using a particular data type.
MySQL supports various Data types, some of which are numeric, string, boolean, spacial, date and time, and JSON
Each of the MySQL data types has its properties so they require different storage conditions.
MySQL also supports data types from other database engines, making it easier to migrate data between different systems.