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.

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

Inserting the maximum value succeeds:

<code>INSERT INTO `user` (`id`) VALUES (4294967295);</code>

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

.

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

Inserting values 1, 10, 100, 1000:

<code>INSERT INTO `user` (`id`) VALUES (1),(10),(100),(1000);</code>

Selecting the data shows padded zeros:

<code>SELECT * FROM user;</code>

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.