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:

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)

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:

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:

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.

Categories: MySQL , Tips

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

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.

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.

Contact Matt

Popular Pages

  1. Fast rounded corners in Photoshop (3954 recent visits)
  2. PHP – passing variables across pages (1485 recent visits)
  3. JavaScript set selected on load (1208 recent visits)
  4. Removing all child nodes from an element (827 recent visits)
  5. iPod songs out of order? (719 recent visits)
  6. Britney - Everytime piano tab (649 recent visits)
  7. Firefox 3 smart address bar: wildcard search (607 recent visits)
  8. MySQL LEFT JOIN syntax (513 recent visits)
  9. Breathe Me - Sia (501 recent visits)
  10. Tumblr: how blogging should be (384 recent visits)

Similar Entries

Stats

164 unique visits since August 2008

Syndicate

Advertisements