Why MySQL int(1) Doesn't Limit Values and How Zerofill Changes Display

Confusion

When adding a user_id column, I used ALTER TABLE xxx ADD user_id int(1). My manager thought int(1) might be too small, but many experienced developers also use int(10) without realizing the number in parentheses does not limit the field size.

Data speaks

In MySQL an INT occupies 4 bytes; an unsigned INT can store up to 2^32‑1 = 4294967295. Using int(1) does not reduce this maximum.

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

Inserting the maximum value succeeds: INSERT INTO `user` (`id`) VALUES (4294967295); The query succeeds, confirming that the display width does not affect the actual range; int(1), int(2) … int(10) are equivalent in storage.

Zero Fill

The numeric argument only becomes meaningful when used with ZEROFILL.

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, 1000:

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

Selecting the data shows padded zeros: SELECT * FROM user; Result: +------+ | id | +------+ | 0001 | | 0010 | | 0100 | | 1000 | +------+

Conclusion

The number in int(num) does not define the field length; it only affects display when combined with ZEROFILL. ZEROFILL is useful for fixed‑length identifiers such as student numbers (001, 002 … 999) when you need zero‑padded output.