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.

Categories: MySQL , Tips

Add Feedback (view all)