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 11 in 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 |
|---|---|---|
| TINYINT | 1 | 255 |
| SMALLINT | 2 | 65535 |
| MEDIUMINT | 3 | 16777215 |
| INT | 4 | 4294967295 |
| BIGINT | 8 | 2^63-1 |
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 0 to 255. The BIGINT data type is the largest integer data type that MySQL provides. It can store values from 0 to 2^63-1.
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 mediumint or 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.
Like this article?
Buy me a coffee
👋 Hi there! I'm Amit. I write articles about all things web development. You can become a sponsor on my blog to help me continue my writing journey and get your brand in front of thousands of eyes.
