MySQL Data Types: Complete Guide with Examples

Every column in a MySQL table requires a data type — a rule that tells the database exactly what kind of information it can store. Think of it like declaring a variable in programming: just as you specify int, string, or boolean in code, MySQL needs to know whether a column will hold numbers, text, dates, or binary data. This isn't just bureaucracy — choosing the right data type affects storage efficiency, query performance, and data integrity. A poorly chosen data type can waste gigabytes of disk space or cause subtle bugs when your application tries to store a value that doesn't fit.

MySQL offers dozens of data types grouped into numeric, string, date/time, and special-purpose categories. Each has different storage requirements and valid ranges. Your job as a developer is to pick the smallest, most precise type that accommodates your data without cutting corners.

• Storage efficiency — An INT uses 4 bytes while a BIGINT uses 8. Multiply that by millions of rows and you're talking real infrastructure costs.
• Query performance — Smaller data types mean more rows fit in memory caches, making WHERE clauses and joins faster.
• Data validation — Declaring a column as DATE prevents you from accidentally storing "next Tuesday" as text.
• Application correctness — Trying to store 300 in a TINYINT (max 127) silently truncates or errors out depending on SQL mode.
• Index optimization — VARCHAR(10) indexes are faster than VARCHAR(255) when you only need 10 characters.

MySQL offers integer and floating-point types. Integers store whole numbers with no decimal component; floats/decimals handle fractional values.

Use DECIMAL for money — FLOAT and DOUBLE introduce rounding errors. For a price column, DECIMAL(10,2) guarantees two decimal places and up to 10 total digits.

sql
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2) NOT NULL,  -- $99,999,999.99 max
    stock SMALLINT UNSIGNED DEFAULT 0,
    rating FLOAT  -- 4.7 stars is fine as approximate
);

Notice UNSIGNED on id and stock — negative product IDs and negative inventory make no sense, so we double the positive range.

Strings hold text. MySQL distinguishes between fixed-length (CHAR) and variable-length (VARCHAR) types, plus larger TEXT types for long content.

CHAR vs VARCHAR: If every value is exactly 2 characters (like US state codes), use CHAR(2). Otherwise, VARCHAR saves space. MySQL pads CHAR with spaces, which can cause surprises in comparisons.

sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    country_code CHAR(2) DEFAULT 'US',  -- Always 2 chars
    bio TEXT,  -- User profile text
    email VARCHAR(255) NOT NULL
);

Character sets matter: Add CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci to support emojis and international characters properly.

MySQL provides five temporal types. Pick based on whether you need a date, a time, or both — and whether you care about time zones.

DATETIME vs TIMESTAMP: TIMESTAMP converts values to UTC for storage and back to the session time zone on retrieval. It's ideal for "created_at" columns. DATETIME stores exactly what you give it with no time zone math.

sql
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The ON UPDATE CURRENT_TIMESTAMP clause auto-updates updated_at whenever you modify the row — no application code needed.

Binary types store non-text data: images, PDFs, encrypted values, serialized objects. These are the "binary large object" (BLOB) family.

Best practice: Store large files in object storage (S3, GCS) and keep only the URL or file path in MySQL. BLOBs bloat your database, slow down backups, and make queries expensive.

sql
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255),
    file_hash BINARY(32),  -- SHA-256 hash (always 32 bytes)
    file_url VARCHAR(500),  -- S3 URL instead of storing blob
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ENUM and SET

ENUM restricts a column to one value from a predefined list. SET allows multiple values from a list (stored as a bitmask).

sql
CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    tags SET('bug', 'feature', 'docs', 'urgent')  -- Can store 'bug,urgent'
);

Warning: Changing ENUM/SET values requires an ALTER TABLE, which can lock the table. For frequently changing categories, use a lookup table instead.

JSON

Modern MySQL (5.7+) supports native JSON columns with validation and indexing.

sql
CREATE TABLE configs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    settings JSON
);

INSERT INTO configs (settings) VALUES 
('{"theme": "dark", "notifications": true, "language": "en"}');

SELECT settings->'$.theme' AS theme FROM configs;

JSON is perfect for flexible schemas, but you lose relational integrity — no foreign keys inside JSON fields.

• Using VARCHAR(255) everywhere — You're wasting space. If usernames max out at 50 characters, use VARCHAR(50). Indexes on shorter columns are faster.
• Storing dates as strings — "2024-01-15" as VARCHAR breaks date math. Use DATE or DATETIME so you can do WHERE event_date > NOW().
• Choosing FLOAT for currency — FLOAT(7,2) looks like it works until you get 9.999999999 instead of 10.00. Always use DECIMAL for money.
• Forgetting UNSIGNED for IDs — An INT UNSIGNED ID lets you store 4 billion records instead of 2 billion. Auto-increment primary keys should almost always be unsigned.
• Storing large files in BLOBs — Your database backups will take hours and queries will slow to a crawl. Store files externally and save the path.
• Using TIMESTAMP past 2038 — The Y2038 problem is real. For far-future dates (retirement plans, subscriptions in 2050), use DATETIME.

💡 Think Like a Programmer: Picking a data type is like choosing the right container — you wouldn't store a single coin in a shipping crate, and you wouldn't cram an encyclopedia into a sticky note. The tighter the fit, the faster and cheaper your database runs.