MySQL Data Types

Every column in a MySQL table must have a data type. A data type tells MySQL what kind of data a column will hold — for example, numbers, text, or dates. Choosing the correct data type is important for accuracy, storage efficiency, and performance.

Why Data Types Matter

When creating a table, MySQL needs to know what kind of data each column will store. If a column is meant to store age, it should hold numbers. If it is meant to store a name, it should hold text. Using the correct data type:

Prevents invalid data from being entered (e.g., storing "abc" in an age column).
Saves storage space by using the most efficient type for the data.
Improves query speed and accuracy.

Categories of MySQL Data Types

MySQL data types fall into three main categories:

Numeric Types
String (Text) Types
Date and Time Types

Numeric Data Types

These types store numbers — either whole numbers (integers) or numbers with decimal places.

Integer Types

Data Type	Storage	Range (Signed)	Use Case
TINYINT	1 byte	-128 to 127	Boolean flags, small counters
SMALLINT	2 bytes	-32,768 to 32,767	Age, year values
MEDIUMINT	3 bytes	-8,388,608 to 8,388,607	Medium-range counters
INT	4 bytes	-2,147,483,648 to 2,147,483,647	General-purpose IDs, counts
BIGINT	8 bytes	Very large range	Large IDs, financial figures

Decimal and Floating Point Types

Data Type	Description	Use Case
FLOAT	Approximate decimal, 4 bytes	Scientific measurements
DOUBLE	Approximate decimal, 8 bytes	Precise scientific calculations
DECIMAL(p, s)	Exact decimal with precision p and scale s	Currency, prices, financial data

Example: DECIMAL(8, 2) stores a number up to 8 digits total with 2 digits after the decimal point — for example, 123456.78.

String (Text) Data Types

These types store text values like names, emails, and descriptions.

Data Type	Description	Use Case
CHAR(n)	Fixed-length string of n characters	Country codes, fixed codes (e.g., "US")
VARCHAR(n)	Variable-length string up to n characters	Names, email addresses, titles
TEXT	Long text, up to 65,535 characters	Articles, comments, descriptions
MEDIUMTEXT	Up to 16 million characters	Large documents
LONGTEXT	Up to 4 billion characters	Very large content storage
ENUM	A column that accepts one value from a predefined list	Status fields (e.g., 'active', 'inactive')

CHAR vs VARCHAR

CHAR(10) always uses 10 characters of storage, even if the value is shorter — padding with spaces if needed. VARCHAR(10) uses only as much space as the actual value requires, up to 10 characters. Use CHAR for fixed-length values like country codes, and VARCHAR for variable-length values like names.

Date and Time Data Types

Data Type	Format	Use Case
DATE	YYYY-MM-DD	Birthdates, deadlines
TIME	HH:MM:SS	Schedules, durations
DATETIME	YYYY-MM-DD HH:MM:SS	Timestamps, order dates
TIMESTAMP	YYYY-MM-DD HH:MM:SS	Auto-recording of creation/update time
YEAR	YYYY	Storing only the year

Example: Choosing Data Types for a Student Table

Consider a table that stores student information:

CREATE TABLE students (
    student_id  INT,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    age         TINYINT,
    grade       CHAR(2),
    gpa         DECIMAL(3, 2),
    enrolled_on DATE
);

Explanation of each choice:

student_id INT — IDs are whole numbers.
first_name VARCHAR(50) — Names vary in length, up to 50 characters.
age TINYINT — Ages are small numbers, TINYINT is sufficient.
grade CHAR(2) — Grades like "A+" are always 1–2 characters, so CHAR is appropriate.
gpa DECIMAL(3, 2) — GPA like 3.75 needs exact decimal precision.
enrolled_on DATE — Stores only the date, no time needed.

Key Points

Always choose the smallest data type that fits the data to save storage.
Use DECIMAL for financial values — never FLOAT or DOUBLE, as those are approximate.
Use VARCHAR for most text fields; use CHAR only for fixed-length values.
Use DATETIME when both date and time are needed; use DATE when only the date matters.

Previous lessons

Back to courses

Next lessons