Does MySQL int(1) Limit Your Data? The Truth About Display Width and ZEROFILL

Confusion

Recently I needed to add a user_id column to a table, and I submitted a MySQL work ticket with ALTER TABLE xxx ADD user_id int(1). My manager warned that int(1) might be insufficient, leading to a long discussion.

Many experienced developers also use int(10) and mistakenly think the number in parentheses limits the field size, but that is not the case.

Data Speaks

In MySQL, int occupies 4 bytes, so an unsigned int can store up to 2^32‑1 = 4294967295 (about 4 billion). 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;

Insert the maximum unsigned value:

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

The insertion succeeds, showing that the number after int does not affect the actual range; int(1), int(2) … int(10) are equivalent.

Zero Fill

The number after int only has an effect when used together 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;

Insert four rows:

INSERT INTO `user` (`id`) VALUES (1),(10),(100),(1000);
-- Query OK, 4 rows affected

Query the table:

SELECT * FROM user;
+------+
| id   |
+------+
| 0001 |
| 0010 |
| 0100 |
| 1000 |
+------+
4 rows in set (0.00 sec)

The result shows that int(4) ZEROFILL pads numbers with leading zeros to a width of four digits, while the underlying storage remains the same integer value.

Summary

The number in int(num) does not define the field’s length; it only influences display when combined with ZEROFILL. ZEROFILL is useful for fixed‑width numeric identifiers such as student numbers (001, 002 … 999) when a consistent visual format is required.

Source: juejin.cn/post/6992574502282477605