MySQL - INT(11) vs BIGINT(11) vs TINYINT(11)

This seems to be a common misconception among many developers who interact with MySQL. The assumption is that the 11 in INT(11) determines the maximum size of the integer that the column can store. i.e. That a column set to INT(11) will hold an integer value up to 11 digits in length and that a column set to INT(6) will hold an integer value that is up to 6 digits in length.This misconception exists because of three reasons:

1. Developers rarely get caught out by it. This is because a regular integer column can store integer values up to 2147483647. That's close to 2.15 billion. This means that an integer primary key wont "hit the ceiling" until the table in question holds more than 2 billion rows. In most cases, this never happens.
2. String data types such as CHAR and VARCHAR do allow you to specify the maximum length. For example: A column set to VARCHAR(30) will only hold 30 characters or less. Anything over that amount will be truncated. 
3. A lot of developers never read the manual.

So, if the 11 in INT(11) isn't a determinant for the column's maximum size, what is? Simply put: It's the data type itself that determines the maximum value. For instance: A regular TINYINT column will always have a max value of 127, an INT column will always have a maximum value of 2147483647 and a BIGINT column will always have a maximum value of 9223372036854775807. The number in brackets has zero affect on the size of the integer being stored. To get a full list of the ranges of integer data types, have a look at the official manual.

What does the number in brackets actually do?

To be honest, the number in brackets is kind of useless unless you're using the ZEROFILL attribute.

All it does is tell MySQL what width to display the column at when the table's data is being viewed via the MySQL console. If you're using the ZEROFILL attribute, the number in brackets will tell MySQL how many zeros to pad incoming integers with. For example: If you're using ZEROFILL on a column that is set to INT(5) and the number 78 is inserted, MySQL will pad that value with zeros until the number satisfies the number in brackets. i.e. 78 will become 00078 and 127 will become 00127. To sum it up: The number in brackets is used for display purposes.

Example :

mysql> create table Test(id int(2) zerofill);
Query OK, 0 rows affected (0.29 sec)

mysql> insert into Test values(1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from Test;
+------+
| id   |
+------+
|   01 |
+------+
1 row in set (0.01 sec)

mysql> insert into Test values(11111);
Query OK, 1 row affected (0.04 sec)

mysql> select * from Test;
+-------+
| id    |
+-------+
|    01 |
| 11111 |
+-------+
2 rows in set (0.00 sec)

mysql> alter table Test modify id int(5) zerofill ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from Test;
+-------+
| id    |
+-------+
| 00001 |
| 11111 |
+-------+
2 rows in set (0.00 sec)

Advantage of using int(N) type declaration :

So having said the above, you can expect the display width to have no affect on the results from a standard query, unless the columns are specified as ZEROFILL columns
OR
In the case the data is being pulled into an application & that application is collecting the display width to use for some other sort of padding.

Side note

The official MySQL manual  also lists the number of bytes that each integer data type requires. A TINYINT will take up 1 byte. A SMALLINT will take up 2 bytes. An INT will take up 4 bytes. A BIGINT will take up 8 bytes. This means that you should choose your integer data types wisely. Last week, I switched a column from INT(1) to TINYINT(1) because it was only being used to store 1s and 0s. The result? An unncessary 6MB was shaved off the total size of the table. After making similar changes to a few more columns, I had managed to get rid of 25MB of uneeded data. Try to keep this in mind the next time you're using an INT instead of a TINYINT or a BIGINT instead of an INT.