When it comes to columns such as INT (11) and TINYINT (3), the number in brackets isn’t actually that important. When you first look at them, it is easy assume that these numbers represent the maximum length of the column, but that is not the case.
So what does INT (11) actually mean?
The number inside the brackets is the display width of the column. This number tells MySQL how many spaces should be prepended if the value inside the column is being displayed inside the MySQL console.
For example: If an INT (11) column contains the number “1”, then the console will add 10 spaces to the front of it. If the column contains the number “78”, then 9 spaces will be prepended.
Essentially, the number inside the brackets isn’t really that important. It does not control how large the integer column can be and it isn’t really that useful unless you are using the ZEROFILL attribute (which I don’t even recommend using).
So what actually determines the maximum size?
The maximum size / length of the column is determined by the type of integer column that you choose. i.e. An INT (3) column will have the same maximum size as an INT (11) column.
MySQL provides you with five different integer types to choose from. Here is a list of them, ordered from smallest to largest:
- TINYINT
- SMALLINT
- MEDIUMINT
- INT
- BIGINT
These columns have different minimum and maximum values.
If you are using a regular SIGNED column, then these minimum and maximum values will be as follows:
- TINYINT: -128 to 127
- SMALLINT: -32768 to 32767
- MEDIUMINT: -8388608 to 8388607
- INT: -2147483648 to 2147483647
- BIGINT: -9223372036854775808 to 9223372036854775807
As you can see: The min / max sizes differ dramatically between each integer type.
If you plan on storing positive non-negative numbers, then you should use the UNSIGNED attribute when creating your columns. If you use an UNSIGNED column, then the minimum and maximum values will be:
- TINYINT: 0 to 255
- SMALLINT: 0 to 65535
- MEDIUMINT: 0 to 16777215
- INT: 0 to 4294967295
- BIGINT: 0 to 18446744073709551615
Note that the BIGINT column is overkill in most cases and that it will only result in your database being larger in size.
What is the UNSIGNED attribute?
The UNSIGNED attribute tells MySQL that the integer column in question will only contain numbers that are 0 or above.
i.e. You cannot store a “-1” value in an UNSIGNED column. The value must be 0 or higher.
For example, if you were only storing a number between 1 and 100, then you would probably use an UNSIGNED TINYINT column, as the min / max values for an UNSIGNED TINYINT column is 0 to 255.