MySQL integer columns and display width

November 28, 2006 / Filed under: MySQL, Tips

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.

Comments/Mentions

# Peekay at 12/3/2006 4:25 am cst

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 would be 0 - 255 so three digits is the max. I'm trying to find out if the minus sign needs to be accounted for.

# Matthom at 12/3/2006 5:19 am cst

Peekay, it should be TINYINT(4), for the signed range. The negative sign does count as part of the width, as far as I know...

For example, if you don't include a display width, when creating the field, MySQL will put a default display width, and it will be 4 for signed TINYINT. (and 3 for unsigned TINYINT.)

That's usually a good way to test it - just let MySQL put the default value in there.

Hope that helps...

# Peekay at 12/5/2006 3:06 am cst

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 default width. I'm using PHPMyAdmin to build some tables and it throws an error if you leave the width empty for character fields. I didn't realise you could leave this empty for numeric fields.

# Jason at 12/20/2006 9:34 pm cst

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 concerned that the values would be chopped off. Looks like I won't need to worry about... I have about 3 centuries before it will increment to 16.7 million, I think I can make the necessary modifications by then. Thanks Matthom!

# Lakshitha at 8/28/2008 2:24 am cst

hi... this is a very straightforward answer.. i was thinking about this matter, now it has cleared.. thanks.. a good job.. :)

# Vincent Voyer at 10/1/2008 3:39 am cst

Thank you so much i have been wrong for 5 years since now !

:):)

# Shawn at 10/17/2008 12:25 pm cst

Thanks a lot! This helped me out a great deal (The ZeroFill tip)

# Marcelo Oliveira at 11/5/2008 11:35 am cst

thx. wonderfully explained!

# Goose at 6/10/2009 7:56 am cst

Thanks just what i was looking for! :-)

# Chris Jacob at 11/5/2009 8:59 pm cst

Thank you! The MySQL guys should hire you to write more clear articles like this one!