Categories of MySQL data types:
MySQL data types are divided into the following categories.
The data types related to each group has explained below with examples.
String Data Type:
The string data type field can contain any alphabetic and numeric characters to define a string of small and large text such as name, address, description, etc. The binary content, such as an image or audio file, can be stored in the field of string data type. The string data types supported in MySQL have been shown below with size and description.
| String Data Type | Description |
| CHAR(n) | It is used to store a string of a fixed number of characters. It can contain a maximum of 255 characters. The argument value defines the length of the string. If the value of the CHAR field contains less character than the n value, then the space value will be added for the remaining characters. |
| VARCHAR(n) | It is used to store a string of the variable number of characters. It can also contain a maximum of 255 characters. The argument value defines the length of the string. It does not waste space like the CHAR data type. |
| TINYTEXT(n) | It works like a VARCHAR data type and can store a maximum of 255 characters. |
| TEXT(n) | It is used to store larger string values containing a maximum of 65,535 characters. |
| MEDIUMTEXT(n) | It stores a larger text value than the TEXT data type that can contain a maximum of 16,777,215 characters. |
| LONGTEXT(n) | It stores larger text values than the MEDIUMTEXT data type containing a maximum of 4GB or 4,294,967,295 characters. |
| BINARY(n) | It is used to store binary characters, and this data’s length is fixed. It can store a maximum of 255 characters. |
| VARBINARY(n) | It works like the BINARY data type, but its length is not fixed. |
| ENUM(v1, v2, v3,…,vn) | The full form of ENUM is Enumeration used to select a single value from multiple values. The numeric index is used to define the string values. It can have a maximum of 65,535 values. |
| SET(v1, v2, v3,…,vn) | It is used to select 0 or more values from the list of values. It can store a maximum of 64 values. |
Numeric Data Type:
The numeric data type field stores the whole number such as ID, security number, etc., or the fractional number, such as price, CGPA, etc. Different types of numeric data types have described below.
| Numeric Data Type | Description |
| BIT(n) | It is used to store bit values. The number of bits per value is defined by n here. It can store values from 1 to 64. The default value is 1. |
| BOOL/BOOLEAN | It is used to store True or False values. 0 value is considered a False value, and any non-zero number is considered a True value. |
| TINYINT(n) | It is used to store a very small-sized integer value. It can store values from -128 to 127 for signed integers and 0 to 255 for an unsigned integer. The n defines the length of the number. |
| SMALLINT(n) | It is used to store a small-sized integer value. It can store values from – -32768 to 32767 for signed integers and 0 to 65535 for an unsigned integer. The n defines the length of the number. |
| MEDIUMINT(n) | It is used to store a medium-sized integer value. It can store values from -8388608 to 8388607 for signed integers and 0 to 16777215 for an unsigned integer. The n defines the length of the number. |
| INT(n)/ INTEGER(n) | It is used to store an integer value. It can store values from -2147483648 to 2147483647 for signed integers and 0 to 4294967295 for an unsigned integer. The n defines the length of the number. |
| BIGINT(n) | It is used to store a big-sized integer value. It can store values from –9223372036854775808 to 9223372036854775807 for signed integers and 0 to 18446744073709551615 for an unsigned integer. The n defines the length of the number. |
| FLOAT(n, d) | It is used to store small-sized fractional numbers. Here, n defines the length of the whole number, and d defines the length of the number after the decimal point. It requires 2 bytes to store the data, and 24 digits can be added after the decimal point. The default value of n is 10 and is 2. |
| DOUBLE(n, d) | It is used to store large-sized fractional numbers. Here, n defines the length of the whole number, and d defines the length of the number after the decimal point. It requires 8 bytes to store the data, and 53 digits can be added after the decimal point. The default value of n is 16 and is 4. |
| DECIMAL(n, d) | It is used to define a fixed-point fractional number that can store a large fractional number than FLOAT and DOUBLE. Here, the maximum value of the n can be 65, and the maximum value of the d can be 30. |
Binary Large Object Data Type (BLOB):
This data type is used to store the variable amount of binary data. Four different types of BLOB data types have been described below.
| BLOB Data Type | Description |
| TINYBLOB | It can be used to store data of a maximum of 255 bytes. |
| BLOB(n) | It can be used to store data of a maximum of 65,535 bytes. |
| MEDIUMBLOB | It can be used to store data of a maximum of 16,777,215 bytes. |
| LONGBLOB | It can be used to store data of a maximum of 4GB or 4,294,967,295 bytes. |
Date and Time Data Type:
This data type is used to store date and time-related values. The date and time data type field can store date, time, DateTime, year, and timestamp values. If any invalid data is provided for the field value of date and time, the zero value will be inserted. Different data types of date and time data types have described below.
| Date and Time Data Type | Description |
| DATE | It is used to display the date in the format, ‘yyyy-mm-dd.’ It can store values from ‘1000-01-01’ to ‘9999-12-31’. |
| TIME | It is used to display the time in the format, ‘hh-mm-ss.’ It can store values from ‘-838:59:59’ to ‘838:59:59’. |
| DATETIME | It is used to display the time in the format, ‘yyyy-mm-dd hh-mm-ss’. It can store values from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. |
| YEAR[(2|4)] | It stores 2 or 4 digits year value. The 2 digits year value is not supported from the MySQL version 8.0. |
| TIMESTAMP(m) | It is used to display the time in the format, ‘yyyy-mm-dd hh-mm-ss’. It can store values from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. |
Spatial Data Type:
This type of data type stores the geographical and geometrical data. Different spatial data types have been described below.
| Spatial Data Type | Description |
| POINT | It is used to store the values of the X and Y coordinates of a point. |
| MULTIPOINT | It is used to store a collection of multiple points. |
| POLYGON | It is used to represent multisided geometry. 0 or more interior boundaries and one exterior boundary can be defined by polygon data type. |
| MULTIPLYGON | It is used to store a collection of multiple polygon elements. |
| LINESTRING | It is used to represent a curve of one or more point values. |
| MULTILINESTRING | It represents multi-curve geometry that contains a collection of LINESTRING values. |
| GEOMETRY | It is used to store a point or aggregate of points of spatial values of any type. |
| GEOMETRYCOLLECTION | It is used to store a collection of 0 or more geometry values. |
JSON Data Type:
The data type is used to store and access JSON documents easily. This data type is used to validate JSON data and provide optimal storage space automatically. When any invalid data is provided for the JSON field, it will produce an error.
Pre-requisites:
You have to create a database table with the data in a MySQL database to check the uses of MySQL data types. Open the terminal and connect with the MySQL server by executing the following command.
Run the following command to create a database named test_db.
Run the following command to select the database.
Uses of MySQL Data Types:
In this part of the tutorial, multiple examples have shown some uses of MySQL data types for creating database tables.
Example-1: Use of string and numeric data type
The following query will create a table with five fields. The data type of the product_id field is INT, and the primary key will be auto-incremented. The data type of the name field is VARCHAR. The data type of the weight field is SMALLINT. The data type of the unit field is ENUM. The data type of the price field is DECIMAL. Run the following query to create the Products table.
product_id int NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
weight smallint NOT NULL,
unit enum('Kg', 'Pound', 'Gram'),
price decimal(6, 2) NOT NULL,
PRIMARY KEY (product_id));
Run the following command to check the structure of the Products table.
Output:
The following output will appear after executing the above command.
Run the following query to read the content of the Products table.
Run the following command to read the content of the Products table.
Output:
The following output will appear after executing the above command.
Example-2: Use of date and time data type
The following query will create a table with four fields. The data type of product_id field is SMALLINT and primary key. The data type of the manufacturing_date field is DATETIME. The data type of the expired_date field is DATETIME. The data type of the entry_date field is TIMESTAMP. Run the following query to create the product_validity table.
product_id SMALLINT(4),
manufacturing_date datetime NOT NULL,
expired_date datetime NOT NULL,
entry_date timestamp,
PRIMARY KEY (product_id));
Run the following command to check the structure of the product_validity table.
Output:
The following output will appear after executing the above command.
Run the following query to read the content of the product_validity table.
Run the following command to read the content of the product_validity table.
Output:
The following output will appear after executing the above command.
Example-3: Use of spatial data type
The following query will create a table with three fields. The data type of id field is SMALLINT. The data type of the description field is TEXT. The data type of the geo field is GEOMETRY. Run the following query to create the geo_table table.
SET @g = 'POINT(10 3)';
Run the following command to check the structure of the geo_table table.
Output:
The following output will appear after executing the above command.
Run the following query to insert a record into the geo_table table.
Run the following query to read the content of the geo_table table.
Output:
The following output will appear after executing the above command.
Example-4: Use of JSON data type
The following query will create a table of JSON data. The data type of the jdata field is JSON. Run the following query to create the jsontable table.
Run the following command to check the structure of the jsontable table.
Output:
The following output will appear after executing the above command.
Run the following query to insert the JSON data.
Run the following query to read the records from the jsontable table.
Output:
The following output will appear after executing the above command.
Conclusion:
The most commonly used MySQL data types have been explained in this tutorial by creating multiple tables to clear the MySQL data types concept for the new MySQL users.
