Feb 03 2011
Answer is actually very simple, but several of my friends have missed the point of having size modifier in MySQL column type definitions, so I suppose it’s something non-obvious. Main confusion, I believe, has its roots in string data-type definitions:
VARCHAR(N)
CHAR(N)
where N is the maximum number of characters you want to store into field, i.e. it is field’s length. And depending on your settings you might receive an error (in strict SQL mode) when you are inserting something that exceeds the pre-defined length. Even if you don’t receive error or warning, the field is limited to the length you set, and the exceeding part is truncated. So, VARCHAR(20) and VARCHAR(40) are really different with respect to how big the stored value could be.
Now, let’s get back to numeric types. For numeric types length modifier is actually display width, which has nothing to do with what can be stored in field. Yes, that’s it – both TINYINT(1) and TINYINT(4) can store values in range -128..127 (or for unsigned values 0..255), and are absolutely identical datatypes, with one small twist (which concerns date retrieval – see below).
Here is explanation of what is meant by display width, taken directly from the source:
The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.
So, if display width doesn’t constrain the range of values our numeric type can hold, why to use it at all? On MySQL forum, there was a post suggesting that by using the display width, we set mental reminder to ourselves on how big the field should be. Me personally, find this quite logical, this is some kind of mental tooltip which pops up when you review your schema.
Of course, there’s a more deliberate functionality for having display width. One obvious example would be ZEROFILL attribute, which when used in datatype definition guarantees that the value returned would always have pre-defined width, and default space padding is replaced with zeroes. For example, when column is defined as INT(5) ZEROFILL, and value to be retrieved is 4, MySQL would return it as 00004.
So, the bottom line: for numeric types, N in TYPE(N) has nothing to do with value range, and refersto display width.
Type Storage Minimum Value Maximum Value (Bytes) (Signed/Unsigned) Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615
int unsigned 4294967295
@philip andrew:
Well, actually integer is quite big enough to make sure you would not run out of numbers. If you use unsigned int, then the maximum is 4294967295 – and believe me it is a huge number!
Of course there might be situations when even this number is not enough, and you resort to BIGINT, but it happens more rare than one might think. Primary keys are used to uniquely identify some entity in your table – and if your application has 4294967295 of anything, it’s quite a big application.
Why I prefer INT? Because of performance – if you don’t need big number, and you use it, then you are wasting not only your resources but computing power as well. MySQL is extremely good when it comes to int primary key traversal (for selects, joins), so try to stick with INT, unless you really need BIGINT.