What does int(11) means in MySQL?

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.

1 2 3 4 5

$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.


In this demo we will see how to create a table with unsigned zerofill to understand more about what int(11) means. We will also see how data is stored in the table and how to display it.

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

Fetch and display the data

Queries

$result = mysqli_query($link, '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();

Display the data:

Command	Output	Description
printf("%s", $row['e'])	1	prints value of column e as a standard string
printf("%0{$length}s", $row['e'])	000000000000001	prints value of e in a column 15 chars wide with zero-padding
printf("%{$length}s", $row['e'])	1	prints value of e in a column 15 chars wide
printf("%'#{$length}s", $row['e'])	##############1	prints value of column e with custom padding character '#'