MySQL Data Types Overview

Every column in a MySQL table must have a data type. A data type tells MySQL what kind of value will be stored in that column — a whole number, a piece of text, a date, or something else. Choosing the correct data type is one of the most important decisions you make when designing a database, because it directly affects storage size, query speed, and data accuracy.

---

Why Data Types Matter

It might be tempting to store everything as plain text, but that approach has serious downsides. Consider these two scenarios:

• If you store a price like 499.99 as a VARCHAR instead of DECIMAL, MySQL cannot perform accurate arithmetic on it — and sorting will give wrong results.
• If you store a date like '2024-03-15' as VARCHAR instead of DATE, you lose the ability to do date comparisons, age calculations, or range queries correctly.

Declaring the right data type ensures MySQL validates your data, stores it efficiently, and lets you run meaningful queries on it.

---

The Four Main Categories

MySQL data types are grouped into four broad families. Each family is covered in its own dedicated lesson — this page gives you the big picture first.

---

Quick Reference Table

The table below lists the most commonly used MySQL data types, their purpose, and typical use cases. You do not need to memorize all of these right now — use this as a reference as you build your tables.

Data Type	Category	Typical Use
TINYINT	Numeric	Small integers: age, status flags (0–255)
INT	Numeric	General whole numbers: user IDs, counts, quantities
BIGINT	Numeric	Very large integers: transaction IDs, social media counters
DECIMAL(p, s)	Numeric	Exact decimal values: prices, salaries, tax amounts
FLOAT / DOUBLE	Numeric	Approximate decimals: scientific data, GPS coordinates
CHAR(n)	String	Fixed-length strings: country codes, gender flags
VARCHAR(n)	String	Variable-length strings: names, email addresses, titles
TEXT	String	Long text content: blog posts, comments, descriptions
ENUM	String	A fixed list of allowed values: status, role, category
DATE	Date & Time	Calendar dates: birthdays, event dates (YYYY-MM-DD)
TIME	Date & Time	Time of day: opening hours, duration (HH:MM:SS)
DATETIME	Date & Time	Combined date and time: order timestamps, appointments
TIMESTAMP	Date & Time	Auto-recorded date/time: created_at, updated_at columns
BLOB	Binary	Binary data: uploaded images, files, PDFs
JSON	Other	Structured JSON documents: configuration, flexible attributes

---

A Practical Example

Here is a simple products table that uses several different data types together. Notice how each column is assigned a type that best matches its real-world meaning:

CREATE TABLE products (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    category ENUM('Electronics','Clothing','Books','Food'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

Breaking it down:

• id — an integer that auto-increments with every new row.
• name — up to 150 characters for a product name.
• description — unlimited text for a long product description.
• price — an exact decimal with two decimal places (e.g., 299.99).
• stock — a whole number for quantity in stock.
• category — only one of the four defined values can be entered.
• created_at — automatically records when each row was inserted.

---

DECIMAL vs FLOAT — Which to Use for Money?

This is one of the most common beginner mistakes. FLOAT and DOUBLE store approximate values — they are designed for scientific calculations where tiny rounding errors are acceptable. For financial data, even a difference of 0.000001 can accumulate across thousands of rows and cause incorrect totals.

Always use DECIMAL for prices, salaries, and any value where exactness matters.

Type	Precision	Good For	Avoid For
DECIMAL	Exact	Money, tax, measurements	—
FLOAT	Approximate (~7 digits)	Scientific data, coordinates	Financial calculations
DOUBLE	Approximate (~15 digits)	High-precision scientific data	Financial calculations

---

CHAR vs VARCHAR — Fixed vs Variable Length

Both CHAR and VARCHAR store text, but they behave differently:

• CHAR(n) always reserves exactly n characters of storage. If you store 'BD' in a CHAR(5) column, MySQL pads it with spaces to fill all 5 characters. It is faster for columns where all values are the same length.
• VARCHAR(n) only uses the space it actually needs, plus 1–2 bytes to record the length. It is the right choice when values vary in length, which is most of the time.

---

NULL vs NOT NULL and Data Types

Every column, regardless of its data type, can optionally allow NULL — meaning "no value was provided". You control this with NOT NULL and NULL column constraints. This is not a data type itself, but it works closely with data types when designing a table:

CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL,
    salary DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id)
);

Here, every employee must have a name and salary, but phone is optional. Marking required columns as NOT NULL protects your data and prevents accidental gaps.

---

Key Points to Remember

• Every MySQL column requires a data type — it tells MySQL what kind of value to expect and how to store it.
• The four main categories are: Numeric, String, Date & Time, and Binary/Other.
• Always use DECIMAL for money and financial values — never FLOAT or DOUBLE.
• Use VARCHAR for most text columns; reserve CHAR for truly fixed-length values.
• Use TIMESTAMP for auto-recorded creation and update times.
• Choosing the smallest appropriate type saves storage and improves query performance.
• Pair your data type with NOT NULL where a value is always required.