Understanding MySQL INT Display Width and ZEROFILL

When adding a user_id column to a MySQL table, many developers mistakenly think that the number in int(N) restricts the column's maximum value; in reality, the display width does not affect the storage size.

MySQL stores an INT in 4 bytes, so an unsigned INT can hold values up to 2^32‑1 = 4294967295 . Inserting this maximum value into a column defined as int(1) succeeds, proving that int(1) , int(2) , … int(10) all have the same range.

Only when the ZEROFILL attribute is used does the number after int affect the displayed output. For example, creating a table with int(4) unsigned ZEROFILL and inserting values 1, 10, 100, 1000 results in the following query output:

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

The underlying stored values remain the same (1, 10, 100, 1000); only the presentation adds leading zeros to reach the specified width.

Key code examples:

ALTER TABLE xxx ADD user_id int(1);

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

INSERT INTO `user` (`id`) VALUES (4294967295);

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

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

SELECT * FROM user;

In summary, the number in int(N) does not define the column's numeric capacity; it only specifies the display width, which becomes visible when combined with ZEROFILL . Use ZEROFILL for fixed‑length numeric identifiers (e.g., 001, 002) if you need zero‑padded output.

Note: The article also includes a promotional notice offering a free book collection, but this does not affect the technical explanation above.