Introduction

When creating a table in MySQL, choosing the right data type for each column is crucial. It defines what kind of values that column can hold — whether it’s text, numbers, dates, or true/false values.

1. Data types in MySQL define the kind of data that can be stored in each column of a table — like numbers, text, or dates.
2. They ensure data accuracy and memory efficiency by allocating the right storage size and format.
3. MySQL supports different categories of data types: Numeric, String (Character), Date/Time, and Spatial.
4. Choosing the right data type is crucial for performance optimization and preventing invalid data entries.
5. Some types like VARCHAR are flexible for text, while others like INT, DECIMAL, and DATE are more specific.
6. Each column in a table must be assigned a specific data type when the table is created or altered.

The most common and useful data types are as follows:- 

1. INT – Whole Numbers (No Decimal):- INT is used to store integer values — meaning numbers without any decimal part. It’s perfect for values like age, quantity, roll numbers, or employee IDs.

Example:

age INT;

2. FLOAT and DOUBLE (Decimal Numbers):- When you need fractional or decimal values, use FLOAT or DOUBLE. They store numbers like 5.75, 99.99, or 3.14159.

• FLOAT is suitable for approximate values with less precision (e.g., product ratings).
• DOUBLE offers more precision and is better for financial or scientific calculations.

Example:

price FLOAT;

3. VARCHAR(n) – Variable-Length Text:- Use VARCHAR when you want to store short to medium-length text such as names, emails, cities, etc. You specify a maximum length (n) to limit how many characters can be stored.

Example:

name VARCHAR(100);

4. TEXT – Large Text Content:- TEXT is for storing long blocks of text, like descriptions, feedback, articles, or reviews. Unlike VARCHAR, TEXT is meant for when you expect a lot of text.

Example:

description TEXT;

5. DATE – Calendar Dates Only:- The DATE type stores values in YYYY-MM-DD format. It’s best for birthdays, registration dates, join dates, etc.

Example:

birthdate DATE;

6. DATETIME – Date + Time Stamp:- This stores both the date and the exact time. It’s great 

for recording events like logins, signups, updates, or transactions.

Example:

created_at DATETIME;

7. BOOLEAN – True/False or Yes/No:- BOOLEAN is used when you want to store binary choices — like whether a user is active, an order is delivered, or an option is selected.

Example:

is_active BOOLEAN;

8. ENUM – Fixed Set of Allowed Values:- ENUM lets you limit a column to a predefined list of possible values. Great for things like gender, user roles, status, or categories.

Example:

gender ENUM(‘Male’, ‘Female’, ‘Other’);