• 05 Mar, 2026
• 0 Comments
• 4 Mins Read

MySQL Data Types Explained with Examples

Introduction

When working with databases, one of the most important decisions developers and database administrators make is choosing the correct data type for each column. In MySQL, data types define the kind of data that can be stored in a table column. Selecting the right data type improves data integrity, storage efficiency, and query performance.

Whether you are a beginner learning databases, a developer building applications, or a database administrator managing data systems, understanding MySQL data types is essential. Using incorrect data types can lead to wasted storage, slow queries, or inaccurate data handling.

In this guide, we will explain MySQL data types in simple terms with practical examples, helping you understand how and when to use each type.

1. What Are MySQL Data Types?

MySQL data types define the type of value that a column can store in a database table.

For example:

• Numbers

• Text

• Dates

• Boolean values

• Binary data

When creating a table, each column must be assigned a specific data type.

Example

CREATE TABLE students (
id INT,
name VARCHAR(50),
age INT,
admission_date DATE
);

In this table:

• id and age store numbers

• name stores text

• admission_date stores a date

2. Categories of MySQL Data Types

MySQL data types are broadly divided into the following categories:

Let’s explore each category in detail.

3. Numeric Data Types in MySQL

Numeric data types store integer and decimal values.

3.1 Integer Data Types

Used to store whole numbers without decimals.

Example

CREATE TABLE employees (
emp_id INT,
salary BIGINT
);

Here:

• emp_id stores employee IDs

• salary stores large salary values

3.2 Decimal Data Types

Used when precision is important, such as financial calculations.

Example

CREATE TABLE products (
product_id INT,
price DECIMAL(10,2)
);

Explanation:

• 10 = total digits

• 2 = digits after decimal

Example value: 99999999.99

4. String Data Types in MySQL

String data types store text or character data.

4.1 CHAR

• Fixed-length string

• Faster for storing fixed-size values

Example:

CREATE TABLE country_codes (
code CHAR(3)
);

Example values:

• USA

• IND

• AUS

4.2 VARCHAR

• Variable-length string

• Saves storage because it only uses required space

Example:

CREATE TABLE users (
username VARCHAR(50)
);

Example values:

• Rahul

• Ananya

• Michael

4.3 TEXT Types

Used for large text data.

Example

CREATE TABLE articles (
title VARCHAR(200),
content TEXT
);

Used for:

• Blog content

• Descriptions

• Comments

5. Date and Time Data Types

MySQL provides data types specifically for storing dates and time values.

Example

CREATE TABLE orders (
order_id INT,
order_date DATE,
order_time TIME,
created_at DATETIME
);

Example values:

6. Binary Data Types

Binary data types store raw binary data such as images, videos, or files.

Example

CREATE TABLE documents (
id INT,
file_data BLOB
);

Used for storing:

• Images

• PDFs

• Videos

• Files

7. Boolean Data Type in MySQL

MySQL does not have a dedicated boolean type, but BOOLEAN is treated as TINYINT(1).

Values:

Example

CREATE TABLE users (
id INT,
is_active BOOLEAN
);

Example values:

8. Example: Creating a Complete Table Using Multiple Data Types

Here is a real-world example combining several MySQL data types.

CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT,
course TEXT,
fees DECIMAL(8,2),
admission_date DATE,
is_active BOOLEAN
);

This table stores:

• Student ID

• Name and email

• Age

• Course description

• Fees amount

• Admission date

• Active status

9. Best Practices for Choosing MySQL Data Types

To design efficient databases, follow these best practices:

1. Use the Smallest Possible Data Type

Smaller types improve performance and reduce storage.

Example:
Use TINYINT instead of INT if numbers are small.

2. Use VARCHAR Instead of CHAR for Variable Data

Example:

Names vary in length, so VARCHAR is better than CHAR.

3. Use DECIMAL for Financial Data

Avoid FLOAT for money calculations.

Example:

4. Use Proper Date Types

Do not store dates as text.

Use:

10. Conclusion

Understanding MySQL data types is a fundamental skill for anyone working with databases. Choosing the correct data type helps ensure:

• Better database performance

• Efficient storage usage

• Accurate data representation

• Improved query speed

From numeric and string types to date, binary, and boolean values, MySQL provides a wide range of data types to support different kinds of applications. By carefully selecting the right data type for each column, developers and database administrators can build scalable, efficient, and reliable database systems.

For learners who want to gain practical experience with databases, working on real-world SQL examples and projects is the best way to strengthen their understanding of concepts like MySQL data types and database design.