MySQL Data Types

Beginner

Fundamentals

Choose data types precisely: INT vs BIGINT, VARCHAR vs TEXT, DATETIME vs TIMESTAMP (timezone-aware), DECIMAL for exact numerics, and ENUM for constrained string sets.

Overview

Choosing the right data type is one of the most impactful schema design decisions. Smaller data types mean smaller rows, more rows per page, better buffer pool utilisation, and faster queries. Key rules: use the smallest integer type that fits the range; use DECIMAL (not FLOAT/DOUBLE) for money; use DATETIME for timestamps that must survive timezone changes; use VARCHAR over CHAR unless data is truly fixed-length; use TEXT/BLOB only when necessary and never in indexes.

Numeric Types

Use the smallest integer that fits the value range. TINYINT (1 byte), SMALLINT (2), MEDIUMINT (3), INT (4), BIGINT (8). DECIMAL is exact (stored as string internally); FLOAT/DOUBLE are approximate — never use for currency.

SQL — numeric types and money storage

-- Integer type sizes
-- TINYINT:   -128 to 127          (unsigned: 0–255)        1 byte
-- SMALLINT:  -32,768 to 32,767    (unsigned: 0–65535)      2 bytes
-- MEDIUMINT: -8M to 8M                                     3 bytes
-- INT:       -2B to 2.1B          (unsigned: 0–4.3B)       4 bytes
-- BIGINT:    -9.2×10^18 to 9.2×10^18                       8 bytes

CREATE TABLE products (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  -- IDs can be large
    stock       MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,    -- max 16M units
    weight_g    SMALLINT UNSIGNED,                         -- max 65 kg (in grams)
    views       INT UNSIGNED NOT NULL DEFAULT 0,           -- up to 4B views
    -- Money: use DECIMAL(precision, scale) — NEVER FLOAT
    price       DECIMAL(10, 2) NOT NULL,   -- 99,999,999.99 max
    tax_rate    DECIMAL(5, 4) NOT NULL,    -- 0.1875 = 18.75%
    -- Floating point — only for scientific/imprecise values
    latitude    FLOAT,
    longitude   FLOAT,
    PRIMARY KEY (id)
);

-- ✗ Float precision problem with money:
-- SELECT 0.1 + 0.2;  → 0.30000000000000004 (approximate!)
-- ✓ Use DECIMAL for exact values

String Types

VARCHAR(n) is variable-length (1–2 bytes overhead); CHAR(n) is fixed-length, padded with spaces — faster for fixed-length data like country codes or UUIDs. TEXT types cannot be indexed directly (only prefix indexes) and cannot have DEFAULT values.

SQL — VARCHAR vs CHAR vs TEXT

CREATE TABLE customers (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -- VARCHAR — variable-length, right-sized
    email       VARCHAR(254) NOT NULL,           -- max RFC 5321 email length
    name        VARCHAR(200) NOT NULL,
    -- CHAR — fixed-length, no padding overhead (e.g. codes, hashes)
    country_code CHAR(2) NOT NULL DEFAULT 'IN',  -- always 2 chars
    status_code  CHAR(10) NOT NULL,              -- fixed-length status
    -- TEXT — for long content; not indexable in full, slower
    bio          TEXT,                            -- up to 65 KB
    -- MEDIUMTEXT: up to 16 MB, LONGTEXT: up to 4 GB
    -- UUID storage: CHAR(36) wastes space; use BINARY(16) or BIGINT
    uuid         CHAR(36),                        -- simple but inefficient
    -- Efficient UUID: BINARY(16) + UUID_TO_BIN()/BIN_TO_UUID()
    -- uuid_bin    BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1))
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Date/Time Types

DATETIME stores the literal date/time (no timezone — 8 bytes). TIMESTAMP stores UTC and converts to/from the session timezone (4 bytes, range 1970–2038). Use DATETIME for birthdates and scheduled events; TIMESTAMP for audit columns.

SQL — DATETIME vs TIMESTAMP, DATE, TIME

CREATE TABLE orders (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -- DATETIME: stored as-is, no timezone conversion, range 1000–9999
    scheduled_at DATETIME NOT NULL,       -- scheduled pick-up, user's local time
    -- TIMESTAMP: stored as UTC, auto-converts to session timezone
    -- Range: 1970-01-01 to 2038-01-19 (Y2K38 problem)
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
    -- DATE: date only (3 bytes) — no time component
    delivery_date DATE,
    -- TIME: time only (3 bytes) — duration or time-of-day
    delivery_window TIME,
    PRIMARY KEY (id)
);

-- YEAR(created_at) vs DATETIME(3) precision
-- DATETIME(3) stores milliseconds — useful for ordering events
-- DATETIME(6) stores microseconds

-- Timezone tip
SET SESSION time_zone = '+05:30';
SELECT NOW();          -- TIMESTAMP returns current time in IST
SELECT created_at FROM orders WHERE id = 1;  -- converted from UTC to IST

Key Points to Remember

1Use the smallest integer type that fits the range — TINYINT saves 7 bytes vs BIGINT per row.
2Use DECIMAL for money/exact values — FLOAT/DOUBLE are approximate and will cause rounding errors.
3VARCHAR is variable-length; CHAR is fixed-length and padded — use CHAR for codes and hashes.
4TEXT/BLOB cannot have DEFAULT values and cannot be fully indexed (only prefix indexes).
5TIMESTAMP (UTC, 4 bytes, 2038 limit) vs DATETIME (literal, 8 bytes, no timezone) — choose carefully.
6utf8mb4 is the correct charset for full Unicode including emoji; plain utf8 in MySQL only has 3-byte chars.

Interview Questions

Why should you never use FLOAT for storing monetary values in MySQL?

EasyAmazon

What is the difference between DATETIME and TIMESTAMP in MySQL?

MediumFlipkart

When would you use CHAR instead of VARCHAR?

MediumInfosys

What is the Y2K38 problem and which MySQL type is affected?

HardLinkedIn

What is the difference between utf8 and utf8mb4 in MySQL?

MediumTCS

Ask Aria about MySQL Data Types

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

MySQL Architecture

DDL — CREATE, ALTER, DROP