MySQL Data Types Guide

Introduction

Choosing the correct data type is crucial for database performance, storage efficiency, and data integrity. This guide covers all major MySQL data types with practical examples.

Categories of Data Types

1. Numeric Types: Numbers (integers, decimals)
2. String Types: Text and characters
3. Date and Time Types: Temporal data
4. Binary Types: Binary data (files, images)
5. JSON Type: JSON documents
6. Spatial Types: Geographic data

Numeric Data Types

Integer Types

Type	Storage	Range (Signed)	Range (Unsigned)	Use Case
TINYINT	1 byte	-128 to 127	0 to 255	Small numbers, flags, ages
SMALLINT	2 bytes	-32,768 to 32,767	0 to 65,535	Counts, small quantities
MEDIUMINT	3 bytes	-8,388,608 to 8,388,607	0 to 16,777,215	Medium range numbers
INT	4 bytes	-2,147,483,648 to 2,147,483,647	0 to 4,294,967,295	Most common integer use
BIGINT	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	0 to 18,446,744,073,709,551,615	Very large numbers

Examples

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED,
    login_count INT DEFAULT 0,
    points BIGINT DEFAULT 0
);

Decimal Types

Type	Description	Use Case
DECIMAL(M,D)	Exact precision	Money, prices, precise calculations
FLOAT	Approximate, 4 bytes	Scientific calculations
DOUBLE	Approximate, 8 bytes	High-precision scientific data

Examples

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2),  -- 10 digits total, 2 after decimal
    weight FLOAT,
    scientific_value DOUBLE
);

Important: Use DECIMAL for money, never FLOAT or DOUBLE!

Boolean Type

-- BOOL or BOOLEAN (actually TINYINT(1))
CREATE TABLE settings (
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOL DEFAULT FALSE
);

-- Values: TRUE (1), FALSE (0)

String Data Types

Character Types

Type	Max Size	Storage	Use Case
CHAR(M)	255 chars	Fixed	Fixed-length strings (country codes, phone)
VARCHAR(M)	65,535 chars	Variable	Variable-length strings (names, emails)
TINYTEXT	255 chars	Variable	Very short text
TEXT	65,535 chars	Variable	Articles, descriptions
MEDIUMTEXT	16,777,215 chars	Variable	Large documents
LONGTEXT	4,294,967,295 chars	Variable	Very large documents

Examples

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    country_code CHAR(2),           -- Fixed: 'US', 'UK'
    title VARCHAR(200) NOT NULL,    -- Variable up to 200
    excerpt TEXT,                   -- Short text
    content LONGTEXT,              -- Full article content
    tags VARCHAR(500)
);

When to Use CHAR vs VARCHAR

Use CHAR when:

• Data is fixed length (e.g., country codes: ‘US’, ‘UK’)
• All entries are nearly the same length
• Slight performance benefit

Use VARCHAR when:

• Length varies significantly
• Want to save storage space
• Most common choice

String Character Sets

CREATE TABLE multilingual_content (
    content_id INT PRIMARY KEY,
    title VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    content TEXT CHARACTER SET utf8mb4
);

Date and Time Types

Type	Format	Range	Use Case
DATE	YYYY-MM-DD	1000-01-01 to 9999-12-31	Birth dates, event dates
TIME	HH:MM:SS	-838:59:59 to 838:59:59	Duration, time of day
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 to 9999-12-31	Specific date and time
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 to 2038-01-19	Auto-update timestamps
YEAR	YYYY	1901 to 2155	Year only

Examples

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_date DATE,                                    -- Just the date
    event_time TIME,                                    -- Just the time
    event_datetime DATETIME,                            -- Specific moment
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     -- Auto set on insert
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
               ON UPDATE CURRENT_TIMESTAMP              -- Auto update
);

DATETIME vs TIMESTAMP

Use DATETIME when:

• Need dates beyond 2038
• Time zone not important
• Need wider range

Use TIMESTAMP when:

• Want automatic updates
• Need UTC conversion
• Tracking creation/modification times

Working with Dates

-- Insert dates
INSERT INTO events (event_date, event_datetime)
VALUES ('2024-12-31', '2024-12-31 23:59:59');

-- Current date/time functions
INSERT INTO events (event_datetime) VALUES (NOW());
INSERT INTO events (event_date) VALUES (CURDATE());
INSERT INTO events (event_time) VALUES (CURTIME());

Binary Data Types

