Deep Analysis of MySQL Numeric Types: Differences Between BigInt and Int and the Meaning of Display Width

Fundamental Concepts of MySQL Numeric Types

In the MySQL database system, the choice of numeric types directly impacts data storage efficiency and query performance. Common integer types include INT, MEDIUMINT, and BIGINT, with their primary differences lying in storage space and value range.

Storage Size and Value Range Comparison

The INT type utilizes 4 bytes of storage space. As a signed integer, its value range spans from -2,147,483,648 to 2,147,483,647. The unsigned INT range is from 0 to 4,294,967,295. This means the INT type can store up to 2³² distinct values.

The BIGINT type, on the other hand, uses 8 bytes of storage. As a signed integer, its range is from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. The unsigned BIGINT ranges from 0 to 18,446,744,073,709,551,615, capable of accommodating 2⁶⁴ distinct values in total.

The True Meaning of Display Width Parameter

Many developers mistakenly believe that the number 20 in INT(20) or BIGINT(20) indicates storage capacity or value range limitations. This is actually a common misconception. This parameter serves solely as a display width hint and is completely unrelated to the actual storage size.

The primary role of the display width parameter becomes evident when used with the ZEROFILL option. For example:

CREATE TABLE example_table (
    id_column INT(20) ZEROFILL,
    large_id BIGINT(20) ZEROFILL
);

INSERT INTO example_table (id_column, large_id) VALUES (1234, 567890);
SELECT id_column, large_id FROM example_table;

After executing the above query, the output will display:

+----------------------+----------------------+
| id_column            | large_id             |
+----------------------+----------------------+
| 00000000000000001234 | 0000000000000567890 |
+----------------------+----------------------+

Practical Considerations in Application

When selecting numeric types, developers should base their decisions on actual business requirements and expected data ranges. If the stored values are not anticipated to exceed 2 billion, using the INT type is sufficient and saves storage space. For scenarios requiring the storage of extremely large values, such as global unique identifiers or timestamps, the BIGINT type should be chosen.

It is particularly important to note that the behavior of the display width parameter may vary across different database clients. Some client tools might ignore this parameter, while others may adjust the display format based on its value.

Comparison with Other Data Types

Unlike character types such as CHAR(20), the display width parameter in numeric types does not restrict the actual stored value size. Even if defined as INT(5), the column can still store any valid INT value, with the display width only affecting padding behavior when ZEROFILL is used.

In practical development, it is advisable to select the appropriate type based on the expected maximum value range, avoiding the overuse of BIGINT which wastes storage space, while also preventing the use of types that are too small and could lead to data overflow.