MySQL INT: Max Values, Best Use & Signed vs Unsigned

1 1. Introduction
2 2. INT Type Basic Specification
3 3. What the M in INT(M) means
4 4. Comparison with Other Integer Types
- 4.1 Types and Ranges of Integer Types
- 4.2 Selection Criteria
5 5. Changes in MySQL 8.0.17 and later
6 6. Practical FAQ Section
- 6.1 Frequently Asked Questions and Answers
7 7. Summary

1. Introduction

MySQL is a popular relational database management system used in many web applications and database systems. Among its data types, the “INT” type is one of the most commonly used for handling numbers. This article provides an in‑depth look at MySQL’s INT type. In particular, we dive into the maximum values the INT type can hold and how to use it efficiently. By reading this article, you’ll gain the knowledge needed to master the INT type in MySQL.

2. INT Type Basic Specification

Maximum and Minimum Values of INT Type

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

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

INT Type Storage Size

INT type always uses a fixed 4-byte storage. This is constant regardless of the size of the stored value. Therefore, if you don’t need to handle a very large range, it is more efficient to consider smaller data types (e.g., TINYINT or SMALLINT).

INT Type Use Cases

INT type is commonly used in the following scenarios:

Auto-increment values (e.g., user IDs, order numbers)
Integer data for calculations or statistical processing (e.g., inventory counts, click counts)
Representing data within a fixed range (e.g., ages or scores)

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

3. What the M in INT(M) means

What is display width (M)?

In MySQL’s INT(M), M represents the “display width”. It does not affect the actual numeric value stored in the database, but specifies how the number is formatted when displayed. For example, if defined as INT(5), the number is displayed with five digits. However, the display width M only matters in the following case:

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

Notes on ZEROFILL

When using the ZEROFILL option, the following characteristics apply:

Zeros are padded on the left.
The UNSIGNED attribute is automatically applied.

Therefore, ZEROFILL cannot be used if you need to handle negative values.

Clearing up misconceptions

Many beginners mistakenly think that M limits the maximum storable value, but M only affects the display format and has no impact on the range of values that can be stored.

4. Comparison with Other Integer Types

Types and Ranges of Integer Types

MySQL provides the following integer types:

Type Name	Bytes	Signed Range	Unsigned Range
TINYINT	1 byte	-128 ～ 127	0 ～ 255
SMALLINT	2 bytes	-32,768 ～ 32,767	0 ～ 65,535
MEDIUMINT	3 bytes	-8,388,608 ～ 8,388,607	0 ～ 16,777,215
INT	4 bytes	-2,147,483,648 ～ 2,147,483,647	0 ～ 4,294,967,295
BIGINT	8 bytes	-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807	0 ～ 18,446,744,073,709,551,615

Selection Criteria

The following criteria help you choose the appropriate type when designing a database:

When the range is small: TINYINT or SMALLINT to save memory.
When a very large range is needed: use BIGINT.
When you need general-purpose: INT is optimal.

5. Changes in MySQL 8.0.17 and later

Deprecation of display width specification (M)

Starting with MySQL 8.0.17, the display width specification (M) for integer types has been deprecated. This change was introduced together with the deprecation of the ZEROFILL option and may be removed in future MySQL versions. The reasons for deprecating the display width specification are as follows:

Misunderstanding:

Many users mistakenly believed that “M” affects the maximum value or number of digits that can be stored.

Limited practical use:

Since “M” only serves as a display format, it has become common to handle formatting in the application layer, reducing its necessity.

Deprecation of ZEROFILL

The ZEROFILL option was also deprecated in the same version. ZEROFILL was a convenient feature for zero‑padding the display width, but it is no longer recommended for the following reasons:

Widespread alternative methods:
Methods for zero‑padding in the application or UI layer have become common.
Avoiding confusion:
When ZEROFILL is used, UNSIGNED is automatically applied, which has confused novice users.

Strategies for handling deprecation

With the deprecation, the following actions should be considered in database design:

Perform formatting in the application:

Implement zero‑padding and formatting of numbers in the application or presentation layer.
Example: implement zero‑padding in PHP or JavaScript.

Adopt designs that do not require display width:

When defining INT types, omit M and focus on the accuracy of the data itself.

6. Practical FAQ Section

Frequently Asked Questions and Answers

Q1. What happens if you store a value that exceeds the maximum for the INT type? A. In MySQL, attempting to store a value that exceeds the range of the INT type results in an error. You need to choose a value within the range or switch to a data type with a larger range (e.g., BIGINT). Q2. What is the difference between the INT and BIGINT types? A. BIGINT uses twice the storage of INT (8 bytes) and offers a much larger range. For example, the signed range of BIGINT exceeds ±9 quintillion, making it suitable for handling massive data. Q3. How can zero-padded display be achieved after ZEROFILL was deprecated? A. It is recommended to perform zero-padding at the application layer. For example, in PHP you can use the str_pad() function, and in JavaScript you can use the padStart() method to achieve zero-padding. Q4. How should you choose between INT and other integer types? A. Choose the type based on the data range. Small numbers (e.g., age or scores) use TINYINT, medium-sized data (e.g., user IDs) use INT, and very large values (e.g., financial transaction data) are best suited for BIGINT.

7. Summary

In this article, we provided an in‑depth explanation of MySQL’s INT type. Here are the key points:

Understanding the differences in maximum and minimum values between signed and unsigned INT types forms the foundation for proper data design.
It’s important to be aware of the alternatives to the deprecated display width (M) and ZEROFILL, and to aim for database designs that can accommodate future updates.
Choose the appropriate integer type based on your 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 streamline database design in MySQL.