MySQL Data Types

Duration: 75 minutes

Module 3: Session 3

Learning Objectives

Understand all MySQL data type categories
Choose optimal data types for different scenarios
Learn storage requirements and performance implications
Master numeric types (INT, DECIMAL, FLOAT)
Understand string types (VARCHAR, TEXT, CHAR)
Work with date/time types effectively
Implement JSON and spatial data types

MySQL Data Type Categories

Choosing the right data type is crucial for database performance, storage efficiency, and data integrity. Let's explore MySQL's data type ecosystem! 📊

💡

Golden Rule

Always use the smallest data type that can reliably store your data. This saves storage space and improves query performance!

Numeric Data Types

graph TB subgraph "Integer Types" T[TINYINT
-128 to 127] S[SMALLINT
-32,768 to 32,767] M[MEDIUMINT
-8,388,608 to 8,388,607] I[INT
-2,147,483,648 to 2,147,483,647] B[BIGINT
-9,223,372,036,854,775,808 to ...] end subgraph "Decimal Types" D[DECIMAL
Exact numeric] F[FLOAT
Approximate] DO[DOUBLE
Approximate] end style T fill:#dbeafe style S fill:#bfdbfe style M fill:#93c5fd style I fill:#60a5fa style B fill:#3b82f6 style D fill:#dcfce7 style F fill:#fef3c7 style DO fill:#fed7aa

Numeric Types in Detail

-- INTEGER TYPES
CREATE TABLE numeric_examples (
                                        -- TINYINT: Perfect for age, status codes, boolean flags
    age TINYINT UNSIGNED,               -- 0 to 255 (no negatives)
    status TINYINT DEFAULT 1,           -- -128 to 127
    is_active BOOLEAN DEFAULT TRUE,     -- Alias for TINYINT(1)
    
    -- SMALLINT: Good for counts, quantities
    quantity SMALLINT UNSIGNED,         -- 0 to 65,535
    year_established SMALLINT,          -- -32,768 to 32,767
    
    -- MEDIUMINT: Less common, but useful for specific ranges
    postal_code MEDIUMINT UNSIGNED,     -- 0 to 16,777,215
    
    -- INT: Most common for IDs and general numbers
    user_id INT AUTO_INCREMENT PRIMARY KEY,  -- Standard ID field
    view_count INT UNSIGNED DEFAULT 0,       -- 0 to 4,294,967,295
    
    -- BIGINT: For very large numbers
    transaction_id BIGINT UNSIGNED,     -- 0 to 18,446,744,073,709,551,615
    unix_timestamp BIGINT,               -- Stores Unix timestamps
    
    -- DECIMAL/NUMERIC: Exact decimal numbers (perfect for money)
    price DECIMAL(10,2),                -- Total 10 digits, 2 after decimal
    salary DECIMAL(12,2),                -- Up to 9,999,999,999.99
    tax_rate DECIMAL(5,4),               -- Up to 9.9999 (percentage)
    
    -- FLOAT/DOUBLE: Approximate numbers (scientific calculations)
    latitude FLOAT,                      -- Single precision
    longitude DOUBLE,                    -- Double precision
    scientific_value DOUBLE(20,10),      -- With precision specification
    
    -- BIT: For bit-field values
    permissions BIT(8),                  -- 8-bit field for flags
    feature_flags BIT(64)                -- Up to 64 boolean flags
);

