A very common misconception about what int(11) means in MySQL is that the column can store maximum integer value with 11 digits in length. However, this is not true. int(11) does not determines the maximum value that the column can store in it. 11 is the display width of the integer column, unlike the characters columns where the number means number of character that can be stored.
The number in the parenthesis does not determines the max and min values that can be stored in the integer field. The max and min values that can be stored are always fixed. The following table shows the required storage and range for each integer type.
Type | Storage | Minimum Value | Maximum Value |
---|---|---|---|
(Bytes) | (Signed/Unsigned) | (Signed/Unsigned) | |
TINYINT | 1 | -128 | 127 |
0 | 255 | ||
SMALLINT | 2 | -32768 | 32767 |
0 | 65535 | ||
MEDIUMINT | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
INT | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
0 | 18446744073709551615 |
Source: Integer Types (Exact Value) – INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
So what is it?
For an integer type of column the numbers in the parentheses is called the display width of the field. This is different from what it means for other types of fields. For floating types it is the total number of digits. For character fields it is the maximum number of characters that can be stored, e.g VARCHAR(20) can store 20 characters.
The display width of the column does not affects the maximum value that can be stored in that column. A column with INT(5) or INT(11) can store the same maximum values. Also, if you have a column INT(20) that does not means that you will be able to store 20 digit values (BIGINT values). The column still will store only till the max values of INT.
The number for each type of integer by default is same as the number of characters in the largest negative value of that type. The largest negative value for an integer is -2147483648 which is 11 characters and hence the default display width is 11. Similarly for BIGINT the default display width is 20 which is equal to the number of characters in the largest negative BIGINT (-9223372036854775808).
What is its use?
The display width of an integer type of column does not actually do anything unless the column is an UNSIGNED ZEROFILL. In this case if the number to be stored is less than 11 characters (for INT(11)) then those numbers will be zero-padded on the left. ZEROFILL implicitly makes the column unsigned so it cannot store negative numbers, hence there are no negative numbers in a ZEROFILL column.
Here is an example of how this the display value affect the columns which are UNSIGNED ZEROFILL.
Command to create the table
CREATE TABLE zerofill_demo ( id INT(11) NOT NULL AUTO_INCREMENT, a INT(11) NOT NULL, b INT(11) UNSIGNED ZEROFILL NOT NULL, c INT(5) DEFAULT NULL, d INT(5) UNSIGNED ZEROFILL NOT NULL, e INT(15) DEFAULT NULL, PRIMARY KEY (`id`) )
Commands to insert data into the table:
INSERT INTO zerofill_demo (a, b, c, d, e) VALUES (1, 1, 1, 1, 1); INSERT INTO zerofill_demo (a, b, c, d, e) VALUES (1234567890, 1234567890, 1234567890, 1234567890, 1234567890);
Data as stored in the table
+----+------------+-------------+------------+------------+------------+ | id | a | b | c | d | e | +----+------------+-------------+------------+------------+------------+ | 1 | 1 | 00000000001 | 1 | 00001 | 1 | | 2 | 1234567890 | 01234567890 | 1234567890 | 1234567890 | 1234567890 | +----+------------+-------------+------------+------------+------------+
The above helps use understand the following:
- If the column has ZEROFILL it will always store the number of characters for that column. In our example the column b will always store integers with minimum 11 characters
- The display width does not restrict the values that can be stored in a column. We can still store value till max allowed value of INT in a INT(5) column (columns c and d in our example)
What is the use of display width if the field is not UNSIGNED ZEROFILL
If a column is not UNSIGNED ZEROFILL mysql command line does not uses the display width. However, other applications can use it if they want. Applications can fetch the metadata to get the display width for the column and then use it for something like setting the width of a column or displaying the numbers in a column, etc. Here is an example in PHP that can be used to display values in a table so that they can be right aligned and padded correctly.
$result = $mysqli->query('SELECT e FROM zerofill_demo WHERE id = 1'); $metadata = $result->fetch_field_direct(0); // fetch metadata from first field (e) $length = $metadata->length; // get the length from it $row = $result->fetch_assoc(); printf("%$0{$length}d\n", $row['e']); // prints e in a column 15 chars wide
The above code will output the value of e as
000000000000001
Check out the demo to see how the display width can be used.
View Demo
thank you man that was helpful
Excellent post!
Wow, I didn’t realize that. Thanks!
great !
Great explanation, thanks a lot!
ps: just a little typo i think in php code line 5: %$0{length} -> %0{$length}
Thanks, fixed.
Thanks it was really helpful
Thanks a lot dear.It was a confusion with me from a long time.Great job.