MySQL Data Types

Introduction

When designing a database in MySQL, choosing the right data type for each column is one of the most critical decisions you'll make. Data types define what kind of values can be stored in a column, how much space they occupy, and what operations can be performed on them. Using appropriate data types not only ensures data integrity but also optimizes storage space and query performance.

In this guide, we'll explore the various data types available in MySQL, when to use them, and the best practices for efficient database design.

Numeric Data Types

MySQL offers several numeric data types to store integer and floating-point values.

Integer Types

Integer types are used to store whole numbers without decimal places.

Type	Storage (Bytes)	Minimum Value	Maximum Value
TINYINT	1	-128	127
SMALLINT	2	-32,768	32,767
MEDIUMINT	3	-8,388,608	8,388,607
INT	4	-2,147,483,648	2,147,483,647
BIGINT	8	-9,223,372,036,854,775,808	9,223,372,036,854,775,807

You can also define these types as UNSIGNED to store only non-negative values, which doubles the maximum positive range.

Let's create a table with different integer types:

sql

CREATE TABLE product_inventory (
    product_id INT UNSIGNED PRIMARY KEY,
    small_quantity TINYINT,
    medium_quantity SMALLINT,
    large_quantity MEDIUMINT,
    total_quantity INT,
    lifetime_sales BIGINT
);

When to use each:

• TINYINT: For small ranges like flags (0/1) or ratings (1-5)
• SMALLINT: For moderate ranges like age, quantity of items
• INT: For standard IDs and counts
• BIGINT: For very large numbers like view counts on popular content

Floating-Point and Decimal Types

For numbers with decimal places, MySQL provides:

Type	Description	Precision	Storage
FLOAT	Single-precision floating-point	~7 decimal digits	4 bytes
DOUBLE	Double-precision floating-point	~15 decimal digits	8 bytes
DECIMAL(M,D)	Fixed-point number where M is total digits and D is decimal places	Depends on M,D values	Varies

Example:

sql

CREATE TABLE product_pricing (
    product_id INT PRIMARY KEY,
    weight FLOAT,
    price DECIMAL(10,2),
    exchange_rate DOUBLE
);

sql

INSERT INTO product_pricing VALUES 
(1, 0.453, 19.99, 1.34567890123456);

SELECT * FROM product_pricing;

Output:

+-----------+--------+--------+----------------------+
| product_id| weight | price  | exchange_rate        |
+-----------+--------+--------+----------------------+
| 1         | 0.453  | 19.99  | 1.34567890123456     |
+-----------+--------+--------+----------------------+

When to use each:

• FLOAT/DOUBLE: For scientific calculations where exact precision isn't critical
• DECIMAL: For financial data where exact precision is required (money, percentages)

caution

Never use FLOAT or DOUBLE for storing currency or any values requiring exact decimal representation due to potential rounding errors!

String Data Types

MySQL provides several options for storing text and binary data.

Character String Types

Type	Maximum Length	Storage
CHAR(M)	Fixed-length strings up to M characters	M bytes
VARCHAR(M)	Variable-length strings up to M characters	Actual length + 1 or 2 bytes
TEXT	Variable-length strings up to 65,535 characters	Actual length + 2 bytes
MEDIUMTEXT	Up to 16,777,215 characters	Actual length + 3 bytes
LONGTEXT	Up to 4,294,967,295 characters	Actual length + 4 bytes

Example usage:

sql

CREATE TABLE user_profile (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password_hash CHAR(64) NOT NULL,
    short_bio VARCHAR(255),
    full_bio TEXT,
    blog_post MEDIUMTEXT
);

When to use each:

• CHAR: For fixed-length strings like state codes, postal codes, or format-specific data
• VARCHAR: For variable-length strings like names, addresses, and short descriptions
• TEXT/MEDIUMTEXT/LONGTEXT: For long textual content like blog posts, articles, or product descriptions

Binary String Types

Type	Maximum Length	Use case
BINARY(M)	Fixed-length binary strings	Fixed-length binary data
VARBINARY(M)	Variable-length binary strings	Variable-length binary data
BLOB	Variable-length binary data up to 65,535 bytes	Binary objects
MEDIUMBLOB	Up to 16,777,215 bytes	Larger binary objects
LONGBLOB	Up to 4,294,967,295 bytes	Very large binary objects

Example:

sql

