Integer types (TINYINT, INT, BIGINT) in MySQL - Deep Dive

Overview - Integer types (TINYINT, INT, BIGINT)

What is it?

Integer types in MySQL are special data types used to store whole numbers without decimals. TINYINT, INT, and BIGINT are different sizes of integers that can hold numbers within specific ranges. They help the database save space and organize data efficiently by choosing the right size for the numbers you want to store.

Why it matters

Using the correct integer type saves storage space and improves database speed. If you use a type too large, you waste space; too small, and you risk errors or data loss. Without these types, databases would be slower and less efficient, making applications sluggish and costly to run.

Where it fits

Before learning integer types, you should understand basic data types and how databases store data. After mastering integer types, you can learn about other numeric types, indexing, and optimizing database performance.

Mental Model

Core Idea

Integer types are like different-sized containers that hold whole numbers, each designed to fit a specific range of values efficiently.

Think of it like...

Imagine you have boxes of different sizes to pack your belongings: a small box for tiny items, a medium box for regular items, and a big box for large items. Choosing the right box size saves space and keeps things organized.

┌─────────────┬───────────────┬───────────────┐
│ Type        │ Storage Size  │ Value Range   │
├─────────────┼───────────────┼───────────────┤
│ TINYINT     │ 1 byte       │ -128 to 127   │
│ INT         │ 4 bytes      │ -2,147,483,648 to 2,147,483,647 │
│ BIGINT      │ 8 bytes      │ -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 │
└─────────────┴───────────────┴───────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Integer Basics

Concept: Introduce what integers are and why databases use specific types for them.

Integers are whole numbers without fractions or decimals. Databases use integer types to store these numbers efficiently. Each integer type has a fixed size in bytes, which determines how big or small the numbers it can hold are.

Result

You know that integers are whole numbers and that different types store different ranges of these numbers.

Understanding that integers are stored in fixed-size containers helps you grasp why choosing the right type matters for storage and performance.

2

Foundation

Storage Size and Range Relationship

Concept: Explain how storage size in bytes relates to the range of numbers an integer type can hold.

Each byte has 8 bits, and each bit can be 0 or 1. The number of bits determines how many different numbers can be stored. For example, 1 byte (8 bits) can store 256 different values. Signed integers split this range between positive and negative numbers.

Result

You understand that more bytes mean a bigger range of numbers can be stored.

Knowing the link between bytes and number range helps you predict which integer type fits your data.

3

Intermediate

Signed vs Unsigned Integers

🤔Before reading on: Do you think unsigned integers can store negative numbers? Commit to your answer.

Concept: Introduce the difference between signed and unsigned integers and how it affects the range.

Signed integers can store both negative and positive numbers, splitting the range around zero. Unsigned integers only store zero and positive numbers, doubling the positive range but no negatives. For example, a signed TINYINT ranges from -128 to 127, while unsigned ranges from 0 to 255.

Result

You can choose signed or unsigned types based on whether you need negative numbers.

Understanding signed vs unsigned lets you optimize storage by expanding positive range when negatives aren't needed.

4

Intermediate

Choosing the Right Integer Type

🤔Before reading on: Would using BIGINT for small numbers always be a good idea? Commit to your answer.

Concept: Teach how to select the appropriate integer type based on the expected data range.

If your numbers are small, use TINYINT or INT to save space. Using BIGINT for small numbers wastes storage and can slow down queries. Always estimate the maximum and minimum values your data will have and pick the smallest type that fits.

Result

You can pick integer types that balance storage efficiency and data needs.

Knowing how to match data range to integer type prevents wasted space and improves database speed.

5

Advanced

Impact on Indexing and Performance

🤔Before reading on: Do you think integer size affects database indexing speed? Commit to your answer.

Concept: Explain how integer size influences indexing and query performance.

Smaller integer types use less space in indexes, making them faster to search and use less memory. Larger types like BIGINT consume more space and can slow down index scans. Choosing the right integer type improves overall database performance, especially on large tables.

Result

You understand that integer size affects not just storage but also speed.

Recognizing the performance impact of integer sizes helps you design faster, more efficient databases.

6

Expert

Unexpected Behavior with Overflow and Casting

🤔Before reading on: What happens if you insert a number too big for the integer type? Commit to your answer.

Concept: Reveal how MySQL handles numbers that exceed the integer type range and casting nuances.

If you insert a number larger than the maximum for the integer type, MySQL clips it to the max or min value without error by default, which can cause silent data corruption. Casting between types can also lead to unexpected results if not handled carefully. Using strict SQL modes can help catch these issues.

Result

You know the risks of overflow and how to prevent silent errors.

Understanding overflow behavior and casting pitfalls prevents subtle bugs and data loss in production.

Under the Hood

Internally, MySQL stores integers as binary numbers using a fixed number of bytes. Each byte has 8 bits, and the bits represent the number in binary form. Signed integers use one bit to indicate positive or negative, while unsigned use all bits for positive values. When you insert a number, MySQL converts it to binary and stores it in the allocated bytes. If the number is out of range, MySQL clips it or throws an error depending on settings.

