MySQL integer columns and display width

Posted on by

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)

15 thoughts on “MySQL integer columns and display width”

  1. 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.

  2. 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…

  3. 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.

  4. 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!

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

  11. Thanks a lot!!! I actually thought I was right by thinking that the number inside the parenthesis limits the number of values an integer field can store.

  12. Hi Matt,

    Great article but it only answers half the question. Now I know what it is, but I still don’t know what to use it for. displaywidth as displayed where? The mysql CLI shell? If I write a PHP program and grab some data from the database and echo that to the screen as-is. What do I get?

    Also, there is a performance hit when zerofilling each number after the fact since it’s not stored with the zeroes.

    Thanks!

  14. i’m still in a confusion, you guys mentioned above saying the number specified inside the parenthesis is the display width, but that doesn’t work fine for me,
    i created a table size with a column ‘sl’ int(2) so, just 2 digit number has to be displayed right? but it displays a 11 digit number(the default size of int)

  15. I clarified my doubt,
    the display width works out when you enter a number like ’00032′, here the zero’s are neglected and 32 is stored in the table.

    correct me if i’m wrong

Comments are closed.