Data Types

Learn MySQL's major data type categories and how to choose the right type for numbers, text, dates, and booleans.

Introduction

Every column in a MySQL table must be given a data type, which tells MySQL exactly what kind of value that column can hold — a number, some text, a date, or something else. Choosing the right data type is one of the most important decisions you make when designing a table.

This lesson covers MySQL's major data type categories, so that when you create the students table in the next lesson, you already know exactly which type to use for each column.

What You Will Learn

Why choosing the right data type matters.
MySQL's numeric types: INT, DECIMAL, and FLOAT.
MySQL's string types: VARCHAR, CHAR, and TEXT.
MySQL's date/time types: DATE, DATETIME, and TIMESTAMP.
How boolean values are represented, and how to choose between similar types.

Why Data Types Matter

A data type does three important things at once: it restricts what values a column can hold (an INT column will reject the text "hello"), it determines how much storage space is used, and it affects how fast MySQL can search and sort that column.

Rule of Thumb

Pick the data type that most precisely matches what the value actually is — an age is a whole number, a price has decimals, a birthdate is a date, not free text. Precise types catch mistakes early and keep your database efficient.

Numeric Types

MySQL offers several numeric types, but three are used the vast majority of the time.

INT

Stores whole numbers, like an age, a quantity, or an id. No decimal point allowed.

DECIMAL(p, s)

Stores exact fixed-point numbers, ideal for money, where p is total digits and s is digits after the decimal.

FLOAT

Stores approximate decimal numbers, faster but less precise — fine for scientific measurements, risky for money.

age INT,
price DECIMAL(8, 2),
temperature FLOAT

DECIMAL(8, 2) means up to 8 total digits, with exactly 2 of them after the decimal point — enough to store a value like 123456.78.

String Types

Text is stored using one of three main types, depending on whether the length is fixed, variable, or potentially very long.

VARCHAR(n)

Variable-length text, up to n characters. Only uses as much space as the actual text needs.

CHAR(n)

Fixed-length text, always exactly n characters, padded with spaces if shorter. Best for uniform-length values.

TEXT

For long, variable text like paragraphs or articles — much larger capacity than VARCHAR.

name VARCHAR(50),
country_code CHAR(2),
biography TEXT

Date & Time Types

Dates and times have their own dedicated types, which is far safer than storing them as plain text.

DATE

Stores just a calendar date, in the format YYYY-MM-DD, like 2026-07-26.

DATETIME

Stores a date and time together, like 2026-07-26 14:30:00. Not affected by time zone.

TIMESTAMP

Similar to DATETIME, but often used for tracking exactly when a row was created or changed, and is time-zone aware.

birth_date DATE,
enrolled_at DATETIME,
last_updated TIMESTAMP

Boolean Type

MySQL does not have a truly separate boolean storage type. Instead, BOOLEAN is simply an alias for TINYINT(1), a tiny integer that conventionally stores 0 (false) or 1 (true).

is_active BOOLEAN

Result

Column stored internally as TINYINT(1); 0 = false, 1 = true

Good to Know

Even though BOOLEAN is really TINYINT(1) under the hood, writing BOOLEAN in your CREATE TABLE statements is still recommended — it clearly communicates intent to anyone reading your schema.

Full Data Type Reference

Category	Type	Stores	Example
Numeric	INT	Whole numbers	25, -10, 1000
Numeric	DECIMAL(p, s)	Exact decimal numbers	199.99
Numeric	FLOAT	Approximate decimal numbers	98.6
String	VARCHAR(n)	Variable-length text up to n chars	'Aarav'
String	CHAR(n)	Fixed-length text of exactly n chars	'IN'
String	TEXT	Long-form variable text	A full paragraph
Date/Time	DATE	Calendar date only	'2026-07-26'
Date/Time	DATETIME	Date and time together	'2026-07-26 14:30:00'
Date/Time	TIMESTAMP	Date and time, time-zone aware	'2026-07-26 14:30:00'
Boolean	BOOLEAN	True/false as 0 or 1	1

Choosing the Right Type

A few recurring decisions come up constantly when designing tables, so it is worth settling them now.

VARCHAR vs CHAR vs TEXT

Use VARCHAR for most text — names, emails, titles — where length naturally varies.
Use CHAR only when every value truly has the same fixed length, like a 2-letter country code.
Use TEXT only for genuinely long content, like an article body or a long comment.

INT vs DECIMAL for Numbers

Use INT for whole-number counts and quantities, like age or number of items.
Use DECIMAL, not FLOAT, for any value representing money.
FLOAT stores approximate values, which can introduce tiny rounding errors — unacceptable when a price must be exact to the cent.

Never Use FLOAT for Money

FLOAT is a binary approximation of a decimal number, so repeated calculations can drift by fractions of a cent. DECIMAL stores the exact digits you specify, which is why every serious application uses DECIMAL for prices, balances, and any other currency value.

Common Mistakes

Avoid These Mistakes

Storing prices or money as FLOAT instead of DECIMAL.
Using CHAR for names or other naturally variable-length text, wasting storage on padding.
Storing dates as plain VARCHAR text instead of using DATE, DATETIME, or TIMESTAMP.
Forgetting to specify a length for VARCHAR, like VARCHAR alone instead of VARCHAR(50).

Best Practices

Match the data type to what the value actually represents, not just what "seems to work."
Default to VARCHAR for text unless you have a specific reason to use CHAR or TEXT.
Always use DECIMAL, never FLOAT, for any monetary value.
Use dedicated date/time types instead of storing dates as plain strings, so MySQL can validate and sort them correctly.

Frequently Asked Questions

What is the maximum length for VARCHAR?

VARCHAR supports up to 65,535 characters, though the practical limit is often lower depending on the row size and character encoding — for very long text, TEXT is usually the better choice.

Does BOOLEAN really exist in MySQL?

MySQL accepts the word BOOLEAN in a CREATE TABLE statement, but internally stores it as TINYINT(1). It behaves like a boolean for practical purposes, even though it is not a fully distinct type.

What is the difference between DATETIME and TIMESTAMP?

Both store a date and time, but TIMESTAMP is time-zone aware and has a smaller supported date range, while DATETIME stores the value exactly as given, with no time zone conversion.

Why does DECIMAL take two numbers, like DECIMAL(8, 2)?

The first number is the total number of digits stored (precision), and the second is how many of those digits come after the decimal point (scale). DECIMAL(8, 2) allows values up to 999999.99.

Key Takeaways

Every column needs a data type, which controls what values it accepts, how much space it uses, and how fast it can be queried.
INT is for whole numbers; DECIMAL is for exact decimals like money; FLOAT is for approximate scientific values.
VARCHAR fits most text, CHAR fits fixed-length values, and TEXT fits long-form content.
DATE, DATETIME, and TIMESTAMP each store date/time information, with TIMESTAMP being time-zone aware.
BOOLEAN is really TINYINT(1) under the hood, storing 0 for false and 1 for true.

Summary

Choosing the correct data type for every column is one of the most consequential decisions in table design — it enforces valid data, saves storage, and speeds up queries throughout the life of your database.

In this lesson, you learned MySQL's numeric, string, date/time, and boolean types, and how to choose between similar options like VARCHAR vs CHAR vs TEXT and INT vs DECIMAL. Next, you will use these exact types to create your first real table.

Lesson 10 Completed

You know MySQL's major numeric, string, date/time, and boolean types.
You can choose between VARCHAR, CHAR, and TEXT confidently.
You understand why DECIMAL, not FLOAT, is correct for money.
You are ready to create your first table using these types.

Next Lesson →