MySQL INT Data Type Explained: Max Value, Range, Storage Size & Deprecation Changes (MySQL 8+)

1. Introduction

MySQL is a popular relational database management system widely used in many web applications and database systems. Among its data types, the INT type is one of the most commonly used for handling numeric values. This article provides a detailed explanation of the MySQL INT type. In particular, we will explore the maximum values supported by the INT type and how to use it efficiently. By reading this article, you will gain the knowledge necessary to properly utilize the INT type in MySQL.

2. Basic Specifications of the INT Type

Maximum and Minimum Values of INT

The MySQL INT type uses 4 bytes (32 bits) of storage, and the range of values it can store is as follows:

• Signed (SIGNED):
• Minimum value: -2,147,483,648
• Maximum value: 2,147,483,647
• Unsigned (UNSIGNED):
• Minimum value: 0
• Maximum value: 4,294,967,295

Storage Size of INT

The INT type always uses 4 bytes of storage. This size remains constant regardless of the value stored. Therefore, if you do not need to handle a very large numeric range, it is more efficient to consider smaller data types (for example, TINYINT or SMALLINT).

Use Cases for INT

The INT type is commonly used in the following scenarios:

• Auto-increment values (e.g., user IDs, order numbers)
• Integer data used in calculations or statistical processing (e.g., inventory counts, click counts)
• Data represented within a fixed range (e.g., age or test scores)

In these use cases, it is important to consider the required numeric range and memory efficiency.

3. What Does M Mean in INT(M)?

What Is Display Width (M)?

In MySQL, the M in INT(M) represents the “display width.” It does not affect the actual numeric value stored in the database; instead, it specifies how the value is formatted when displayed. For example, if defined as INT(5), the number will be displayed with a width of 5 digits.

However, the display width M only has meaning in the following case:

• When the ZEROFILL option is enabled
• Example: With INT(5) ZEROFILL, if the value is 123, it will be displayed as 00123.

Important Notes About ZEROFILL

When using the ZEROFILL option, the following characteristics apply:

1. Leading zeros are automatically added on the left.
2. The UNSIGNED attribute is automatically applied.

Therefore, if you need to store negative values, you cannot use ZEROFILL.

Clearing Up Common Misunderstandings

Many beginners mistakenly believe that M limits the maximum storable value. However, M only affects display formatting and has absolutely no impact on the actual storage range.

4. Comparison with Other Integer Types

Types of Integer Data Types and Their Ranges

MySQL provides the following integer types:

Selection Criteria

Use the following guidelines when selecting an appropriate data type in database design:

• If the range is small: Use TINYINT or SMALLINT to save memory.
• If an extremely large range is required: Use BIGINT.
• If general-purpose usage is desired: INT is usually optimal.

5. Changes Since MySQL 8.0.17

Deprecation of Display Width (M)

Starting from MySQL 8.0.17, the “display width (M)” for integer types has been deprecated. This change was introduced together with the deprecation of the ZEROFILL option, and it may be removed in future MySQL versions.

The reasons for deprecating display width are as follows:

1. Caused misunderstandings:

• Many users misunderstood that M affected the maximum storable value or number of digits.

1. Limited practical usage:

• Since M only controlled display formatting, formatting is now commonly handled on the application side, reducing its necessity.

Deprecation of ZEROFILL

The ZEROFILL option was also deprecated in the same version. Although ZEROFILL was convenient for padding numbers with leading zeros, it is no longer recommended for the following reasons:

• Widespread alternative approaches:
• Zero-padding can now be easily handled in the application or UI layer.
• Avoiding confusion:
• The automatic application of the UNSIGNED attribute when using ZEROFILL caused confusion for beginner users.

How to Adapt to the Deprecation

In response to these deprecations, consider the following approaches in database design:

1. Handle formatting in the application layer:

• Perform zero-padding and formatting in the application or presentation layer.
• Example: Implement zero-padding using PHP or JavaScript.

1. Adopt a design that does not rely on display width:

• Define the INT type without specifying M and focus on data accuracy rather than display formatting.

6. Practical FAQ Section

Frequently Asked Questions

Q1. What happens if I store a value exceeding the maximum of the INT type?
A. In MySQL, attempting to store a value outside the range of the INT type will result in an error. You must either choose a value within the supported range or switch to a data type with a larger range (for example, BIGINT).

Q2. What is the difference between INT and BIGINT?
A. The BIGINT type uses twice the storage of INT (8 bytes) and provides a much wider numeric range. For example, the signed range of BIGINT exceeds ±9 quintillion, making it suitable for handling very large datasets.

Q3. After ZEROFILL is deprecated, how should I implement zero-padded display?
A. It is recommended to handle zero-padding in the application layer. For example, in PHP you can use the str_pad() function, and in JavaScript you can use the padStart() method to implement zero-padding.

Q4. How should I choose between INT and other integer types?
A. Select the type based on the data range. Small numeric values (for example, age or scores) are suitable for TINYINT, medium-scale data (for example, user IDs) are suitable for INT, and very large values (for example, financial transaction data) are suitable for BIGINT.

7. Conclusion

In this article, we provided a detailed explanation of the MySQL INT data type. The key points are as follows:

• Understanding the differences between signed and unsigned maximum and minimum values of the INT type forms the foundation of proper data design.
• It is important to understand alternative approaches to the deprecated “display width (M)” and “ZEROFILL” options and design databases that can adapt to future MySQL updates.
• Select the appropriate integer type based on data characteristics to build an efficient and maintainable database.

Use this article as a reference to optimize your use of the INT type and further improve efficiency in MySQL database design.