The other day while designing a database schema i was suggested to use Tinyint instead of int. Tinyint, never ever used in SQL. So i digged a bit about this datatype. Later i thought probably i can use INT(1) for declaring the database field rather than TINYINT(1) as both might be restricted to a single digit only. Yes they would. But representation of TINYINT(1) is not exactly this. I’ve always just used INT(1) to say it’s an integer and it will only be one digit, which I assume means it could only be a value 0 through 9.
So, is there any difference between the various integer types INT, TINYINT, SMALLINT, MEDIUMINT, and BIGINT if you define a length of 1 for each type? And the answer is Yes there is a big diffeence between these data types though they belong to same class.
The number in parentheses for integer column types is the “display width”. This does not effect the storage requirements as they are pre-defined. INT(1) does not allow only one character, infact it sets the display width for that column in result sets. 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. So INT(5) will not hold maximum value as 99999 but it will set the width of number as 5 for display and will truncate more or pad with space if less.
As an example quoted from Matthom
INT(6) sets the display as:
where the left side are padded with spaces.
To help visualize it easier, try this:
_ _ _ _ 5 5
_ _ _ 6 4 3
_ _ 1 0 9 8
_ 8 1 1 5 3
Notice how they all have a display width of 6.
Also, notice the datatype range and size would be different for int and tinyint. While int takes 4 bytes, tinyint takes just a single byte.