? MySQL Data Types

Choosing the right data type in MySQL helps you store data efficiently, avoid errors, and improve performance.

MySQL Basics Data Modeling Table Design

⚡ Quick Overview

MySQL provides different categories of data types so that each column can store the right kind of value, such as numbers, text, or dates.

Numeric types – for integers, decimals, and floating point numbers.
String types – for text, short strings, and long documents.
Date & time types – for dates, times, and timestamps.
Other types – such as BOOLEAN, ENUM, and SET.

? Key Concepts

Domain of a column: the set of allowed values, defined mainly by its data type.
Storage size: some data types consume more bytes than others.
Precision & scale: important for decimal numbers (money, measurements).
Unsigned numbers: disallow negative values but allow a larger positive range.
NULL handling: controls whether a column can store "no value".

? Syntax & Theory

When creating a table, you specify the data type directly after the column name.

? View Code Example

-- Defining columns with different MySQL data types
CREATE TABLE products (
    id INT UNSIGNED PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    in_stock BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

In the example above:

INT UNSIGNED – integer ≥ 0, good for IDs.
VARCHAR(100) – variable-length string up to 100 characters.
DECIMAL(10,2) – up to 10 digits total, with 2 digits after the decimal point.
BOOLEAN – stored internally as TINYINT(1) (0 or 1).
DATETIME – stores both date and time.

? Numeric Data Types

Use numeric types when you want to store numbers that can be used in calculations.

INT – common integer type, 4 bytes.
TINYINT – very small integer, good for flags (0/1).
BIGINT – very large integer values.
DECIMAL(p,s) – exact numeric values (money, prices).
FLOAT / DOUBLE – approximate numeric values (scientific calculations).

? View Code Example

-- Example: numeric data types for an orders table
CREATE TABLE orders (
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(8,2) NOT NULL,
    discount_percent TINYINT UNSIGNED DEFAULT 0
);

? String (Character) Data Types

String types are used to store text, from short labels to long articles.

CHAR(n) – fixed-length string; pads with spaces if shorter.
VARCHAR(n) – variable-length string; very common for names, emails, etc.
TEXT – large text data.
MEDIUMTEXT / LONGTEXT – very large text content.

? View Code Example

-- Example: string data types for a users table
CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    bio TEXT NULL
);

⏰ Date & Time Data Types

Date and time types help you track when things happened.

DATE – stores only date in YYYY-MM-DD format.
TIME – stores only time in HH:MM:SS format.
DATETIME – stores both date and time.
TIMESTAMP – date & time with automatic timezone conversion and defaults.
YEAR – stores a year value.

? View Code Example

-- Example: date & time columns for an events table
CREATE TABLE events (
    event_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

? Other Useful Data Types

MySQL also provides special-purpose types that limit values to a predefined set.

BOOLEAN – synonym for TINYINT(1), usually 0 or 1.
ENUM – one value from a list, e.g., 'pending', 'paid', 'cancelled'.
SET – multiple values from a list.
BLOB – binary data (images, files), usually not recommended for very large files.

? View Code Example

-- Example: ENUM and BOOLEAN in an invoice table
CREATE TABLE invoices (
    invoice_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status ENUM('PENDING', 'PAID', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    is_recurring BOOLEAN DEFAULT FALSE
);

? Live Output / Explanation

Let us insert some sample data and see how MySQL stores it according to the data types.

? View Code Example

-- Insert sample rows to observe data types in action
INSERT INTO products (id, name, price, in_stock)
VALUES
    (1, 'Keyboard', 999.50, TRUE),
    (2, 'Mouse', 499.00, TRUE),
    (3, 'Monitor', 8999.99, FALSE);

? What happens here?

id uses INT UNSIGNED, so negative IDs would be rejected.
price uses DECIMAL(10,2), so every value will be stored with exactly 2 decimal places.
in_stock uses BOOLEAN, so TRUE is stored internally as 1 and FALSE as 0.
If you tried to insert a string like 'abc' into price, MySQL would either convert or reject the value depending on SQL mode.

? How to Choose the Right Data Type

Understand the nature of data – Is it numeric, text, date, or a fixed list?
Avoid over-sizing – Do not use BIGINT if INT is enough.
Use DECIMAL for money – Avoid floating-point rounding issues.
Use ENUM for controlled states – Good for status values.
Allow NULL only when needed – Keeps data cleaner.

?️ Tips & Best Practices

Match the data type to the real-world meaning (e.g., DATE for birth dates, not VARCHAR).
Use UNSIGNED for IDs and counts that can never be negative.
Prefer VARCHAR over TEXT when the maximum length is known and small.
Use DECIMAL for prices and financial information.
Document your schema so other developers understand why each data type was chosen.

? Try It Yourself

Create a students table with:
- student_id as INT UNSIGNED primary key
- full_name as VARCHAR(80)
- email as VARCHAR(120)
- dob as DATE
- gpa as DECIMAL(3,2)
Design a blog_posts table that uses both VARCHAR and TEXT types.
Modify an existing table to change a column from VARCHAR to INT and see what happens if the data cannot be converted.
Add an ENUM column called priority to a tasks table with values 'LOW', 'MEDIUM', 'HIGH'.

? View Practice Schema Example

-- Practice: create a students table with appropriate data types
CREATE TABLE students (
    student_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(80) NOT NULL,
    email VARCHAR(120) NOT NULL,
    dob DATE NOT NULL,
    gpa DECIMAL(3,2) NULL
);