MySQL integer columns and display width Nov28 '06
One confusing aspect of MySQL is declaring integer columns. It took me a while to understand what the number in parenthesis meant, for example:
INT(6)
Initially, I thought it meant that column could only hold unsigned (no negatives) integer values up to a maximum 999999, which is 6 characters wide.
This is wrong thinking. The 6 means something else entirely...
However, the reason I thought this is because string columns are declared in a similar fashion:
CHAR(6)
This means a character column, with a maximum length of 6 characters, such as these words:
- houses
- string
- column
- grease
- streak
Notice those are all 6 characters in length.
If I tried storing the word "special" in that column, MySQL would chop the value to be "specia", since the original word has 7 characters.
Anyway, integer columns all have a preset range of values allowed. The number in parenthesis only indicates the display width.
This is probably still confusing, so let me explain further...
The display width is a number from 1 to 255. You can set the display width if you want all of your integer values to "appear" similarly:
INT(6)
- 55
- 643
- 1098
- 81153
Wait... those numbers above don't appear similar at all! They're all different lengths!
Actually, you can't see it, because the left side is padded with spaces.
To help visualize it easier, try this:
- _ _ _ _ 5 5
- _ _ _ 6 4 3
- _ _ 1 0 9 8
- _ 8 1 1 5 3
Does that help? Notice how they all have a display width of 6.
Sure, that helps. But no one can see spaces normally, so why does setting the "display width" matter?
Enter ZEROFILL.
If you declare your integer column like this:
INT(6) ZEROFILL
... your numbers will "appear" like this:
- 000055
- 000643
- 001098
- 081153
Notice how the left side is padded with zero's, which makes the numbers look consistent, no matter what the "real" length is.
Great! Does declaring the "display width" affect the actual range of values allowed, or the storage space?
No, for both. You can still store up to the maximum value allowed for that column type, no matter what you set as the "display width." Also, the "display width" does not affect the number of bytes of storage required.
For example, if your column is declared like this:
INT(6)
... and you want to store the number 6543210, which is 7 characters, it will still be accepted.
Here is a partial list of numeric column type ranges:
| Column Type | Signed (negative) values | Unsigned (non-negative) values |
|---|---|---|
| TINYINT | -128 to 127 | 0 to 255 |
| SMALLINT | -32768 to 32767 | 0 to 65535 |
| MEDIUMINT | -8388608 to 8388607 | 0 to 16777215 |
| INT | -2147683648 to 2147483647 | 0 to 4294967295 |
| BIGINT | -9223372036854775808 to 9223372036854775807 | 0 to 18446744073709551615 |
More information can be found at the Numeric Data Types section of the MySQL Reference Manual.
Add Feedback (view all)
Leave feedback
Peekay, it should be TINYINT(4), for the signed range. The negative sign does count as part of the width, as far ... Read more.
Many thx Matthom. :-) I couldn't find any mention of this on the official MySQL website. Thanks also for the tip about letting MySQL set its own de ... Read more.
Thanks - had a db field which was about to increment to 100,000 (probably overnight) and had just noticed the fields were MEDIUMINT(5). Was conce ... Read more.
matthom 
is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from a suburb of Chicago.
Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.
- Ripping as lossless from now on
- More on displaying table data
- Brightkite suggests Twitter instead of 404
Popular Pages
- Fast rounded corners in Photoshop (3954 recent visits)
- PHP – passing variables across pages (1485 recent visits)
- JavaScript set selected on load (1208 recent visits)
- Removing all child nodes from an element (827 recent visits)
- iPod songs out of order? (719 recent visits)
- Britney - Everytime piano tab (649 recent visits)
- Firefox 3 smart address bar: wildcard search (607 recent visits)
- MySQL LEFT JOIN syntax (513 recent visits)
- Breathe Me - Sia (501 recent visits)
- Tumblr: how blogging should be (384 recent visits)
Similar Entries
- Install Apache, PHP, MySQL on Windows (17 recent visits)
- MySQL search criteria - column alias (126 recent visits)
- MySQL LEFT JOIN syntax (513 recent visits)
- MySQL viewing saved searches (2 recent visits)
- MySQL changing column types (89 recent visits)
- MySQL: enum or char(1)? (56 recent visits)
Stats
164 unique visits since August 2008
Recent Referrers (click)
- mysql int(11) can hold values up to
- mysql type width length
- mysql int max 255
- mysql int max 255
- mysql width
- mysql integer
- mysql range of integers
- int mysql
- mySql int(11)
- mysql int range number
- mysql caracter em number int
- mysql int 0 zeros
- mysql why use int(11)
- mysql int(
- mysql int
- mysql int starting 0
- mysql display special characters
- mysql integer
- MySQL int
- mysql int
If the unsigned range of TINYINT is -128 to 127, should the display width be TINYINT(3) or TINYINT(4)? I understand that the unsigned range ... Read more.