Why designed this way?

Integer types were designed to balance storage efficiency and range. Early computers used fixed byte sizes for simplicity and speed. MySQL follows this tradition to ensure compatibility and performance. Alternatives like variable-length integers exist but add complexity and overhead, so fixed sizes remain standard for core integer types.

┌───────────────┐
│ Input Number  │
└──────┬────────┘
       │ Convert to binary
       ▼
┌───────────────┐
│ Binary Format │
│ (fixed bytes) │
└──────┬────────┘
       │ Store in database
       ▼
┌───────────────┐
│ Stored Integer│
│ (TINYINT/INT/ │
│  BIGINT bytes)│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does using BIGINT always improve safety for storing numbers? Commit yes or no.

Common Belief: Using BIGINT is always safer because it can store any number you might need.

Reality: BIGINT uses more storage and can slow down queries; it's only safer if you truly need its large range.

Why it matters: Overusing BIGINT wastes space and reduces performance, especially on large datasets.

Quick: Can unsigned integers store negative numbers? Commit yes or no.

Common Belief: Unsigned integers can store negative numbers because they just use a different format.

Reality: Unsigned integers cannot store negative numbers; they only store zero and positive values.

Why it matters: Using unsigned when negatives are needed causes incorrect data storage and logic errors.

Quick: Does MySQL always throw an error if you insert a number too big for the integer type? Commit yes or no.

Common Belief: MySQL will always give an error if a number is too big for the integer type.

Reality: By default, MySQL clips the number to the max or min allowed value without error unless strict mode is enabled.

Why it matters: Silent clipping can cause data corruption and hard-to-find bugs.

Quick: Is the storage size of an integer type affected by whether it is signed or unsigned? Commit yes or no.

Common Belief: Signed integers use more storage than unsigned because they store negative signs.

Reality: Signed and unsigned integers use the same storage size; only the range of values differs.

Why it matters: Misunderstanding this can lead to wrong assumptions about storage needs.

Expert Zone

1

Choosing integer types affects not only storage but also index size, which impacts query speed and memory usage.

2

MySQL's default behavior on overflow can silently corrupt data unless strict SQL modes are enabled, which many developers overlook.

3

Using unsigned integers can double the positive range but requires careful application logic to avoid negative number errors.

When NOT to use

Avoid using BIGINT when INT or TINYINT suffice to save space and improve performance. For very large or arbitrary precision numbers, use DECIMAL or string types instead. When you need fractional numbers, use FLOAT or DOUBLE rather than integers.

Production Patterns

In production, developers often use INT for IDs and counters, TINYINT for boolean flags or small enums, and BIGINT for large counters like user IDs in massive systems. They enable strict SQL modes to catch overflow errors and carefully choose signed vs unsigned based on business rules.

Connections

Data Compression

Integer types relate to data compression by minimizing storage size for numeric data.

Understanding integer sizes helps grasp how data compression algorithms reduce storage by exploiting fixed-size representations.

Computer Architecture

Integer types mirror how CPUs handle fixed-size binary numbers in registers and memory.

Knowing CPU integer sizes clarifies why databases use fixed byte sizes and how this affects performance.

Human Memory Limits

Choosing integer sizes is like managing human short-term memory capacity for numbers.

Recognizing limits in number storage helps design systems that avoid overload and errors, similar to cognitive load management.

Common Pitfalls

#1 Using BIGINT for all integer columns regardless of data size.

Wrong approach:

CREATE TABLE users (id BIGINT PRIMARY KEY, age BIGINT);

Correct approach:

CREATE TABLE users (id BIGINT PRIMARY KEY, age TINYINT);

Root cause:

Misunderstanding that bigger integer types always mean better safety, ignoring storage and performance costs.

#2 Inserting values outside the range of the integer type without enabling strict mode.

Wrong approach:

INSERT INTO products (stock) VALUES (1000); -- stock is TINYINT signed

Correct approach:

SET sql_mode = 'STRICT_ALL_TABLES'; INSERT INTO products (stock) VALUES (1000);

Root cause:

Not knowing MySQL silently clips out-of-range values by default, leading to silent data corruption.

#3 Using unsigned integers when negative values are possible.

Wrong approach:

CREATE TABLE transactions (amount UNSIGNED INT); -- amount can be negative

Correct approach:

CREATE TABLE transactions (amount INT);

Root cause:

Confusing unsigned integers as a way to store all numbers, ignoring negative value needs.

Key Takeaways

Integer types in MySQL store whole numbers using fixed byte sizes that determine their range and storage needs.

Choosing the right integer type balances storage efficiency, performance, and data correctness.

Signed integers store negative and positive numbers; unsigned store only zero and positive numbers, doubling positive range.

MySQL silently clips out-of-range integer values by default, so enabling strict mode is crucial to avoid data errors.

Understanding integer types deeply helps design faster, safer, and more efficient databases.