In the article, we are going to discuss data types including string, numeric, date and time, spatial, and JSON supported by MySQL. Also, we’ll provide examples of their usage and see how to change a data type for the table column using dbForge Studio for MySQL.
Contents
- What is a Data Type
- Data Types in MySQL
- MySQL String Data Types (Text Formats)
- MySQL Numeric Data Types (Number Formats)
- MySQL Date & Time Data Types
- MySQL Spatial Data Types
- JSON Data Type
- How to Change Data Type in MySQL
What is a Data Type
Selecting an appropriate data type can be very important. When creating a table, you need to specify a table name and a data type that not only defines what data can be stored in the table column but also what influence it may have on database performance. A data type also indicates a data range to be stored in each column of the table. When specifying data types, you can set a field size.
In MySQL, there are various data types that are grouped in numeric (integer, float, boolean, etc.), date and time (DATETIME, DATE, etc.), string (CHAR, VARCHAR, etc.), spatial, and JSON. For example, if the column data type is numeric, it means that only numerical data can be stored in the column and you can define its maximum length in brackets.
The syntax of data types is as follows:
CREATE TABLE table_name (
column1_name data type(length),
column2_name data type(length),
);Here is the example of the CREATE statement displaying MySQL data types:
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_item VARCHAR(255) NOT NULL,
use_by date,
price int,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Data Types in MySQL
MySQL supports several standard SQL data types. Each column can contain only one data type. In MySQL, data types are grouped in different categories:
- Numeric
- Date and time
- String
- Spatial
- JSON.
MySQL String Data Types (Text Formats)
In MySQL, string data types usually store data as long strings of text, for example, feedback or product description. Strings can consist of letters, numbers, or binary media files such as images, photos, or audio files.
The MySQL string data types are divided into:
- TEXT
- BLOB
- CHAR and VARCHAR
- BINARY and VARBINARY
- ENUM
- SET
TEXT Data Type
The MySQL TEXT data type is used to store long-text strings to display information about the table object, such as product descriptions, blog comments, etc. The storage size of the TEXT data type varies from 1 byte to 4 GB. Unlike the numeric data types, you do not have to set a length next to the TEXT data type in the table column. Moreover, TEXT values are not stored in the server’s memory but use the disk instead. Therefore, the TEXT data types require +1 additional byte to retrieve data.
In MySQL, there are four different TEXT data types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.
The table displays the allowable storage in bytes for the TEXT data types and cases when they can be used.
| Type | Storage | Maximum number of characters | Overhead storage (in bytes) | Usage |
| TINYTEXT | 255 | 255 | 1 | To store short-text strings such as links, product description or summary |
| TEXT | 64 kB | 65535 | 2 | To store texts such as articles that do not exceed the specified length of characters |
| MEDIUMTEXT | 16 MB | 16777215 | 3 | To store large texts such as whitepapers or books |
| LONGTEXT | 4 GB | 4294967295 | 4 | To store huge texts such as computer programs or applications |
BLOB Datatype in MySQL
Unlike the TEXT data types, which are non-binary string data types, the BLOB data types are binary strings. In MySQL, the BLOB data type represents a binary large object and can be used to store binary media data, such as audio or video links, images, or files.
The BLOB data types, including TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, have a variable length, i.e. additional bytes (from 1 to 4) are required to store a value length in the column. For example, the maximum size of data to be stored is as follows:
- TINYBLOB => 255 bytes + 1 byte
- BLOB => 65535 + 2 bytes
- MEDIUMBLOB => 16777215 + 3 bytes
- LONGBLOB => 4294967295 + 4 bytes
When comparing BLOB and TEXT data types, it should be noted that BLOB is defined as numeric values, while TEXT – as character strings having a character set. This should be taken into account when comparing and sorting information.
CHAR and VARCHAR data type
In MySQL, the CHAR data types store non-binary strings with a fixed length that reaches 255 characters, while the VARCHAR data types store non-binary strings with a variable length having a maximum size of up to 65535 characters.
For both data types, you need to set a size parameter in characters (in brackets) when creating a column. The size parameter represents the column length for a CHAR data type and the maximum column length for a VARCHAR data type. For example, CHAR(3) refers to up to 3 characters for the column value.
The main distinction between the CHAR and VARCHAR data types is a way of storing data. CHAR adds spaces to values on the right to the specified length, for example, CHAR(3) will be displayed as follows ‘table ‘. VARCHAR outputs the value as it is, without any additional spaces – VARCHAR(3) will be displayed as ‘table’.
It should be noted that when defining a datatype for a phone number in MySQL, VARCHAR is more preferable to integers as sometimes there may be special symbols or characters. In addition, VARCHAR simplifies validation.
BINARY and VARBINARY Types
Though CHAR and VARCHAR seem to be similar to BINARY and VARBINARY data types, they have some differences. BINARY and VARBINARY store binary strings, and length is measured in bytes.
ENUM Data Type in MySQL
The MySQL ENUM data types are strings with enumeration values. ENUM allows you to set a list of predefined values and then choose any of them. If you add an invalid value that is not included in the list, you will get an empty string.
For example, we want to create a table that will store information about the size of women’s clothes: small, medium, and large. In the table, we will insert the size column with the ENUM type. It means that this column will take only specified values.
-- create a table with the CREATE TABLE statement
CREATE TABLE clothes (
product_ID int PRIMARY KEY AUTO_INCREMENT,
name varchar(255) NOT NULL,
fabric text NOT NULL,
size enum ('small', 'medium', 'large') NOT NULL
);
-- insert into the table a new row with a valid value
INSERT INTO clothes (product_ID, name, fabric, size)
VALUES (1, 'dresses', 'cotton', 'small');After that, retrieve data and see the result.
Now, insert another row with a value (‘extra large‘) that was not specified in the list and retrieve data.
-- add 'extra large' to the size column that is not included in the permitted values
INSERT INTO clothes (product_ID, name, fabric, size)
VALUES (2, 'dresses', 'silk', 'extra large');As you can see, the output displays an empty string for the invalid value of the ENUM data type column.
SET Data Type
The MySQL SET data types allow you to store zero or multiple values (separated by comma) you specified in a list of predefined values when creating a table. For example, suppose that customers can wear some dresses both in autumn and winter. In this case, we can insert in the aforementioned table clothes a new row season and assign the SET (‘autumn’, ‘winter’) data type to the column. In the output, we may see the following options:
''
'autumn'
'winter'
'autumn,winter'MySQL Numeric Data Types (Number Formats)
MySQL supports numeric data types such as integers, decimals, and floating-point data types:
- Integers represent numbers without fractions and can have SIGNED and UNSIGNED attributes. Usually, they may be used for IDs or counting numbers.
- Decimals represent numbers with fractions and store exact numeric values in the column. They can be signed and unsigned and are usually used for columns that store monetary values. In the comparison with the floating-point numbers, decimals are more accurate.
- Floating-point represent numbers with fractions but do not store exact numeric values. They can be signed and unsigned. Floating-point numeric values use a double-precision 64-bit format or a single-precision 32-bit format to store data. They may lead to a loss of precision during arithmetic operations.
Integer Data Types
Integer data types are numeric values without fractions. MySQL supports the following integer data types:
- TINYINT
- SMALLINT
- INT
- MEDIUMINT
- BIGINT
They can be UNSIGNED, which allow only zero and positive numbers in a column, and SIGNED, which store zero, positive, and negative numbers. For more information about integer data types, see MySQL INT (INTEGER) Data Types with Different Examples.
Boolean Data Type
The boolean data types can only accept either true or false values. In a binary format, true refers to 1 and false – to 0. As a rule, they are used for logical operations.
MySQL does not have a boolean (or bool) data type. Instead, it converts boolean values into integer data types (TINYINT). When you create a table with a boolean data type, MySQL outputs data as 0, if false, and 1, if true.
Float Data Type
The Float data types represent single-precision approximate numeric values that require 4 bytes for storage. They can have SIGNED and UNSIGNED attributes.
| Attribute | Minimum storage size | Maximum storage size |
| SIGNED | -3.402823466E+38 | -1.175494351E-38 |
| UNSIGNED | 0 and 1.175494351E-38 | 3.402823466E+38 |
Note: Starting from MySQL version 8.0.17, UNSIGNED is deprecated for the FLOAT and DOUBLE data types.
When adding a column, you need to set values for the float data type in brackets – FLOAT(m,d) where ‘m‘ is the number of digits in total and ‘d‘ is the number of digits after the decimal point.
Double Data Type
The Double data types refer to the floating-point numeric data types and use 8 bytes to store double-precision values. The syntax for the double data type is DOUBLE PRECISION(m,d) where ‘m‘ is the total number of digits and ‘d‘ is the number of digits following the decimal point. For example, DOUBLE(7,5) means that it will store a value with seven digits and five decimals.
| Attribute | Minimum storage size | Maximum storage size |
| SIGNED | -1.7976931348623157E+308 | -2.2250738585072014E-308 |
| UNSIGNED | 0 and 2.2250738585072014E-308 | 1.7976931348623157E+308 |
Decimal Data Type
The DECIMAL data type can be used to store exact and fixed numeric values. When creating a table column, the syntax for the data type is DECIMAL(p,s) where ‘p‘ stands for precision, the maximum number of digits, and ‘s‘ stands for scale, the number of digits following the decimal.
As a result, the main difference between float and double data types is precision (from 0 to 23 for FLOAT, and from 24 to 53 for DOUBLE) and accuracy (up to approximately 7 decimals for FLOAT, and up to approximately 15 decimals for DOUBLE).
To sum up, decimals are better to use for fixed amounts, such as monetary and financial information (price, salary, etc,), while float and double – for approximate calculations where rounding values might have a negative impact.
BIT Data Type
The BIT data type is used to store binary values in the column and accepts either 0 or 1. The range of bit values for the column goes from 1 to 64. If the range is not set, the default value will be 1.
For example, create the working_hours table with the days column as BIT(7). In the days column, 1 serves as a working day and 0 – as a day-off.
CREATE TABLE working_hours (
employee_id int NOT NULL AUTO_INCREMENT,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
month varchar(8),
week int,
days bit(7),
PRIMARY KEY (employee_id)
);Suppose in April, employees can have two additional day-offs. Thus, insert a new row in the table using the INSERT INTO statement:
INSERT INTO working_hours (employee_id, first_name, last_name, month, week, days)
VALUES (1, 'Jordan', 'Sanders', 'April', 2, B'1111100');Retrieve data by executing the SELECT statement:
SELECT * FROM working_hours wh;In the output, we can see that the result is not displayed as binary, and MySQL treats the BIT data type as integers – 124.
So, to retrieve data being converted to binary, we need to use the BIT() function and view the result.
SELECT employee_id, first_name, last_name, month, week, BIN(days) FROM working_hours wh
MySQL Date & Time Data Types
For managing date and time information in databases, MySQL date types are used that are divided into DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
| Type | Usage | Data type format | Range |
| DATE | Stores only date information in the table column | YYYY-MM-DD format (year, month, and date) | from ‘1000-01-01’ to ‘9999-12-31’ |
| TIME | Displays only time | HH:MM:SS format (hours, minutes, and seconds) | from ‘-838:59:59’ to ‘838:59:59’ |
| DATETIME | Stores both date and time in the column | YYYY-MM-DD HH:MM:SS ( year, month, and date, and hours, minutes, and seconds) | from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ |
| DATETIME | Stores both date and time values in the column | YYYY-MM-DD HH:MM:SS ( year, month, and date, and hours, minutes, and seconds) | from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ |
| TIMESTAMP | Stores both date and time values in the column. Conversion of the value from the zone of the connection server to UTC takes place. | YYYY-MM-DD HH:MM:SS ( year, month, and date, and hours, minutes, and seconds) | 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’ |
Date Data Type
Let’s see on a particular example how to create the employees table with information about employees, including the date of birth to which we declare the DATE type. Then, insert a new row into the table and retrieve data.
-- create the employees table
CREATE TABLE employees(
employee_id INT AUTO_INCREMENT,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
date_of_birth date,
PRIMARY KEY (employee_id)
);
-- insert data into the table
INSERT INTO employees (employee_id, first_name, last_name, date_of_birth)
VALUES (1, 'John', 'Sanders', '2000-01-19');
-- retrieve data
SELECT * FROM employees e;The output is as follows:
DATETIME & TIMESTAMP
DATETIME and TIMESTAMP seem to be similar, though they have some differences. The DATETIME data type needs 4 bytes to store data, while the TIMESTAMP data type requires 8 bytes. Besides, DATETIME is a constant data type, and TIMESTAMP is temporal as it depends on the UTC zone.
Let’s add the date and time of the first day at work. In this case, we will use the DATETIME data type for the new column first_day_at_work.
-- modify the table with a new column
ALTER TABLE employees
ADD COLUMN first_day_at_work datetime;
-- add data to the created table
UPDATE employees
SET
first_day_at_work = '2020-11-18 10:00:00'
WHERE employee_id = 1;
-- retrieve data
SELECT * FROM employees e;The output is as follows:
Time Data Type
Now, let’s add information about the time the employee should start working every day. For this, update the employees table by adding the start_to_work column with the MySQL TIME data type for the value.
-- modify the table
ALTER TABLE employees
ADD COLUMN start_to_work time;
-- update the table with new information
UPDATE employees
SET
start_to_work = '08:00:00'
WHERE employee_id = 1;
-- retrieve data
SELECT * FROM employees e;The result should be as follows:
Year Data Type
Finally, we will add to the table the year when an employee was certified and declare the YEAR data type for the column.
-- add a new column
ALTER TABLE employees
ADD COLUMN certified year;
-- set the value to the column
UPDATE employees
SET
certified = '2021'
WHERE employee_id = 1;
-- retrieve data
SELECT * FROM employees e;
MySQL Spatial Data Types
Spatial data types store geometry and geography values in the table column. MySQL supports single geometry values (GEOMETRY, POINT, LINESTRING, POLYGON) and multiple values grouped in a set (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION).
The table provides a description per spatial data type.
| Type | Description |
| GEOMETRY | Stores any type of the geometry value |
| POINT | Stores a MySQL single X and Y coordinate value |
| LINESTRING | Stores a set of points that form a curve |
| POLYGON | Stores a set of points in the multisided geometry |
| MULTIPOINT | Stores a set of multiple point values |
| MULTILINESTRING | Stores a set of multiple LINESTRING values |
| MULTIPOLYGON | Stores a set of multiple POLYGON values |
| GEOMETRYCOLLECTION | Stores a set of multiple GEOMETRY values |
JSON Data Type
Starting with version 5.7.8, MySQL supports JSON data types that store JSON documents in the JSON column and provide access to document elements easily and quickly. The JSON format allows for a quick search of values within the document using a key or array index, which, in turn, will improve overall performance and optimize storage. In addition, JSON helps validate documents and in case of invalid values, you will get an error. The maximum size is 1GB.
How to Change Data Type in MySQL
Considering a list of MySQL data types discussed in the article, it can be important to change default data types so that they suit our needs best.
Let’s have a look at the example explaining how to change default data types in MySQL columns using dbForge Studio for MySQL which is a cutting-edge alternative to Workbench. The dbForge MySQL GUI tool provides a huge set of advanced features for database development, management, and deployment aimed at maximizing developers’ productivity and increasing overall performance.
To change a data type for the given column, use the ALTER statement. The syntax is as follows:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;For our example, we are going to use the employees table. To open the CREATE script of the table, in Database Explorer, right-click the table and select Generate Script As > CREATE > To New SQL Window.
In the SQL document that opens, change the data type of the first_day_at_work column using the ALTER statement. Then, execute the query by clicking Execute on the SQL toolbar.
Now, retrieve the data: In Database Explorer, right-click the table and select Retrieve Data.
Alternatively, you can refresh the list of tables in Database Explorer and see the changed data type.
Additionally, you can read about how to change a column type in this article.
Conclusion
In the article, we have reviewed MySQL data types with examples, such as string, numeric, date and time, spatial, and JSON, as well as explored data types each group includes. Besides, we exemplified how to change a data type in the MySQL table column with the help of dbForge Studio for MySQL.
You can also use the following articles to learn how to make the development process much faster and easier with dbForge Studio for MySQL:
- Migrate data from MySQL to PostgreSQL
- Create a database in MySQL
- Use SELECT statement in MySQL
- Use MySQL integer data types
Download a 30-day free trial version to assess the features of dbForge Studio for MySQL to be convinced that it is worth purchasing the full license of the tool in order to improve productivity and database development and management in the most efficient way.
