I came to the world of MySQL with very little experience. I inherited a DB with a client I took on, and found myself learning what I needed as I went on. And I found something out today that surprised me, and had escaped me for some time. And clearly, the original designers of the DB had missed this too.
This sort of definition is seen a lot in table creation:
INT(11)
This does not mean that the column can contain values from 0 to 2^11. It means the field is a SIGNED INT that can contain values of -2147483648 to 2147483647, and if it’s a ZEROFILL, it will always be padded with leading zeroes to display 11 characters.
Consequently:
SMALLINT(1)
Is just a SMALLINT that can only contain values from -128 to 127 and will only display whatever value is in the field. 0 or 1 will display as 0 or 1, and 125 displays as 125. The content is not truncated by the (VALUE) in brackets. For most purposes, the value in brackets does not really do a lot.
The only factor that affects what values a column can contain is whether it is declared as a TINYINT, SMALLINT, MEDIUMINT, INT or BIGINT. And the MySQL reference page here shows the range of values for each.
Maybe not news to some, but definitely something I learnt today. Props go to the author of this article that was the root blog post that led me to this realisation.
0 Comments.