MySQL datatype INT(11) whereas UNSIGNED INT(10)?

Solution 1:

int value can be -2147483648 these are 11 digits so the default display size is 11

unsigned int does not allow negative numbers so by default it need only display size 10

As the documentation below shows, the number of bits required to store SIGNED INT and UNSIGNED INT is the same, the range of storable numbers is merely shifted:

Unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Solution 2:

According to the documentation, this number is merely the display width.

For example, INT(4) specifies an INT with a display width of four digits.

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

The default display width for an UNSIGNED INT is one fewer than that for a non-UNSIGNED INT simply because you will never be displaying a - character.

Note that you can still specify whatever display width you like. This is just the default.

The use of the term "digits" in the documentation is slightly misleading here.

Solution 3:

Just incase anyone doesn't quite understand Shakti's answer (as I didn't). Here's a visual representation of why:

 Signed minimum:
 - 2 1 4 7 4 8 3 6 4  8
 1 2 3 4 5 6 7 8 9 10 11

 Unsigned max (also the signed max):
 4 2 9 4 9 6 7 2 9 5
 1 2 3 4 5 6 7 8 9 10