INT(11) doesn't mean what you think
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
| 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
UNSIGNEDfor counts and ids: nothing that can't be negative should waste half its range on negative numbers. An unsignedINTid gets you to 4.29 billion rows. TINYINTfor flags and small enums:TINYINT(1)is also what MySQL aliasesBOOLEANto.- Auto-increment keys that might grow big: start with
BIGINT UNSIGNEDon 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), neverFLOAT: floating point can't represent most decimal fractions exactly, and pennies go missing.DECIMALstores exact digits.