MEDIUMINT

Learning Focus

Use this lesson to understand MEDIUMINT with practical syntax and examples.

Example of mediumint

Certainly! The MEDIUMINT data type in MySQL is used to store medium-sized integers. It requires 3 bytes of storage and can hold values within the following ranges:

• Signed: -8,388,608 to 8,388,607
• Unsigned: 0 to 16,777,215

Example of using MEDIUMINT in a table:

Table: user_statistics

user_id (MEDIUMINT)	username	total_posts (MEDIUMINT)	total_likes (MEDIUMINT UNSIGNED)
100001	alice	250	5000
100002	bob	150	3000
100003	charlie	400	7000
100004	diana	100	2000
100005	edward	350	6000

SQL to create this table:

CREATE TABLE user_statistics (
    user_id MEDIUMINT PRIMARY KEY,
    username VARCHAR(50),
    total_posts MEDIUMINT,
    total_likes MEDIUMINT UNSIGNED
);

SQL to insert sample data:

INSERT INTO user_statistics (user_id, username, total_posts, total_likes) VALUES
(100001, 'alice', 250, 5000),
(100002, 'bob', 150, 3000),
(100003, 'charlie', 400, 7000),
(100004, 'diana', 100, 2000),
(100005, 'edward', 350, 6000);

MySQL MEDIUMINT Data Type: Complete Guide

What is MEDIUMINT?

The MEDIUMINT is a 3-byte (24-bit) integer data type in MySQL that provides a middle ground between SMALLINT and INT. It's less commonly used but can be optimal for specific use cases where storage efficiency matters.

Key Features

Feature	Details
Storage Size	3 bytes (24 bits)
Signed Range	-8,388,608 to 8,388,607
Unsigned Range	0 to 16,777,215
Synonyms	None (unique to MySQL)
Default Value	NULL (if not specified)

When to Use MEDIUMINT?

[OK] Best for:

• Storing numbers that exceed SMALLINT but don't need full INT range
• Specialized applications where 3-byte storage provides optimal space savings
• Data where values are consistently between 65,536 and 16.7 million

[X] Avoid for:

• General-purpose applications (INT is usually better)
• Very small numbers (use TINYINT or SMALLINT)
• When storage savings are negligible compared to INT

How to Define MEDIUMINT

Basic Syntax

column_name MEDIUMINT [OPTIONS]

Available Options

Option	Description	Example
UNSIGNED	Only allows 0 to 16,777,215	MEDIUMINT UNSIGNED
ZEROFILL	Pads with leading zeros (deprecated in MySQL 8.0+)	MEDIUMINT(8) ZEROFILL -> 00012345
AUTO_INCREMENT	Rarely used with MEDIUMINT	MEDIUMINT AUTO_INCREMENT

Practical Examples

Example 1: Website Visitor Counter

CREATE TABLE site_stats (
    day DATE PRIMARY KEY,
    visitors MEDIUMINT UNSIGNED  -- Supports up to 16.7 million visits/day
);

Example 2: Product Inventory for Large Warehouse

CREATE TABLE warehouse_inventory (
    product_id INT PRIMARY KEY,
    quantity MEDIUMINT UNSIGNED  -- For products with large stock quantities
);

Example 3: Specialized Sensor Data

CREATE TABLE sensor_readings (
    reading_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    value MEDIUMINT  -- For values that might be negative
);

MEDIUMINT vs Other Integer Types

Type	Storage	Signed Range	Unsigned Range	Best For
SMALLINT	2 bytes	-32K to 32K	0 to 65K	Medium-range numbers
MEDIUMINT	3 bytes	-8M to 8M	0 to 16M	Large-but-limited numbers
INT	4 bytes	-2B to 2B	0 to 4B	Most common integer storage
BIGINT	8 bytes	-9Q to 9Q	0 to 18Q	Extremely large numbers

Performance Considerations

• Storage Efficiency: Saves 25% space compared to INT (3 bytes vs 4 bytes)
• Index Performance: Slightly better than INT for large tables due to smaller size
• CPU Usage: Modern CPUs handle 32-bit operations more efficiently than 24-bit

Common Issues & Best Practices

** Potential Problems**

1. Obscurity: Many developers aren't familiar with MEDIUMINT
2. Limited Use Cases: Often not worth the complexity vs using INT
3. Portability: Not all database systems support this type

[OK] Best Practices

Yes Only use when you specifically need the 3-byte storage Yes Document why MEDIUMINT was chosen over INT Yes Consider future growth - will values exceed 16.7 million?

Summary

• MEDIUMINT is a 3-byte integer for specialized large-but-limited numbers
• Signed range: 8.3 million to 8.3 million
• Unsigned range: 0 to 16.7 million
• Best for: Specialized applications where storage optimization is critical

When to Choose MEDIUMINT Over INT

Consider MEDIUMINT when:

1. You have millions of rows
2. Values will never exceed 16.7 million
3. Storage savings are significant for your use case
4. You're working with legacy systems optimized for this type

For most modern applications, INT is preferable due to:

• Simplicity
• Better CPU optimization
• Future-proofing

Concept Map

flowchart LR
    A[Schema Context] --> B[MEDIUMINT]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products;
SELECT department, MAX(salary) FROM employees GROUP BY department;

What's Next

• Previous: SMALLINT - Review the previous lesson to reinforce context.
• Next: BIGINT - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.