in MySQL if we create a field dataType of INT and does not specify any length/values then it automatically became int(11) and if we set the attribute UNSIGNED or UNSIGNED ZEROFILL then it turns into int(10)
Where does this length(1) goes?
Add a comment
|
3 Answers
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.
4 Comments
xkeshav
Thanks a ton.. btw i m also from Bikaner :)
Shakti Singh
@diEcho: Great, We both are from same place. Nice to talk you on SO
Ravikumar Sharma
Hello Rajasthanis :).. Your post still helping us :)
Prusprus
So if I understand correctly, the numeric value (10 or 11) isn't tied to the memory size allocated for this column, but the number of characters allows to form the number? So setting a column as UNSIGNED INT(11) won't allow any more number than an UNSIGNED INT(10)?
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.
answered Jul 25, 2011 at 1:38
Lightness Races in Orbit
387k77 gold badges670 silver badges1.1k bronze badges
1 Comment
blio
That is great. It means the display width, not the range of values.
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
2 Comments
Lightness Races in Orbit
Spaces to save the value have nothing to do with it. This is only the display width and does not constrain the stored value in any way.
Julien Palard
The signed max is not 2^32-1 but 2^31-1, as you have one bit for the sign, so unsigned max != signed max.