MySQL Data Types (INT, VARCHAR, DATE…)

In this lesson: MySQL data types explained — numeric, string, date/time — with sizes, examples and which type to choose.

Why data types matter

Every column in a MySQL table has a data type. It is a promise about what kind of value that column will hold — a whole number, a price, a name, a date, and so on. This one decision quietly controls three things: how much disk space each row uses, how fast your queries run, and whether wrong data can sneak in.

Picture a school database. If you store a student's fee as plain text, MySQL will happily accept "abc" as a fee. But if you declare that column as DECIMAL(10,2), the wrong value is blocked at the door and every price stays exact to the paisa. That is the real job of data types — they act like the shape of a container, so only the right thing fits inside.

Numeric types (INT, DECIMAL, FLOAT)

Numeric types store numbers, but they are not interchangeable. The key split is between exact types (INT, DECIMAL) and approximate types (FLOAT, DOUBLE).

The word DECIMAL(10,2) means: up to 10 digits in total, with 2 of them after the decimal point. So the largest value is 99999999.99. This is the type you reach for whenever money is involved.

String types (CHAR, VARCHAR, TEXT)

String types hold text. The two you will use every day are CHAR and VARCHAR, and the difference is about length.

Use CHAR only when the value is genuinely a fixed width. A 2-letter state code is a perfect fit. For a student's name — which could be 3 or 30 characters — VARCHAR wastes no space. Reach for TEXT only when you expect long paragraphs, because it cannot have a default value and is slower to index.

Date and time types

MySQL has dedicated types for dates and times, which is far better than storing them as plain strings. A real date type lets you sort chronologically, compare ranges, and use date functions like DATEDIFF().

Use DATE for a birth date or admission date. Use DATETIME when you need the exact clock time of an event and want a wide range. Use TIMESTAMP when you want MySQL to auto-fill "created at" or "updated at" — but remember it stops working in the year 2038, so it is not for future planning dates.

A full CREATE TABLE example

Here is a small students table that puts one type from each family to work. Notice how each column's type matches the real thing it stores.

CREATE TABLE students (
    roll_no   INT PRIMARY KEY,          -- whole number ID
    name      VARCHAR(50) NOT NULL,     -- variable-length text
    state     CHAR(2),                  -- fixed 2-letter code
    fees      DECIMAL(10,2),            -- exact money value
    dob       DATE,                     -- only a date
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO students (roll_no, name, state, fees, dob)
VALUES (1, 'Aarav Sharma', 'UP', 12500.00, '2008-04-15');

SELECT * FROM students;

See how fees kept its two decimal places and joined_at filled itself in automatically because of DEFAULT CURRENT_TIMESTAMP. Each value landed in the right shape of container.

How to choose the right type

When you are unsure, ask three quick questions about the value:

• Is it a number I will do math on? Use INT for whole numbers, DECIMAL for money and marks.
• Is it text of changing length? Use VARCHAR. Only use CHAR for genuinely fixed-width codes, and TEXT for long paragraphs.
• Is it a date or time? Use DATE, DATETIME or TIMESTAMP — never a string, so date functions keep working.

One more rule: pick the smallest type that comfortably fits your data. A status flag that is only 0 or 1 does not need INT; TINYINT does the job in a quarter of the space. Across millions of rows, small choices like this add up to real speed.

Common mistakes

• Using FLOAT for money. It looks fine on one row and quietly breaks totals. Always DECIMAL for currency.
• Storing dates as VARCHAR. A string date like "01-06-2026" cannot be sorted or compared correctly. Use a real DATE type.
• Making everything VARCHAR(255) by habit. It works, but it hides the true shape of your data and lets wrong values in. Match the type to the meaning.
• Choosing TIMESTAMP for far-future dates. It stops at 2038. For dates beyond that, use DATETIME.

Summary

• MySQL types fall into three families: numeric, string, and date/time.
• Use INT for whole numbers and DECIMAL for money and marks — never FLOAT for currency.
• Use VARCHAR for most text, CHAR only for fixed-width codes, TEXT for long content.
• Use DATE, DATETIME or TIMESTAMP for time — remember TIMESTAMP ends in 2038.
• Pick the smallest type that fits; it saves space and speeds up queries.

Next, learn how to protect these columns further with rules using constraints in MySQL, or see how tables connect through a primary and foreign key.