Introduction

MySQL, a popular open-source relational database management system, offers various data types that help tailor databases to suit specific needs. Among these types are integers, which are fundamental for storing whole numbers, evaluating counts, identifiers, and distinctions between objects. It's essential to choose the correct integer type to optimize storage and performance. In this article, we'll explore two integer types: INT(20) and BIGINT(20). Although they might seem similar at first glance, their differences are crucial for database design.

Understanding Data Types

Integer Types in MySQL

MySQL offers several integer types, each designed with specific storage size and usage in mind:

1. TINYINT: Occupies 1 byte.
2. SMALLINT: Occupies 2 bytes.
3. MEDIUMINT: Occupies 3 bytes.
4. INT: Occupies 4 bytes.
5. BIGINT: Occupies 8 bytes.

Each type can be assigned as UNSIGNED, which prevents the storage of negative numbers, effectively doubling the positive range.

Display Width

The numbers in parentheses, such as the 20 in INT(20) or BIGINT(20), represent the display width. This is a common misconception area; it doesn't affect storage size or range but influences the display of values, often when paired with the ZEROFILL attribute. For instance, and most importantly, INT(20) does not mean it can store numbers larger than usual INT can handle. When using ZEROFILL, numbers are padded with zeros up to the specified width during display.

Comparing INT and BIGINT

Storage and Range

When considering which one to use, it's imperative to understand their storage capacities and ranges:

• INT: Uses 4 bytes of storage.
  • Range for SIGNED: -2,147,483,648 to 2,147,483,647
  • Range for UNSIGNED: 0 to 4,294,967,295
• BIGINT: Uses 8 bytes of storage.
  • Range for SIGNED: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  • Range for UNSIGNED: 0 to 18,446,744,073,709,551,615

A quick decision point can be understood like this: if you anticipate storing small whole numbers, INT would be sufficient. However, for massive numbers, especially when considering, for example, IDs for globally unique identifiers, BIGINT is a better choice.

Usage and Performance Impacts

• INT is more than adequate for most typical applications needing whole numbers, like storing age, counts, or small identifiers.
• BIGINT is necessary when handling exceptionally large records, like user IDs in massive systems like social media platforms with billions of entities.

The primary trade-off is storage. BIGINT consumes more storage—twice as much as INT. This disparity is vital for high-volume databases where efficient use of storage can affect performance and cost.

A Practical Example

Imagine an application to track worldwide registrations of users, and we employ user IDs:

1-- Sample usage of INT
2CREATE TABLE Users(
3    user_id INT(20) UNSIGNED ZEROFILL,   -- user_id is padded with 0s up to 20 digits when displayed
4    user_name VARCHAR(255)
5);
6
7-- Sample usage of BIGINT
8CREATE TABLE BigUsers(
9    user_id BIGINT(20) UNSIGNED ZEROFILL,  -- Suitable for extremely large numbers
10    user_name VARCHAR(255)
11);

In the above structure, even with fields designated as INT(20) or BIGINT(20), the actual storage is 4 bytes and 8 bytes, respectively. The ZEROFILL attribute is utilized to demonstrate the padding when numbers are displayed, filling them up to 20 digits.

Table Summary

Conclusion

When designing a MySQL database, understanding the differences between INT and BIGINT helps streamline data storage solutions. It boils down to predicting the data range you'll be handling and balancing storage efficiency. Although the choice of integer-type data might seem minute in the engineering process, carefully selecting between INT and BIGINT can lead to optimized performances and well-fitted storage solutions for scalable applications.