CREATE TABLE media_files (
    file_id INT PRIMARY KEY,
    file_name VARCHAR(255),
    file_thumbnail BLOB,
    file_content MEDIUMBLOB
);

tip

While you can store binary files like images in BLOB columns, it's often better to store them in the filesystem and just store the file path in the database for better performance.

Date and Time Data Types

MySQL provides several data types for storing temporal information:

Type	Format	Range	Storage
DATE	YYYY-MM-DD	1000-01-01 to 9999-12-31	3 bytes
TIME	HH:MM:SS	-838:59:59 to 838:59:59	3 bytes
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 to 9999-12-31 23:59:59	8 bytes
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC	4 bytes
YEAR	YYYY	1901 to 2155	1 byte

Example usage:

sql

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    end_time TIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO events (event_id, event_name, event_date, start_time, end_time)
VALUES (1, 'MySQL Workshop', '2023-11-15', '09:00:00', '17:00:00');

SELECT * FROM events;

Output:

+----------+---------------+------------+------------+----------+---------------------+---------------------+
| event_id | event_name    | event_date | start_time | end_time | created_at          | last_updated        |
+----------+---------------+------------+------------+----------+---------------------+---------------------+
| 1        | MySQL Workshop| 2023-11-15 | 09:00:00   | 17:00:00 | 2023-10-20 14:30:00 | 2023-10-20 14:30:00 |
+----------+---------------+------------+------------+----------+---------------------+---------------------+

Key differences:

• TIMESTAMP automatically converts to UTC when stored and back to your current time zone when retrieved.
• DATETIME stores the actual value as entered without time zone conversion.
• TIMESTAMP has a more limited range but uses less storage.

Special Data Types

ENUM Type

ENUM allows you to define a list of permissible values for a column. Only those values can be stored in that column.

sql

CREATE TABLE task_status (
    task_id INT PRIMARY KEY,
    status ENUM('pending', 'in_progress', 'completed', 'canceled') 
        DEFAULT 'pending'
);

INSERT INTO task_status (task_id, status) VALUES (1, 'in_progress');
-- This works

INSERT INTO task_status (task_id, status) VALUES (2, 'delayed');
-- This fails because 'delayed' is not in the ENUM list

SET Type

SET is similar to ENUM but allows multiple values to be selected from a predefined list.

sql

CREATE TABLE product_features (
    product_id INT PRIMARY KEY,
    features SET('waterproof', 'bluetooth', 'wifi', 'gps', 'touchscreen')
);

INSERT INTO product_features VALUES (1, 'waterproof,bluetooth,wifi');
SELECT * FROM product_features;

Output:

+------------+---------------------------+
| product_id | features                  |
+------------+---------------------------+
| 1          | waterproof,bluetooth,wifi |
+------------+---------------------------+

JSON Type

MySQL 5.7 and later support native JSON data type, which is useful for storing JSON documents:

sql

CREATE TABLE user_preferences (
    user_id INT PRIMARY KEY,
    preferences JSON
);

INSERT INTO user_preferences VALUES (
    1, 
    '{"theme": "dark", "notifications": true, "sidebar": "left"}'
);

-- You can extract JSON values using JSON_EXTRACT or the -> operator
SELECT user_id, JSON_EXTRACT(preferences, '$.theme') AS theme
FROM user_preferences;

-- Or using the -> operator
SELECT user_id, preferences->'$.theme' AS theme
FROM user_preferences;

Output:

+---------+--------+
| user_id | theme  |
+---------+--------+
| 1       | "dark" |
+---------+--------+

Spatial Data Types

MySQL supports Spatial Data Types for storing geographic information:

• GEOMETRY: Can store any type of geometric value
• POINT: Represents a single location in coordinate space
• LINESTRING: A curve with linear interpolation between points
• POLYGON: A planar surface
• MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: Collections of the above types

Example of using spatial data:

sql

CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(100),
    location POINT
);

INSERT INTO stores VALUES (
    1, 
    'Downtown Store', 
    ST_GeomFromText('POINT(40.7128 -74.0060)')
);

-- Find stores within a certain distance
SELECT store_name, 
       ST_Distance_Sphere(
           location, 
           ST_GeomFromText('POINT(40.7300 -74.0200)')
       ) AS distance_in_meters 
FROM stores
WHERE ST_Distance_Sphere(
    location, 
    ST_GeomFromText('POINT(40.7300 -74.0200)')
) < 5000;

Choosing the Right Data Type

