Learn MySQL with the Curriculum Team

  • March 26, 2014

What does the 11 mean in INT(11)?

Jeremy Smyth
Manager, MySQL Curriculum

If you create a table using a numeric type like INT or BIGINT, you might have been surprised by the numbers that appear in the table's DESCRIBE:

Query OK, 0 rows affected (1.04 sec)
mysql> DESCRIBE test;
| Field | Type        | Null | Key | Default | Extra |
| a     | int(11)     | YES  |     | NULL    |       |
| b     | smallint(6) | YES  |     | NULL    |       |
| c     | bigint(20)  | YES  |     | NULL    |       |
3 rows in set (0.03 sec)

I didn't put those values 11, 6, and 20 in there. Where did they come from and what are they?

They're the columns' "Display Width"

Well, for an integer type (the value in parentheses called the display width of the field. This is different from (and somewhat less intuitive than) the parenthesised value in character fields—such as VARCHAR(10)—where it's the maximum number of characters you can store in the field, and for floating types where it describes the total number of digits you can store. The display width for integers... well it doesn't seem to do much really, on the surface.

An example 

Here's an example, using BIGINT because they're, well, biggest:

mysql> CREATE TABLE d1(c1 BIGINT(5), c2 BIGINT, c3 BIGINT(30));
Query OK, 0 rows affected (0.78 sec)
mysql> DESC d1;
| Field | Type       | Null | Key | Default | Extra |
| c1    | bigint(5)  | YES  |     | NULL    |       |
| c2    | bigint(20) | YES  |     | NULL    |       |
| c3    | bigint(30) | YES  |     | NULL    |       |
3 rows in set (0.00 sec) 
The c1 and c3 columns use explicit display widths. The c2 column uses the default display width, which is just enough to contain the largest amount of characters in a BIGINT (including the sign). The largest negative BIGINT is -9223372036854775808, which if you count carefully you'll see is 20 characters. For similar reason, the default display with of an INT (with largest negative value -2147483648, go on, count 'em) is 11, and so on.
mysql> INSERT INTO d1 VALUES (1, 1, 1);
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO d1 VALUES (123456, 123456, 123456);
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM d1;
| c1     | c2     | c3     |
|      1 |      1 |      1 |
| 123456 | 123456 | 123456 |
2 rows in set (0.00 sec)
You can see from this that the display width has no effect on the output at all, at least when you use the mysql command-line client. We'll come back to this idea later.

So what's it for?

The numeric type overview calls this mysterious value "the maximum display width for integer types," and continues: "Display width is unrelated to the range of values a type can contain."

To see an effect, you can pad the columns with zeroes:

    -> c3 BIGINT(30) ZEROFILL);
Query OK, 0 rows affected (0.67 sec)

Note that ZEROFILL implicitly makes the column unsigned so it cannot store negative numbers. You couldn't, for example, see a number like -000123 in such a column.

The ZEROFILL option fills up the return value with zeros, as you might have guessed. It turns numbers like 123 into numbers like 000123, assuming your display width is 6. Let's see what it means in our table:

mysql> INSERT INTO d2 VALUES (1, 1, 1);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO d2 VALUES (123456, 123456, 123456);
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM d2;
| c1     | c2                   | c3                             |
|  00001 | 00000000000000000001 | 000000000000000000000000000001 |
| 123456 | 00000000000000123456 | 000000000000000000000000123456 |
2 rows in set (0.00 sec)

So it should be clear that the display width does not limit the amount of values you store in the column—you still get 123456 in a column with a display width of 5—but simply put, it affects how the values appear when they're padded.

What's the point if it doesn't work without ZEROFILL?

Ah, but it does. Well, it does if you want it to. The mysql command-line client doesn't use the display width unless the field is full of zeroes, but other client applications can (and do). The display width is available to applications through the API, so they can use it to pad (with spaces, dashes, or whatever you like) the values.

For example, in PHP you could do something like this:

$result = $mysqli->query('SELECT c3 FROM d1'); 
$c3metadata = $result->fetch_field_direct(0); // fetch metadata from first field (c3)
$c3length = $c3metadata->length;              // get the length from it (30)
$row = $result->fetch_assoc();
printf("%${c3length}d\n", $row['c3']);        // prints c3 in a column 30 chars wide



This code reads the display width of the c3 column (which we know is 30, from the code above) from the column's metadata into a variable $c3length, and uses that value to provided a width to the format specifier %d (for decimal integer), so you get the expression %${c3length}d, which evaluates as %30d. This prints the value of $row['c3'] as an integer in a field 30 characters wide, right justified and space-padded.

The same sort of code exists in other languages; in Java, the java.sql.ResultSetMetaData interface provides the getColumnDisplaySize method, and Java's String.format method works similarly to the printf code above.

In short... 

  • You can ignore the display widths if you don't use them. The defaults are enough to display any value compatible with the type, and won't cause you trouble if your application uses the display width without your knowledge.
  • If you need to zero-pad your numbers, display width lets you say how much padding you need, but you'll need to make sure your application deals with number larger than that width either by ensuring it can handle them, or that your business logic prevents them.
  • If you want to display space-padded numbers in plaintext reports or other fixed-width output formats and you want to store the column's display width with the other column metadata at the database (and where else would you put it?), use the display width.


Join the discussion

Comments ( 5 )
  • Vincent Wednesday, March 26, 2014

    "and where else would you put it?"

    In the query that reads the data for the report, of course, formatting is an application-side thing, it does not belong in the DDL.

    The spaces and zeros have no purpose inside the database or any application using them, so it is far better to add them manually in the handful of cases where you do need them.

  • guest Thursday, June 4, 2015

    INSERT INTO d1 VALUES (123456, 123456, 123456);

  • Jeremy Thursday, June 4, 2015

    Thank you. I've fixed the post.

  • guest Monday, January 23, 2017

    Congrats, the best explanation ever read for that question...

  • Dickson Wednesday, August 22, 2018
    Very helpful... thanks.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Integrated Cloud Applications & Platform Services