Data Types in MySQL: Tutorial and Full List With Examples of Data Formats

In this article, we are going to discuss the MySQL data types list, including string, numeric, date and time, spatial, and JSON supported by MySQL. We’ll also provide examples of their usage and show how to change a data type for a table column using dbForge Studio for MySQL. 

Table of contents

What is a data type in MySQL

Data types in MySQL

String data types in MySQL

• TEXT data type
• BLOB datatype in MySQL

CHAR and VARCHAR data type

• VARCHAR vs CHAR in MySQL
• BINARY and VARBINARY types
• ENUM data type in MySQL
• SET data type

Numeric data types in MySQL

• Integer data types

Boolean data type

• Float data type
• Double data type
• Decimal data type
• BIT data type

Date and time data types in MySQL

• Date data type
• DATETIME & TIMESTAMP
• MySQL TIMESTAMP vs DATETIME in MySQL
• Time data type
• Year data type

MySQL spatial data types

MySQL JSON data type

How to change data type in MySQL

Conclusion 

What is a data type in MySQL 

Selecting an appropriate data type can be very important. When creating a table, you need to specify a table name and a data type. This will help define what data can be stored in the table column and 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. Also, 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 an example of the CREATE statement that demonstrates a list of data types in MySQL. 

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
);

This example highlights how columns can use different data types: INT for identifiers, VARCHAR for names, DATE for expiry dates, TEXT for descriptions, and TIMESTAMP for record creation. It also serves as a practical way to see data types in MySQL with examples in action.

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

String data types in MySQL

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

In the next sections, you’ll find these MySQL data types explained with examples that show how each string type works in practice.

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. It’s especially useful in applications where users input or interact with large amounts of unstructured content, which is a common scenario in many use cases of MySQL in web development, content management systems, and data-driven apps (see what is MySQL used for).

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	255B	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

The TEXT family is part of the MySQL DATA types explained in the string group. It shows how different variations support everything from short summaries to very large documents. 

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 fits for character strings having a character set. This should be taken into account when comparing and sorting information.

CHAR and VARCHAR data type

In MySQL, CHAR and VARCHAR are the most commonly used string types, but they behave differently. Here’s how: 

• CHAR is a fixed-length type with a maximum size of 255 characters. It always uses the full defined length, padding shorter values with spaces. For example, CHAR(5) will store ‘ab’ as ‘ab ‘.
• VARCHAR is a variable-length type that can store up to 65,535 characters (depending on row size and constraints). It only uses as much space as needed for the value, plus 1 or 2 bytes to record the length. For example, VARCHAR(5) will store ‘ab’ exactly as ‘ab’ without padding. 

Because of these differences, choosing between them affects storage efficiency, query performance, and indexing behavior. 

VARCHAR vs CHAR in MySQL 

The table below summarizes the MySQL VARCHAR vs CHAR differences that matter most in practice. 

Feature	CHAR	VARCHAR
Indexing	Faster for lookups on fixed-length keys	Indexes can be slower with widely varying lengths
Memory use	Can waste space if values are short	Space-efficient, especially for large datasets
Row storage impact	Rows remain uniform in size	Rows may fragment as values grow/shrink
Best for	Country codes, hashes, fixed codes	Names, descriptions, addresses, text fields

Rule of thumb: use CHAR for consistently sized values and VARCHAR when length varies significantly. 

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'

Numeric data types in MySQL 

MySQL supports numeric data types such as integers, decimals, and floating-point numbers. They are essential for storing values like IDs, counters, financial data, or scientific measurements. 

The MySQL numeric data types list includes: 

• Integers (TINYINT, SMALLINT, INT, MEDIUMINT, BIGINT)
• Boolean values (BOOLEAN, implemented as TINYINT)
• Floating-point types (FLOAT, DOUBLE)
• Exact numeric values (DECIMAL)
• Bit fields (BIT) 

The following subsections provide details and examples for each numeric type. 

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 type in MySQL is used for logical values that can only be true or false. Internally, MySQL treats BOOLEAN (or BOOL) as a synonym for TINYINT(1), where 0 represents false and 1 represents true. 

The following MySQL Boolean data type example shows how it works in practice. 

CREATE TABLE users ( 
    user_id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(50), 
    is_active BOOLEAN 
); 
 
INSERT INTO users (name, is_active) VALUES  
('Alice', TRUE), 
('Bob', FALSE); 
 
SELECT * FROM users; 

In the output, MySQL will store TRUE as 1 and FALSE as 0. 

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 are used 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.

If you want to populate your tables with realistic sample data, try the built-in data generator for MySQL in dbForge Studio. It lets you simulate various data scenarios without manual input.

SELECT employee_id, first_name, last_name, month, week, BIN(days) FROM working_hours wh

Date and time data types in MySQL 

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’

The following subsections cover each type in detail with examples, including a comparison of MySQL timestamp vs datetime. Together, they are part of the broader MySQL data types explained in MySQL

Date Data Type

Let’s look at an example that uses the DATE data type, which stores values in the standard MySQL date format ‘YYYY-MM-DD’. Here, we create an employees table with a date_of_birth column, insert a row, and retrieve the 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

MySQL provides two main types for storing date and time values: DATETIME and TIMESTAMP. The difference is that DATETIME stores values exactly as entered, while TIMESTAMP automatically converts values between the session time zone and UTC. 

MySQL TIMESTAMP vs DATETIME in MySQL 

The table below summarizes the MySQL TIMESTAMP vs DATETIME differences: 

Feature	DATETIME	TIMESTAMP
Storage size	8 bytes	4 bytes
Time zone aware	No — stores value exactly as entered	Yes — converts between session TZ and UTC
Range	1000-01-01 00:00:00 to 9999-12-31 23:59:59	1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
Typical use cases	Historical dates, logs requiring long ranges	Timestamps for transactions, updates, auditing

Example: 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. 

ALTER TABLE employees  
  ADD COLUMN first_day_at_work DATETIME, 
  ADD COLUMN last_login TIMESTAMP; 
 
UPDATE employees 
SET first_day_at_work = '2020-11-18 10:00:00', 
    last_login = CURRENT_TIMESTAMP 
WHERE employee_id = 1; 
 
SELECT * FROM employees; 

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

MySQL 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.

To confirm that your updates are consistent across environments, you can also use the MySQL Data Compare feature in dbForge Studio. 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 this tool is a full-featured MySQL DB manager that improves productivity across database development, testing, and deployment.