Understanding MySQL INT Display Width and ZEROFILL Behavior

If you add a user_id column to a MySQL table and specify INT(1), you might think the column can only store values up to 9, but the INT type always occupies 4 bytes and can store up to 2^32‑1 (4294967295) when unsigned.

Running

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

and then inserting the maximum unsigned value INSERT INTO `user` (`id`) VALUES (4294967295); succeeds, showing that the number in parentheses does not restrict the range.

The number after INT only influences the display width when the column is defined with the ZEROFILL attribute. For example:

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

Inserting values 1, 10, 100, and 1000:

INSERT INTO `user` (`id`) VALUES (1),(10),(100),(1000);
SELECT * FROM `user`;

produces the output:

+------+ 
| id   |
+------+ 
| 0001 |
| 0010 |
| 0100 |
| 1000 |
+------+

This demonstrates that ZEROFILL pads the displayed value with leading zeros to match the specified width, while the stored value remains unchanged.

In summary, the number in INT(N) does not define the column's capacity; it only sets a display width that becomes visible when ZEROFILL is used, which is useful for fixed‑length numeric identifiers such as student numbers.