-- UNSIGNED vs SIGNED
CREATE TABLE unsigned_example (
    -- SIGNED (default): Can store negative values
    temperature INT,                     -- -2,147,483,648 to 2,147,483,647
    
    -- UNSIGNED: Only positive values, doubles positive range
    product_count INT UNSIGNED,          -- 0 to 4,294,967,295
    
    -- Common pattern for IDs
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

-- AUTO_INCREMENT
CREATE TABLE auto_inc_example (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- Starts at 1 by default
    name VARCHAR(100)
) AUTO_INCREMENT = 1000;                 -- Start at 1000

-- ZEROFILL (displays with leading zeros)
CREATE TABLE zerofill_example (
    invoice_number INT(8) ZEROFILL      -- Displays as 00000001, 00000002, etc.
);

⚠️

Money Storage Warning

NEVER use FLOAT or DOUBLE for money! Use DECIMAL to avoid rounding errors:

-- Wrong
price FLOAT  -- Can cause $19.99 to become $19.990000001

-- Correct
price DECIMAL(10,2)  -- Exactly $19.99

String Data Types

String Type Guidelines

-- CHAR: Fixed-length strings (padded with spaces)
CREATE TABLE char_examples (
    country_code CHAR(2),               -- Always 2 characters (US, UK, CA)
    status_code CHAR(3),                 -- Always 3 characters (NEW, OLD, ACT)
    isbn CHAR(13),                       -- ISBN always 13 characters
    zip_code CHAR(5)                    -- US ZIP codes
);

-- VARCHAR: Variable-length strings (most common)
CREATE TABLE varchar_examples (
    username VARCHAR(50) UNIQUE,         -- Up to 50 characters
    email VARCHAR(255) NOT NULL,         -- Standard email length
    phone VARCHAR(20),                   -- International phone numbers
    first_name VARCHAR(100),             -- Names vary in length
    url VARCHAR(2083),                   -- Max URL length for browsers
    -- Note: Maximum VARCHAR is 65,535 bytes
    -- But actual max depends on character set and row size
);

-- TEXT: Large text data
CREATE TABLE text_examples (
    -- TINYTEXT: Up to 255 characters
    short_bio TINYTEXT,
    
    -- TEXT: Up to 65,535 characters (~64KB)
    article_content TEXT,
    product_description TEXT,
    
    -- MEDIUMTEXT: Up to 16,777,215 characters (~16MB)
    book_content MEDIUMTEXT,
    large_json_data MEDIUMTEXT,
    
    -- LONGTEXT: Up to 4,294,967,295 characters (~4GB)
    huge_document LONGTEXT,
    video_transcript LONGTEXT
);

-- BINARY and VARBINARY: For binary data
CREATE TABLE binary_examples (
    uuid BINARY(16),                    -- Store UUID as binary (saves space)
    file_hash BINARY(32),               -- MD5 or SHA256 hash
    encrypted_data VARBINARY(1000),     -- Variable binary data
    small_image BLOB,                    -- Binary large object
    large_file LONGBLOB                 -- Up to 4GB binary data
);

-- ENUM and SET: Predefined values
CREATE TABLE enum_set_examples (
    -- ENUM: Choose one value from list
    status ENUM('pending', 'active', 'inactive', 'deleted') DEFAULT 'pending',
    size ENUM('XS', 'S', 'M', 'L', 'XL', 'XXL'),
    priority ENUM('low', 'medium', 'high', 'urgent') NOT NULL,
    
    -- SET: Choose multiple values from list
    permissions SET('read', 'write', 'delete', 'admin'),
    features SET('newsletter', 'notifications', 'premium', 'beta'),
    days_available SET('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun')
);

-- Character Sets and Collations
CREATE TABLE charset_examples (
    -- utf8mb4: Supports all Unicode including emojis
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    
    -- Case-sensitive collation
    username VARCHAR(50) COLLATE utf8mb4_bin,
    
    -- ASCII only (smaller storage)
    country_code CHAR(2) CHARACTER SET ascii
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Date and Time Data Types

Date/Time Types Explained

Type	Format	Range	Storage	Use Case
DATE	YYYY-MM-DD	1000-01-01 to 9999-12-31	3 bytes	Birthdays, deadlines
TIME	HH:MM:SS	-838:59:59 to 838:59:59	3 bytes	Duration, time of day
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 to 9999-12-31	8 bytes	Events, appointments
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 to 2038-01-19	4 bytes	Record changes, logs
YEAR	YYYY	1901 to 2155	1 byte	Year only data

-- Date and Time examples
CREATE TABLE datetime_examples (
    -- DATE: Just the date (no time)
    birth_date DATE,
    hire_date DATE NOT NULL,
    project_deadline DATE,
    
    -- TIME: Just the time (or duration)
    start_time TIME,
    duration TIME,                      -- Can store durations like '48:30:00'
    
    -- DATETIME: Date and time (timezone-independent)
    appointment_datetime DATETIME,
    event_start DATETIME NOT NULL,
    
    -- TIMESTAMP: Date and time (auto-updates, timezone-aware)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- YEAR: Just the year
    graduation_year YEAR,
    model_year YEAR(4)                  -- 4-digit year
);

-- DATETIME vs TIMESTAMP comparison
CREATE TABLE timestamp_vs_datetime (
    -- TIMESTAMP: 
    -- • Stored in UTC, converted to timezone
    -- • Range: 1970-2038 (32-bit limitation)
    -- • Auto-updates with ON UPDATE
    -- • 4 bytes storage
    last_login TIMESTAMP NULL,
    
    -- DATETIME:
    -- • Stored as-is (no timezone conversion)
    -- • Range: 1000-9999
    -- • No auto-update unless specified
    -- • 8 bytes storage
    event_date DATETIME NOT NULL
);

-- Working with dates
INSERT INTO datetime_examples (
    birth_date, 
    appointment_datetime,
    created_at
) VALUES (
    '1990-05-15',                       -- DATE literal
    '2025-01-20 14:30:00',              -- DATETIME literal
    NOW()                               -- Current timestamp
);

-- Date functions
SELECT 
    -- Current date/time functions
    CURDATE() AS today,                 -- 2025-01-15
    CURTIME() AS now_time,              -- 14:30:45
    NOW() AS right_now,                 -- 2025-01-15 14:30:45
    
    -- Date arithmetic
    DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week,
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month,
    DATEDIFF('2025-12-31', CURDATE()) AS days_to_new_year,
    
    -- Date formatting
    DATE_FORMAT(NOW(), '%W, %M %d, %Y') AS formatted_date,  -- Wednesday, January 15, 2025
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') AS custom_format   -- 2025-01-15 14:30
FROM DUAL;

-- Fractional seconds (MySQL 5.6.4+)
CREATE TABLE precise_time (
    -- Microsecond precision (6 decimal places)
    precise_timestamp TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    precise_datetime DATETIME(3),       -- Millisecond precision
    precise_time TIME(6)                -- Microsecond precision
);

💡

TIMESTAMP vs DATETIME

UseTIMESTAMPfor recording when rows are created/modified
UseDATETIMEfor user-entered dates (birthdays, appointments)
TIMESTAMP converts to UTC for storage, DATETIME stores as-is
TIMESTAMP has the 2038 problem (Unix timestamp limit)

JSON Data Type

Working with JSON (MySQL 5.7+)

-- JSON data type (MySQL 5.7+)
CREATE TABLE json_examples (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Store JSON data
    settings JSON,
    metadata JSON NOT NULL,
    preferences JSON DEFAULT '{}',
    
    -- Generated columns from JSON
    email VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.email'))) STORED,
    
    INDEX idx_email (email)
);

-- Inserting JSON data
INSERT INTO json_examples (settings, metadata) VALUES (
    '{"theme": "dark", "notifications": true, "language": "en"}',
    '{"email": "user@example.com", "role": "admin", "tags": ["vip", "premium"]}'
);

-- JSON object creation
INSERT INTO json_examples (settings, metadata) VALUES (
    JSON_OBJECT('theme', 'light', 'notifications', false),
    JSON_ARRAY('tag1', 'tag2', 'tag3')
);

-- Querying JSON data
SELECT 
    -- Extract values
    JSON_EXTRACT(settings, '$.theme') AS theme,
    settings->>'$.theme' AS theme_unquoted,  -- Shorthand, unquoted
    
    -- Check if key exists
    JSON_CONTAINS_PATH(settings, 'one', '$.notifications') AS has_notifications,
    
    -- Search in arrays
    JSON_CONTAINS(metadata->'$.tags', '"vip"') AS is_vip,
    
    -- Get array length
    JSON_LENGTH(metadata->'$.tags') AS tag_count
FROM json_examples;

-- Updating JSON data
UPDATE json_examples 
SET settings = JSON_SET(
    settings,
    '$.theme', 'dark',
    '$.new_feature', true
)
WHERE id = 1;

-- JSON validation and indexing
ALTER TABLE json_examples 
ADD CONSTRAINT check_valid_json CHECK (JSON_VALID(settings));

-- Virtual column for indexing JSON fields
ALTER TABLE json_examples
ADD COLUMN user_role VARCHAR(50) AS (settings->>'$.role') VIRTUAL,
ADD INDEX idx_role (user_role);

⚠️

When to Use JSON

Good uses:User preferences, configuration, flexible schemas
Avoid for:Data you need to frequently search, join, or index
JSON should complement, not replace, proper relational design!

Choosing the Right Data Type

Quick Reference Guide

Data	Recommended Type	Example
Primary Key	INT UNSIGNED AUTO_INCREMENT	`user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY`
Email	VARCHAR(255)	`email VARCHAR(255) UNIQUE NOT NULL`
Username	VARCHAR(50)	`username VARCHAR(50) UNIQUE NOT NULL`
Password Hash	CHAR(60) or VARCHAR(255)	`password_hash CHAR(60) NOT NULL`
Price/Money	DECIMAL(10,2)	`price DECIMAL(10,2) NOT NULL`
Quantity	INT UNSIGNED	`quantity INT UNSIGNED DEFAULT 0`
Status	ENUM	`status ENUM('active', 'inactive', 'pending')`
Description	TEXT	`description TEXT`
URL	VARCHAR(2083)	`url VARCHAR(2083)`
Phone	VARCHAR(20)	`phone VARCHAR(20)`
ZIP/Postal Code	VARCHAR(10)	`postal_code VARCHAR(10)`
Created/Updated	TIMESTAMP	`created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`

Practice Exercise

💻

Design a User Profile Table

Create a table for user profiles with optimal data types for:

User ID (primary key)
Username (unique, 3-30 characters)
Email (unique)
Age
Bio (up to 500 characters)
Avatar URL
Account balance
Is verified (yes/no)
Join date
Last login time
Preferences (flexible JSON)

💡 Click for Solution

CREATE TABLE user_profiles (
    -- Primary key
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- String data
    username VARCHAR(30) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    bio TEXT,  -- Could also use VARCHAR(500)
    avatar_url VARCHAR(500),
    
    -- Numeric data
    age TINYINT UNSIGNED CHECK (age >= 13 AND age <= 150),
    account_balance DECIMAL(12,2) DEFAULT 0.00,
    
    -- Boolean
    is_verified BOOLEAN DEFAULT FALSE,
    
    -- Date/Time
    join_date DATE NOT NULL,
    last_login_at TIMESTAMP NULL,
    
    -- JSON for flexible data
    preferences JSON DEFAULT '{}',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_last_login (last_login_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

PHP WordPress Development

Course Progress