When deciding which data type to use, consider these factors:

1. Storage requirements: Choose the smallest data type that can safely handle your data.
2. Range requirements: Ensure the data type can accommodate both current and future data.
3. Precision requirements: For numeric data, determine if you need exact precision.
4. Performance impact: Some data types are more efficient for indexing and searching.
5. Compatibility: Consider how the data might be used in other systems.

Best Practices

• Use INT for IDs, especially with AUTO_INCREMENT.
• Use DECIMAL for currency and financial data, never FLOAT or DOUBLE.
• Use VARCHAR for variable-length strings, and CHAR only when the length is fixed.
• Use TIMESTAMP for tracking record changes, and DATETIME for user-specified dates.
• Consider using ENUM for columns with a small, fixed set of values.
• Avoid using TEXT or BLOB types in tables with frequent queries, as they can't be fully indexed.

Real-World Examples

E-commerce Product Table

sql

CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku CHAR(10) NOT NULL UNIQUE,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    weight DECIMAL(5,2),
    stock_quantity SMALLINT UNSIGNED DEFAULT 0,
    category ENUM('Electronics', 'Clothing', 'Food', 'Books', 'Other') NOT NULL,
    tags SET('featured', 'sale', 'new', 'bestseller'),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    product_specs JSON
);

-- Insert sample data
INSERT INTO products 
(sku, product_name, description, price, weight, stock_quantity, category, tags, product_specs)
VALUES
('BOOK123456', 'MySQL Database Design', 'Comprehensive guide to MySQL database design', 
 29.99, 0.75, 120, 'Books', 'featured,new', 
 '{"pages": 450, "language": "English", "format": ["hardcover", "ebook"]}');

User Activity Tracking System

sql

CREATE TABLE user_activities (
    activity_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    activity_type ENUM('login', 'logout', 'purchase', 'page_view', 'search') NOT NULL,
    activity_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),  -- Can store IPv6
    user_agent VARCHAR(255),
    page_url VARCHAR(255),
    search_query VARCHAR(100),
    session_id CHAR(32),
    additional_data JSON,
    INDEX idx_user_activity (user_id, activity_type, activity_time)
);

-- Insert sample data
INSERT INTO user_activities 
(user_id, activity_type, ip_address, user_agent, page_url)
VALUES
(1001, 'page_view', '192.168.1.100', 
 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
 'https://example.com/products/mysql-book');

Performance Considerations

The data types you choose can significantly impact your database performance:

1. Smaller is better: Use the smallest data type that meets your requirements to save disk space, memory, and CPU cycles.

2. Alignment: MySQL performs better when data types align with CPU architecture. For example, INT (4 bytes) aligns well with 32-bit CPUs.

3. Indexing efficiency: Shorter data types make indexes smaller and faster to search.

4. JOIN performance: Joins work faster when the joining columns use the same data type and size.

5. Storage engines: Different MySQL storage engines may handle data types differently.

Common Pitfalls to Avoid

1. Over-allocation: Don't use VARCHAR(255) just because it's a common default. Size columns appropriately.

2. Using TEXT/BLOB unnecessarily: These types can't be fully indexed and slow down your queries.

3. Mixed character sets: Be consistent with character sets to avoid conversion issues.

4. Ignoring storage implications: ENUM/SET can be more efficient for storing a predefined list of values.

5. Inappropriate numeric types: Using INT for ZIP codes can cause leading zeros to disappear.

Summary

Choosing the appropriate MySQL data types is essential for database efficiency and data integrity. We've covered:

• Numeric types for storing integers and decimals
• String types for text and binary data
• Date and time types for temporal information
• Special types like ENUM, SET, and JSON
• Spatial data types for geographic information

Remember that the right data type balances storage efficiency, performance, and data integrity. Always consider your specific requirements when designing your database schema.

Exercises

1. Design a table schema for a blog platform with posts, comments, and user information.
2. Convert a table using all VARCHAR columns to use the most appropriate data types.
3. Create a table to store product inventory with appropriate data types for each field.
4. Compare the storage requirements for storing phone numbers as INT, VARCHAR, and CHAR.
5. Create a database schema for a simple social media platform, paying special attention to data types.

Additional Resources

• MySQL Official Documentation on Data Types
• MySQL Performance Optimization
• MySQL Workbench for Database Design

By understanding and properly implementing MySQL data types, you'll create more efficient, robust, and performant database systems.