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
Consider reading the site disclaimer before commenting.
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.
hi... this is a very straightforward answer.. i was thinking about this matter, now it has cleared.. thanks.. a good job.. :) ... Read more.
Thank you so much i have been wrong for 5 years since now ! :):) ... Read more.
Thanks a lot! This helped me out a great deal (The ZeroFill tip) ... Read more.
thx. wonderfully explained! ... Read more.
Thanks just what i was looking for! :-) ... Read more.
matthom
is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from 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.
Contact Matt | Site Disclaimer
Similar Entries
- Install Apache, PHP, MySQL on Windows (342 recent visits)
- MySQL search criteria - column alias (3008 recent visits)
- MySQL LEFT JOIN syntax (6918 recent visits)
- MySQL viewing saved searches (46 recent visits)
- MySQL changing column types (1871 recent visits)
- MySQL: enum or char(1)? (961 recent visits)
Stats
3012 unique visits since April 2009
Recent Referrers (click)
- characters wide mysql int
- real size of integer mysql
- mysql number integer
- mysql int length
- mysql integer order
- mysql int length/value
- mysql int length/value
- mysql maximum display width
- mysql maximum display width
- mysql int percent
- increase column width in mysql
- mysql size int
- mysql display width
- mysql column width
- mysql int length
- http://bing.com/search?q=mysql
- mysql zero padding
- mysql set column width
- http://bing.com/search?q=colum
- mysql max int(10)
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.