SQL Help

INT(11) doesn't mean what you think

Updated 26 October 2026

Based on the SQL Help series David J. Lake published here in 2006 to 2009. Rewritten for MySQL 8 by the WeFixTech editorial team.

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

TypeBytesSigned rangeUnsigned range
TINYINT1−128 … 1270 … 255
SMALLINT2−32,768 … 32,7670 … 65,535
MEDIUMINT3−8,388,608 … 8,388,6070 … 16,777,215
INT4−2,147,483,648 … 2,147,483,6470 … 4,294,967,295
BIGINT8±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.