Type	Max Size	Use Case
BINARY(M)	255 bytes	Fixed binary data
VARBINARY(M)	65,535 bytes	Variable binary data
TINYBLOB	255 bytes	Tiny binary objects
BLOB	65,535 bytes	Images, files
MEDIUMBLOB	16,777,215 bytes	Larger files
LONGBLOB	4,294,967,295 bytes	Very large files

Example

CREATE TABLE files (
    file_id INT PRIMARY KEY,
    file_name VARCHAR(255),
    file_data MEDIUMBLOB,
    file_hash BINARY(32)  -- SHA-256 hash
);

Note: Storing files in database is generally not recommended. Consider storing file paths and keeping files on filesystem.

ENUM and SET Types

ENUM Type

Stores one value from a predefined list:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
        DEFAULT 'pending'
);

-- Insert
INSERT INTO orders (order_id, status) VALUES (1, 'processing');

SET Type

Stores zero or more values from a predefined list:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    permissions SET('read', 'write', 'delete', 'admin')
);

-- Insert multiple values
INSERT INTO users (user_id, permissions) VALUES (1, 'read,write');

JSON Data Type

Store and query JSON documents:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

-- Insert JSON
INSERT INTO products (product_id, name, attributes) VALUES (
    1,
    'Laptop',
    '{"brand": "Dell", "ram": "16GB", "storage": "512GB SSD"}'
);

-- Query JSON
SELECT
    name,
    attributes->>'$.brand' AS brand,
    attributes->>'$.ram' AS ram
FROM products;

Data Type Selection Guide

For IDs

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
-- or for distributed systems
id VARCHAR(36)  -- UUID

For Names and Titles

first_name VARCHAR(50)
last_name VARCHAR(50)
title VARCHAR(200)

For Email and URLs

email VARCHAR(255) UNIQUE
website VARCHAR(500)

For Descriptions

short_description VARCHAR(500)
description TEXT
long_description MEDIUMTEXT

For Money

price DECIMAL(10, 2)
total_amount DECIMAL(12, 2)

For Quantities

quantity INT UNSIGNED DEFAULT 0
stock_count SMALLINT UNSIGNED

For Percentages

discount_percent DECIMAL(5, 2)  -- 0.00 to 999.99

For Dates

birth_date DATE
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

For Status/Flags

is_active BOOLEAN DEFAULT TRUE
status ENUM('active', 'inactive', 'pending')

Best Practices

1. Use Appropriate Types

-- Bad
age VARCHAR(10)
price VARCHAR(20)

-- Good
age TINYINT UNSIGNED
price DECIMAL(10, 2)

2. Use Smallest Sufficient Type

-- If values never exceed 100
quantity TINYINT UNSIGNED  -- not INT

3. Use UNSIGNED for Non-Negative Numbers

user_id INT UNSIGNED
age TINYINT UNSIGNED
quantity INT UNSIGNED

4. Use ENUM for Fixed Options

-- Instead of VARCHAR
gender ENUM('male', 'female', 'other')
status ENUM('active', 'inactive', 'suspended')

5. Use VARCHAR, Not CHAR

-- Unless truly fixed length
name VARCHAR(100)  -- not CHAR(100)

6. Always Use utf8mb4

title VARCHAR(200) CHARACTER SET utf8mb4

7. Use DECIMAL for Money

-- Never FLOAT or DOUBLE for money
price DECIMAL(10, 2)

Common Mistakes to Avoid

1. Wrong Type for Money

-- Wrong
price FLOAT

-- Correct
price DECIMAL(10, 2)

2. VARCHAR Too Large

-- Wasteful
name VARCHAR(1000)

-- Better
name VARCHAR(100)

3. Using TEXT for Short Strings

-- Inefficient
status TEXT

-- Better
status VARCHAR(20)
-- or
status ENUM('active', 'inactive')

4. Not Using UNSIGNED

-- Wastes range
age INT

-- Better
age TINYINT UNSIGNED

Practice Exercises

Exercise 1: Choose Data Types

Design appropriate data types for a user registration table including: id, username, email, password, age, bio, registration_date, is_verified.

Exercise 2: E-commerce Products

Create a products table with appropriate types for: product_id, name, description, price, discount_percentage, stock_quantity, weight, dimensions (JSON), created_at.

Exercise 3: Optimize Data Types

Review and optimize these column definitions:

CREATE TABLE bad_example (
    id VARCHAR(50),
    age VARCHAR(10),
    price FLOAT,
    email TEXT,
    status VARCHAR(255),
    created_at VARCHAR(50)
);

Next Steps

• SQL Queries
• Tables and Schemas
• Database Design Best Practices

Additional Resources

• MySQL Data Types Documentation
• Choosing the Right Data Type
• Data Type Storage Requirements