An int(11) is not what you think it is in MySQL
Here’s the biggest myth I had about MySQL’s
int data type that just got busted while watching one of the videos from the course MySQL for Developers. Essentially, I have been doing it wrong all this time.
So, when designing a database schema I have always been using
int(11) as the default data type for any integer column (and I suppose you might have done this at some point). But I never knew what that
11 signifies. I just assumed that it was the default length of the integer column.
But, it turns out that it’s not the case. The
int(11) is not the length of the integer column. It’s just a display width hint for the MySQL client.
So, if you have a column that has a value of
123 and you have defined it as
int(11), the MySQL client will display it as
123 but if you have defined it as
int(5), it will display it as
00123. And that too only if you have defined the
ZEROFILL attribute for the column. Otherwise, it doesn’t have any effect at all.
There are better integer data types that you can use for a column based on the context for which you want to use it.
First look at this table.
|Type||Storage (Bytes)||Maximum Unsigned|
As you can tell, we have a range of integer data types in MySQL. The
TINYINT data type is the smallest integer data type that MySQL provides. It can store values from
BIGINT data type is the largest integer data type that MySQL provides. It can store values from
So, this means that if you predict the value of your data will never need a huge number like
4294967295, you can choose not to use the
int data type. We are just over-allocating the storage space for the data. Instead, we can use a relatively smaller integer data type like
smallint which will better utilize the storage space.
This was a really interesting revelation for me and I hope it was for you too.
I would highly recommend you try MySQL for Developers course which has so many little tidbits like this.