INT(11) doesn't mean what you think

Updated August 2026 · First published 2006 by David J. Lake

Part of the classic SQL Help series first published on this domain by David J. Lake (guelphdad). Rewritten from scratch and updated for MySQL 8 — same address, modern SQL.

Twenty years ago GUI tools sprinkled INT(11) and SMALLINT(6) through everyone's schemas, and people naturally read the bracketed number as a size limit — "eleven digits max". It never meant that. An INT holds the same range whether you write INT(1), INT(11) or plain INT.

What the number actually was

It was a display width, and it only ever did anything visible in combination with ZEROFILL: an INT(6) ZEROFILL column displays 100 as 000100, left-padding with zeros to six characters. No padding requested, no effect at all — storage, range and maths were always identical.

The modern postscript: MySQL 8.0 deprecated both — display widths for integers (8.0.17) and ZEROFILL itself. New schemas should write bare INT, BIGINT, etc.; when you see INT(11) in an old dump, know it's an inert fossil. If you need padded numbers for display, format at query time: LPAD(order_no, 6, '0') — formatting belongs in the query or the application, not in the column type.

The real limits

Type	Bytes	Signed range	Unsigned range
`TINYINT`	1	−128 … 127	0 … 255
`SMALLINT`	2	−32,768 … 32,767	0 … 65,535
`MEDIUMINT`	3	−8,388,608 … 8,388,607	0 … 16,777,215
`INT`	4	−2,147,483,648 … 2,147,483,647	0 … 4,294,967,295
`BIGINT`	8	±9.22 × 10¹⁸	0 … 18,446,744,073,709,551,615

Choosing well

Add UNSIGNED for counts and ids — nothing that can't be negative should waste half its range on negative numbers. An unsigned INT id gets you to 4.29 billion rows.
TINYINT for flags and small enums — TINYINT(1) is also what MySQL aliases BOOLEAN to.
Auto-increment keys that might grow big: start with BIGINT UNSIGNED on high-churn tables. Hitting an id ceiling (remember: gaps burn values without adding rows) forces an ugly online migration later; four extra bytes now is cheap insurance.
Exact money: DECIMAL(p,s), never FLOAT — floating point can't represent most decimal fractions exactly, and pennies go missing. DECIMAL stores exact digits.