{"id":1997,"date":"2016-08-10T21:21:00","date_gmt":"2016-08-11T04:21:00","guid":{"rendered":"https:\/\/www.virendrachandak.com\/techtalk\/?p=1997"},"modified":"2019-09-22T15:23:29","modified_gmt":"2019-09-22T22:23:29","slug":"mysql-int11-what-does-it-means","status":"publish","type":"post","link":"https:\/\/www.virendrachandak.com\/techtalk\/mysql-int11-what-does-it-means\/","title":{"rendered":"What does int(11) means in MySQL?"},"content":{"rendered":"

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.
\n
\nThe 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.<\/p>\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Type<\/th>\nStorage<\/th>\nMinimum Value<\/th>\nMaximum Value<\/th>\n<\/tr>\n
<\/th>\n(Bytes)<\/th>\n(Signed\/Unsigned)<\/th>\n(Signed\/Unsigned)<\/th>\n<\/tr>\n<\/thead>\n
TINYINT<\/td>\n1<\/td>\n-128<\/td>\n127<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n0<\/td>\n255<\/td>\n<\/tr>\n
SMALLINT<\/td>\n2<\/td>\n-32768<\/td>\n32767<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n0<\/td>\n65535<\/td>\n<\/tr>\n
MEDIUMINT<\/td>\n3<\/td>\n-8388608<\/td>\n8388607<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n0<\/td>\n16777215<\/td>\n<\/tr>\n
INT<\/td>\n4<\/td>\n-2147483648<\/td>\n2147483647<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n0<\/td>\n4294967295<\/td>\n<\/tr>\n
BIGINT<\/td>\n8<\/td>\n-9223372036854775808<\/td>\n9223372036854775807<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n0<\/td>\n18446744073709551615<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Source: Integer Types (Exact Value) – INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT<\/a><\/p>\n

So what is it?<\/h3>\n

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.<\/p>\n

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.<\/p>\n

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).<\/p>\n

What is its use?<\/h3>\n

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.<\/p>\n

Here is an example of how this the display value affect the columns which are UNSIGNED ZEROFILL.<\/p>\n

Command to create the table<\/h4>\n
CREATE TABLE zerofill_demo (\r\n\tid INT(11) NOT NULL AUTO_INCREMENT,\r\n\ta INT(11) NOT NULL,\r\n\tb INT(11) UNSIGNED ZEROFILL NOT NULL,\r\n\tc INT(5) DEFAULT NULL,\r\n\td INT(5) UNSIGNED ZEROFILL NOT NULL,\r\n\te INT(15) DEFAULT NULL,\r\n\tPRIMARY KEY (`id`)\r\n)<\/pre>\n
<\/p>\n
Commands to insert data into the table:<\/h4>\n
INSERT INTO zerofill_demo (a, b, c, d, e) VALUES (1, 1, 1, 1, 1);\r\nINSERT INTO zerofill_demo (a, b, c, d, e) VALUES (1234567890, 1234567890, 1234567890, 1234567890, 1234567890);<\/pre>\n
Data as stored in the table<\/h4>\n
\r\n+----+------------+-------------+------------+------------+------------+\r\n| id | a          | b           | c          | d          | e          |\r\n+----+------------+-------------+------------+------------+------------+\r\n|  1 |          1 | 00000000001 |          1 |      00001 |          1 |\r\n|  2 | 1234567890 | 01234567890 | 1234567890 | 1234567890 | 1234567890 |\r\n+----+------------+-------------+------------+------------+------------+\r\n<\/pre>\n
\nThe above helps use understand the following:<\/p>\n