The Misconception

A common confusion arises when working with MySQL integer types. Many developers believe that specifying different values like int(1) or int(10) affects the actual storage capacity or maximum value of the integer field. This misconception often leads to unnecessary debates in code reviews and database design discussions.

Technical Reality

In MySQL, the INT data type always occupies 4 bytes of storage, regardless of the number specified in parentheses. For an unsigned INT, the maximum value is 2^32-1 = 4,294,967,295 (approximately 4.3 billion). Let's demonstrate this with a practical example:

CREATE TABLE `customer` (
 `customer_id` int(1) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Despite defining the field as int(1), we can still insert the maximum value:

mysql> INSERT INTO `customer` (`customer_id`) VALUES (4294967295);
Query OK, 1 row affected (0.01 sec)

This confirms that int(1) and int(10) have identical storage capabilities and maximum values.

The ZEROFILL Connection

The number in parentheses becomes meaningful only when used with the ZEROFILL attribute. Consider this example:

CREATE TABLE `product` (
 `item_number` INT (5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`item_number`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;

Let's insert several values:

mysql> INSERT INTO `product` (`item_number`) VALUES (7),(42),(158),(2301);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

Querying the table reveals the formatting effect:

mysql> SELECT * FROM product;
+-------------+
| item_number |
+-------------+
| 00007 |
| 00042 |
| 00158 |
| 02301 |
+-------------+
4 rows in set (0.00 sec)

As shown, the ZEROFILL attribute displays numbers with leading zeros to match the specified width. The actual storage remains unaffected - the database still stores the numerical values 7, 42, 158, and 2301.

Practical Applications

The ZEROFILL functionality is particularly useful for formatted identifiers like invoice numbers, student IDs, or product codes where consistent digit width is important for dissplay purposes. Without this feature, developers would need to handle zero-padding application-side, adding unnecessary complexity to their code.

Conclusion

When definnig integer columns in MySQL, remember that int(N) does not restrict the range of values that can be stored. The number only has meaning when combined with ZEROFILL, which controls display formatting. For standard integer storage without specific display requirements, simply using INT is sufficient.