MySQL Data Types

Data Type Categories

INT, DECIMAL, FLOAT

VARCHAR, TEXT, CHAR

DATE, DATETIME, TIMESTAMP

BLOB, BINARY, VARBINARY

🔹 Numeric Data Types

Numeric types store numbers ranging from small integers to large decimals. INT stores whole numbers, DECIMAL preserves exact decimal values for financial data, FLOAT handles approximate decimals, and TINYINT saves space for small numbers. Choose based on your value range and precision requirements.

-- Integer types
TINYINT     -- Range: -128 to 127 (1 byte)
SMALLINT    -- Range: -32,768 to 32,767 (2 bytes)
MEDIUMINT   -- Range: -8,388,608 to 8,388,607 (3 bytes)
INT         -- Range: -2,147,483,648 to 2,147,483,647 (4 bytes)
BIGINT      -- Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (8 bytes)

-- Decimal types
DECIMAL(10,2)  -- Exact decimal: 10 digits, 2 after decimal
FLOAT          -- Approximate decimal (4 bytes)
DOUBLE         -- Approximate decimal (8 bytes)

-- Example table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quantity SMALLINT,
    price DECIMAL(10,2),
    weight FLOAT
);

🔹 String Data Types

String types store text data of varying lengths. VARCHAR is variable-length and efficient for most text, CHAR is fixed-length for consistent data, TEXT handles large content like articles, and ENUM restricts values to predefined options. Select the type that matches your text storage needs and query patterns.

-- Variable-length strings
VARCHAR(255)   -- Max 255 characters, uses only needed space
VARCHAR(1000)  -- Max 1000 characters

-- Fixed-length strings
CHAR(10)       -- Always uses 10 characters

-- Large text
TEXT           -- Max 65,535 characters
MEDIUMTEXT     -- Max 16,777,215 characters
LONGTEXT       -- Max 4,294,967,295 characters

-- Enumeration
ENUM('small', 'medium', 'large')

-- Example table
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    slug CHAR(50),
    content TEXT,
    status ENUM('draft', 'published', 'archived')
);

🔹 Date and Time Data Types

Date and time types store temporal information with different levels of precision. DATE stores only the date, TIME stores only time, DATETIME combines both, TIMESTAMP auto-updates and handles time zones, and YEAR stores just the year. Use these for scheduling, logging, and time-based analysis.

-- Date types
DATE           -- Format: YYYY-MM-DD (1000-01-01 to 9999-12-31)
TIME           -- Format: HH:MM:SS
DATETIME       -- Format: YYYY-MM-DD HH:MM:SS
TIMESTAMP      -- Auto-updates, timezone aware
YEAR           -- Format: YYYY (1901 to 2155)

-- Example table
CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATE,
    start_time TIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    event_year YEAR
);

-- Insert example
INSERT INTO events (event_date, start_time, event_year)
VALUES ('2024-12-25', '14:30:00', 2024);

🔹 Binary Data Types

Binary types store non-text data like images, files, and encrypted information. BLOB stores large binary objects, BINARY is fixed-length binary, and VARBINARY is variable-length binary. These are useful for multimedia content, file uploads, and storing encrypted or serialized data in your database.

-- Binary types
BINARY(16)     -- Fixed-length binary (e.g., for UUIDs)
VARBINARY(255) -- Variable-length binary

-- BLOB types for large binary data
TINYBLOB       -- Max 255 bytes
BLOB           -- Max 65,535 bytes
MEDIUMBLOB     -- Max 16,777,215 bytes
LONGBLOB       -- Max 4,294,967,295 bytes

-- Example table
CREATE TABLE files (
    id INT PRIMARY KEY,
    file_name VARCHAR(255),
    file_data MEDIUMBLOB,
    file_hash BINARY(32),
    mime_type VARCHAR(100)
);

🔹 Boolean Data Type

MySQL uses TINYINT(1) to represent boolean values where 0 means false and 1 means true. You can use TRUE and FALSE keywords in queries for better readability. Boolean types are perfect for flags, status indicators, and yes/no fields in your database schema.

-- Boolean (stored as TINYINT(1))
BOOLEAN        -- Alias for TINYINT(1)
BOOL           -- Same as BOOLEAN

-- Example table
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOL DEFAULT FALSE,
    email_notifications TINYINT(1) DEFAULT 1
);

-- Insert with boolean values
INSERT INTO users (username, is_active, is_verified)
VALUES ('john_doe', TRUE, FALSE);

-- Query with boolean
SELECT * FROM users WHERE is_active = TRUE;

🔹 JSON Data Type

The JSON data type stores structured JSON documents efficiently with validation and indexing support. It's ideal for flexible schemas, storing configuration data, API responses, and semi-structured information. MySQL provides functions to query and manipulate JSON data directly within SQL statements.

-- JSON data type
JSON

-- Example table
CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    preferences JSON,
    metadata JSON
);

-- Insert JSON data
INSERT INTO user_profiles (username, preferences, metadata)
VALUES (
    'alice',
    '{"theme": "dark", "language": "en", "notifications": true}',
    '{"last_login": "2024-01-15", "login_count": 42}'
);

-- Query JSON data
SELECT username, 
       JSON_EXTRACT(preferences, '$.theme') as theme
FROM user_profiles;

-- Update JSON field
UPDATE user_profiles
SET preferences = JSON_SET(preferences, '$.theme', 'light')
WHERE username = 'alice';

🔹 Choosing the Right Data Type

Selecting appropriate data types improves performance, saves storage space, and ensures data integrity. Use the smallest type that fits your data, prefer VARCHAR over TEXT for shorter strings, use DECIMAL for money, and always consider future growth. Proper data types prevent errors and optimize query execution.

-- Good practices
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL,        -- Not too large
    customer_id INT NOT NULL,                 -- Appropriate size
    total_amount DECIMAL(10,2) NOT NULL,      -- Exact for money
    status ENUM('pending', 'paid', 'shipped', 'delivered'),
    order_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT                                -- Only for large text
);

-- Bad practices (avoid these)
CREATE TABLE bad_example (
    id VARCHAR(255),              -- Too large for ID
    age VARCHAR(100),             -- Should be TINYINT
    price FLOAT,                  -- Should be DECIMAL for money
    status VARCHAR(255),          -- Should be ENUM
    description VARCHAR(10000)    -- Should be TEXT
);