MySQL Data Types

A data type specifies what kind of value a column can store.

id       INT
name     VARCHAR(100)
price    DECIMAL(10,2)
dob      DATE

Each column has a data type suitable for its purpose.

MySQL data types can broadly be grouped into:

• Numeric data types
• String data types
• Date and time data types
• Spatial data types
• JSON data type
• Binary data types

Numeric data types are used to store numbers.

Common examples include:

INT
BIGINT
DECIMAL
FLOAT
DOUBLE

INT is commonly used for whole numbers.

CREATE TABLE students (
    id INT,
    age INT
);

Examples of integer values are 1, 25, 100, and 500.

BIGINT is used for larger integer values than INT.

CREATE TABLE transactions (
    transaction_id BIGINT
);

It can be useful when an application needs to store very large integer values.

DECIMAL is commonly used when exact decimal values are required.

CREATE TABLE fees (
    amount DECIMAL(10,2)
);

It is commonly suitable for financial values such as fees and prices.

In DECIMAL(10,2):

• 10 represents the total number of digits.
• 2 represents the number of digits after the decimal point.

DECIMAL(10,2)

Example:

15000.50

FLOAT is used for approximate floating-point numbers.

CREATE TABLE measurements (
    value FLOAT
);

It can be useful for scientific or measurement-related values where approximate representation is acceptable.

DOUBLE is another approximate floating-point numeric type with a larger range and precision than FLOAT.

CREATE TABLE calculations (
    result DOUBLE
);

String data types are used to store text and character data.

Common examples include:

CHAR
VARCHAR
TEXT
LONGTEXT

CHAR is used for fixed-length strings.

CREATE TABLE employees (
    gender CHAR(1)
);

It can be useful when values have a consistent fixed length.

VARCHAR is used for variable-length text.

CREATE TABLE students (
    name VARCHAR(100),
    email VARCHAR(150)
);

VARCHAR is commonly used for names, emails, addresses, and similar text.

TEXT is used for larger amounts of text.

CREATE TABLE posts (
    title VARCHAR(200),
    content TEXT
);

It can be useful for descriptions, articles, comments, and other text content.

LONGTEXT is designed for very large text values.

CREATE TABLE documents (
    id INT,
    content LONGTEXT
);

It can be useful when an application needs to store very large text content.

MySQL provides data types for storing dates and times.

Common examples include:

DATE
TIME
DATETIME
TIMESTAMP
YEAR

DATE stores a calendar date.

CREATE TABLE students (
    admission_date DATE
);

A DATE value can look like:

2026-09-21

TIME stores a time value.

CREATE TABLE classes (
    start_time TIME
);

Example:

09:00:00

DATETIME stores both date and time.

CREATE TABLE attendance (
    login_time DATETIME
);

Example:

2026-09-21 09:30:00

TIMESTAMP is another date and time data type and is commonly used for recording timestamps such as when a record was created or updated.

CREATE TABLE users (
    id INT,
    created_at TIMESTAMP
);

YEAR is used to store year values.

CREATE TABLE employees (
    name VARCHAR(100),
    joining_year YEAR
);

Example:

2026

MySQL supports BOOLEAN as a synonym for TINYINT(1).

It is commonly used when an application needs a true/false-style value.

CREATE TABLE students (
    id INT,
    active BOOLEAN
);

Applications commonly use values representing true or false.

MySQL provides binary string data types for storing binary data.

Common examples include:

BINARY
VARBINARY
BLOB

These types can be useful for binary information such as certain files or raw data.

BLOB stands for Binary Large Object.

It can be used for storing binary data.

CREATE TABLE files (
    id INT,
    file_data BLOB
);

MySQL provides a JSON data type for storing JSON documents.

CREATE TABLE users (
    id INT PRIMARY KEY,
    profile JSON
);

This can be useful when applications need to store structured JSON data.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    fee DECIMAL(10,2),
    birth_date DATE,
    login_time DATETIME,
    active BOOLEAN
);

This table demonstrates several commonly used MySQL data types.

Some common mistakes include:

• Using VARCHAR for every type of data
• Using INT for decimal amounts
• Using TEXT for short fixed values unnecessarily
• Using an inappropriate date or time type
• Choosing an unnecessarily large data type
• Ignoring the actual range of values required

CREATE DATABASE IF NOT EXISTS training_db;

USE training_db;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    mobile VARCHAR(15),
    fee DECIMAL(10,2),
    admission_date DATE,
    admission_time TIME,
    created_at DATETIME,
    active BOOLEAN,
    profile JSON
);

This example demonstrates numeric, string, decimal, date, time, Boolean, and JSON data types.

Here is a practical example of selecting data types for a student management application:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    father_name VARCHAR(100),
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(150),
    age INT,
    course VARCHAR(100),
    fee DECIMAL(10,2) DEFAULT 0.00,
    admission_date DATE,
    login_time DATETIME,
    active BOOLEAN DEFAULT TRUE
);

Each column uses a data type according to the kind of information it stores.