MySQL Data Types (2026 Helpful Guide) – Complete Overview with Examples

MySQL

Jan 10, 2023

In this article, we’ll see MySQL Data Types.

Table of Contents

Choosing the right data types is a crucial aspect of database design. When working with MySQL, understanding the various MySQL data types available is essential for efficiently storing and manipulating data.

By the end, you’ll have a comprehensive understanding of MySQL data types and be better equipped to design efficient and scalable database schemas.

A database table contains multiple columns with specific data types such as numeric or string. MySQL provides more data types other than just numeric and string. When you define a table, you need to specify the datatype for each database column. For example, see below :

CREATE TABLE employee( 
emp_id INT AUTO_INCREMENT, 
first_name varchar(45) NOT NULL, 
last_name varchar(45) NOT NULL, 
date_of_birth date, 
PRIMARY KEY (emp_id) );

In the above example, varchar, data, and int are MySQL Data Types.

MySQL Data Types:

MySQL Data Types are grouped into several categories: Numeric, Date and time, String, Spatial type, and JSON type.

1). String Data Types :

String data types are mainly used to store names, addresses, descriptions, or any value that contains letters and numbers including binary data, like image or audio files in MySQL Data Types.

Data Type	Description	Storage
CHAR(size)	Holds up to 255 characters and allows a fixed length string.	(Declared column length of characters * Number of bytes) <= 255
VARCHAR(size)	Holds up to 255 characters and allows a variable length string. If you store characters greater than 55, then the data type will be converted to TEXT type.	String value(Len) + 1 WHERE column values require 0 − 255 bytes String value(Len) + 2 bytes WHERE column values may require more than 255 bytes
TINYTEXT	Allows a string with a maximum length of 255 characters	Actual length in bytes of String value(Len) + 1 bytes, where Len < 2⁸
TEXT	Allows a string with a maximum length of 65,535 characters	Actual length in bytes of String value(Len) + 2 bytes, where Len < 2¹⁶
BLOB	Holds up to 65,535 bytes of data, and is used for Binary Large Objects.	Actual length in bytes of String value(Len) + 2 bytes, where Len < 2¹⁶
MEDIUMTEXT	Allows a string with a maximum length of 16,777,215 characters	Actual length in bytes of String value(Len) + 3 bytes, where Len < 2²⁴
MEDIUMBLOB	Holds up to 16,777,215 bytes of data, and is used for Binary Large Objects.	Actual length in bytes of String value(Len) + 3 bytes, where Len < 2²⁴
LONGTEXT	Allows a string with a maximum length of 4,294,967,295 characters	Actual length in bytes of String value(Len) + 4 bytes, where Len < 2³²
LONGBLOB	Holds up to 4,294,967,295 bytes of data, and is used for Binary Large Objects.	Actual length in bytes of String value(Len) + 4 bytes, where Len < 2³²
ENUM(x,y,z,etc.)	Allows you to enter a list of possible values, with the maximum to be 65535 values. Just in case a value is inserted which is not present in the list, a blank value will be inserted.	1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET	SET is similar to ENUM, but SET can have up to 64 list items and can store more than one choice.	1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

2). Numeric Data Types :

MySQL supports all standard SQL numeric data types which include INTEGER, SMALLINT, DECIMAL, and NUMERIC. It also supports the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).

Data Type	Description	Storage
TINYINT(size)	Allows signed integers -128 to 127 and 0 to 255 unsigned integers.	1 byte
SMALLINT(size)	Allows signed integers from -32768 to 32767 and 0 to 65535 unsigned integers.	2 bytes
MEDIUMINT(size)	Allows signed integers from -8388608 to 8388607 and 0 to 16777215 unsigned integers.	3 bytes
INT(size)	Allows signed integers from -2147483638 to 214747483637 and 0 to 4294967925 unsigned integers.	4 bytes
BIGINT(size)	Allows signed integers from -9223372036854775808 to 9223372036854775807 and 0 to 18446744073709551615 unsigned integers.	8 bytes
FLOAT(size,d)	Allows small numbers with floating decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal.	4 bytes
DOUBLE(size,d)	Allows large numbers with floating decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal.	8 bytes
DECIMAL(size,d)	Allows storing DOUBLE as a string, so that there is a fixed decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal.	Varies

3). Date and time Types :

MySQL provides types for date and time as well as the combination of date and time in MySQL Data Types. In addition, MySQL supports the timestamp data type for tracking the changes in a row of a table.

Data Type	Description	Storage Required Before MySQL 5.6.4	Storage Required as of MySQL 5.6.4
YEAR()	Holds the value of year either in a two digit or in a four-digit format. Year values in the range (70-99) are converted to (1970-1999), and year values in the range (00-69) are converted to (2000-2069)	1 byte	1 byte
DATE()	Holds the date values in the format: YYYY-MM-DD, where the supported range is (1000-01-01) to (9999-12-31)	3 bytes	3 bytes
TIME()	Holds the time values in the format: HH:MI:SS, where the supported range is (-838:59:59) to (838:59:59)	3 bytes	3 bytes + fractional seconds storage
DATETIME()	A combination of date and time values in the format: YYYY-MM-DD HH:MI:SS, where the supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’	8 bytes	5 bytes + fractional seconds storage
TIMESTAMP()	Holds values which are stored as the number of seconds, with the format (YYYY-MM-DD HH:MI: SS). The supported range is from (1970-01-01 00:00:01) UTC to (2038-01-09 03:14:07) UTC	4 bytes	4 bytes + fractional second storage

4). Spatial Data type:

It is a special kind of MySQL Data Types that is used to hold various geometrical and geographical values.

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.

5). JSON Data type:

Since version 5.7.8, MySQL Data types included support for the native JSON data type, allowing users to store and manage JSON documents through a database.

MySQL makes sure that the JSON documents are valid and stores them in the JSON column.

MySQL Data Types Best Practices (2026)

Choose smallest possible data type
Use INT or BIGINT wisely
Prefer VARCHAR over TEXT when possible
Use DECIMAL for financial calculations
Avoid overusing NULL values
Index columns with appropriate data types
Use JSON for dynamic data structures

When to Use Which Data Type?

Scenario	Recommended Type
User ID	INT / BIGINT
Name	VARCHAR
Description	TEXT
Price	DECIMAL
Created Date	DATETIME
Status	ENUM
Config Data	JSON

References

Conclusion

Understanding MySQL data types is essential for designing efficient and scalable databases in 2026. By choosing the correct data types:

You improve query performance
Reduce storage usage
Maintain data integrity

Modern MySQL versions (8.x) provide advanced capabilities like JSON indexing and improved storage engines, making it even more important to select data types wisely.

Understanding MySQL data types is vital for designing efficient and scalable database schemas. By selecting the appropriate MySQL data types for each column, you can ensure data integrity, optimize storage space, and improve query performance. This comprehensive overview of MySQL data types equips you with the knowledge needed to make informed decisions when designing your database schema. I hope this article helps!

Blog

MySQL Data Types (2026 Helpful Guide) – Complete Overview with Examples