MySQL Data Types

When we create a table in MySQL, we must specify the data type for each column. These are called data types.

Choosing the correct data type is very important because it affects:

  • Storage space
  • Performance
  • Accuracy of data
  • Data validation
  • Future scalability

Let’s understand MySQL data types in a simple and structured way.

1. Why Data Types Matter

In MySQL, every column must have a defined data type.

For example:

CREATE TABLE Students (

    id INT,

    name VARCHAR(50),

    age INT,

    admission_date DATE

);

Here:

  • id and age store numbers → INT
  • name stores text → VARCHAR
  • admission_date stores date → DATE

Why is this important?

  1. Prevents invalid data
    • You cannot insert text in an INT column.
  2. Improves performance
    • Smaller data types use less memory.
  3. Maintains accuracy
    • DECIMAL is better for money than FLOAT.
  4. Ensures data consistency
    • Dates stored as DATE are easier to sort and compare.

2. Numeric Data Types

Numeric types store numbers. They are divided into:

A. Integer Types

Used for whole numbers (no decimal).

Data Type

Storage

Range (Signed)

TINYINT

1 byte

-128 to 127

SMALLINT

2 bytes

-32,768 to 32,767

INT

4 bytes

-2B to 2B approx

BIGINT

8 bytes

Very large numbers

Example:

CREATE TABLE Employees (

    emp_id INT,

    experience_years TINYINT

);

Use:

  • INT for IDs
  • BIGINT for very large values
  • TINYINT for small numbers (like age, rating, etc.)

B. Decimal and Floating Types

Used for numbers with decimal values.

Data Type

Description

FLOAT

Approximate decimal value

DOUBLE

Larger approximate decimal

DECIMAL(p,s)

Exact decimal value

Example:

salary DECIMAL(10,2)

Here:

  • 10 → total digits
  • 2 → digits after decimal

Example value: 12345678.90

Important:

  • Use DECIMAL for money.
  • Avoid FLOAT for financial calculations because it is approximate.

3. String Data Types

Used to store text.

A. CHAR vs VARCHAR

Data Type

Description

CHAR(n)

Fixed-length string

VARCHAR(n)

Variable-length string

Example:

name VARCHAR(100)

gender CHAR(1)

Difference:

  • CHAR(10) always uses 10 characters.
  • VARCHAR(10) uses only the required space.

Use:

  • CHAR → fixed-size values (like gender: M/F)
  • VARCHAR → names, emails, addresses

B. TEXT Types

Used for large text data.

Data Type

Maximum Size

TEXT

65,535 characters

MEDIUMTEXT

16 million characters

LONGTEXT

4GB

Example:

description TEXT

Used for:

  • Blog content
  • Comments
  • Descriptions

4. Date & Time Data Types

Used for storing date and time values.

Data Type

Format

DATE

YYYY-MM-DD

TIME

HH:MM: SS

DATETIME

YYYY-MM-DD HH:MM: SS

TIMESTAMP

Date & time (auto-updated option)

YEAR

YYYY

Example:

CREATE TABLE Orders (

    order_date DATE,

    order_time TIME,

    created_at TIMESTAMP

);

Important difference:

  • DATETIME → Stores exact date & time.
  • TIMESTAMP → Can automatically update when a row changes.

Example:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

5. Boolean & Special Types

A. BOOLEAN

MySQL does not have a true Boolean type.

Instead:

  • BOOLEAN = TINYINT(1)
  • 0 = FALSE
  • 1 = TRUE

Example:

is_active BOOLEAN

Internally stored as TINYINT.

B. ENUM

Stores one value from a predefined list.

Example:

status ENUM('pending', 'shipped', 'delivered')

Only these values are allowed.

C. JSON

Used to store JSON-formatted data.

Example:

details JSON

Useful for:

  • Flexible data
  • Storing nested information

6. Choosing the Right Data Type (Common Mistakes)

Beginners often make these mistakes:

Mistake 1: Using VARCHAR for everything

Wrong:

age VARCHAR(10)

Correct:

age INT

Reason:
Numbers should be stored as numeric types for better performance.

Mistake 2: Using FLOAT for money

Wrong:

price FLOAT

Correct:

price DECIMAL(10,2)

Reason:
FLOAT causes rounding errors.

Mistake 3: Choosing very large data types unnecessarily

Wrong:

id BIGINT

When is INT enough?

Larger types:

  • Use more storage
  • Slightly reduce performance

Mistake 4: Using CHAR instead of VARCHAR

If length varies (like name), use VARCHAR.

Mistake 5: Storing dates as strings

Wrong:

dob VARCHAR(20)

Correct:

dob DATE

Reason:
DATE allows sorting, filtering, and date functions.

Final Summary

MySQL data types specify the types of data a column can store.

Main categories:

  1. Numeric → INT, BIGINT, DECIMAL
  2. String → CHAR, VARCHAR, TEXT
  3. Date & Time → DATE, DATETIME, TIMESTAMP
  4. Special → BOOLEAN, ENUM, JSON

Choosing the correct data type:

  • Saves storage
  • Improves performance
  • Prevents errors
  • Makes queries faster