Does MySQL int(1) Limit Your Values? Uncovering Display Width and ZEROFILL

In MySQL, the integer type always occupies 4 bytes, giving an unsigned range of 0 to 4,294,967,295 regardless of the number in parentheses. The number in int(N) is only a display width hint and does not affect the actual storage capacity.

Demonstration of int(1) Capacity

Creating a table with an unsigned int(1) primary key and inserting the maximum unsigned value succeeds, proving that int(1), int(2), … int(10) all support the same range.

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

INSERT INTO `user` (`id`) VALUES (4294967295);
Query OK, 1 row affected (0.00 sec)

When Does the Number Matter? ZEROFILL

The display width becomes visible only when the ZEROFILL attribute is used. With int(4) ZEROFILL, MySQL pads values to four digits when displayed, while the stored value remains unchanged.

CREATE TABLE `user` (
  `id` int(4) unsigned ZEROFILL NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user` (`id`) VALUES (1),(10),(100),(1000);
SELECT * FROM user;
+------+
| id   |
+------+
| 0001 |
| 0010 |
| 0100 |
| 1000 |
+------+

The output shows leading zeros added for numbers with fewer than four digits, but the underlying storage is still the original integer.

Key Takeaways

The number in int(N) does not limit the numeric range; it only defines display width.

To see the effect of display width, combine it with ZEROFILL.

ZEROFILL is useful for formatting identifiers (e.g., student numbers) to a fixed length without altering the stored value.