MySQL int(11) and int(3) store the same values.

MySQL has a little know feature for numerical types known as zerofill. This feature effects the display size of numerical types. Unlike the string types the number inside the parentheses is not the storage size in characters for the type. For numerical types the type name itself determines storage size.

Column Type Bytes On Disk Signed Storage Range Unsigned Storage Range
tinyint 1 bytes -128 to 127 0 to 255
smallint 2 bytes -32768 to 32767 0 to 65535
mediumint 3 bytes -8388608 to 8388607 0 to 16777215
int 4 bytes -2147483648 to 2147483647 0 to 4294967295
bigint 8 bytes -9223372036854775808 to 9223372036854775807 0 to 18446744073709551615

The confusion between types comes from the number inside parentheses for different types. The integer type it’s the padding size for zerofill. The following examples demonstrates zerofill. All of these tables store the same range of values since they are all integer type.

Zerofill with padding specified:

mysql> create table t (t int(3) zerofill);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+——+
| t |
+——+
| 010 |
+——+
1 row in set (0.11 sec)

Zerofill with default width, the same as int(10):

mysql> create table t (t int zerofill);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+————+
| t |
+————+
| 0000000010 |
+————+
1 row in set (0.08 sec)

Without zerofill:

mysql> create table t (t int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t set t = 10;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+——+
| t |
+——+
| 10 |
+——+
1 row in set (0.00 sec)

One common usage for this is creating invoice ids. It saves the work of having to use lpad() for ids like this ‘UP000009′.

16 Comments

  1. Matt says:

    I think you’ve got signed and unsigned backwards in your table. Just thought I’d let you know…

  2. Thomas says:

    Thanks Eric, exactly the info I was looking for!

  3. ignacio says:

    Exactly, I was wondering what was the purpose for the number between parenthesis.
    Great

  4. shelley says:

    In the previous sample with int(11) and int(3) with an initial value of 10, what comparison these two values should yield? ie Are the values the same or different in a where clause?

  5. Eric Bergen says:

    It depends on the comparison. If you compare them as integers then it’s the same. If you compare them as strings they aren’t.

  6. Great. I was looking for the same info. I was wondering if I could find an answer to this question. Thanks

  7. ben says:

    Was a bit confused about the value between parentheses. Cleared up now!

  8. Mark says:

    Just pointing out the following (perhaps obvious) detail:

    The query time increased with more “padding” and decreased with less.

  9. [...] caro Peter è sfuggito un piccolo dettaglio, infatti come spiega Eric Bergen, i valori memorizzabili in un campo di questo tipo sono da -2,147,483,648 a [...]

  10. laalesh says:

    it would be better if you would had privided an example on signed and unsigned.

  11. noel says:

    Hi,

    So if there is no zerofill it doesnt make a difference?

    If you have int(3) with 10000 it is still 10000, but there are no leading zeroes with zerofill?

    Thx Noel

  12. Eric Bergen says:

    Correct, if you don’t specify zerofill it doesn’t matter.

  13. doucol says:

    Perfect, simple and concise blog article. thanks much

  14. mysql expert says:

    The diffrerence is only in display for mysql.It displays 1 or 0000000001.Nothing changes about data range for mysql int data type

  15. Neha Vishwakarma says:

    Explained through examples, which is very easy to understand. Not even explained on MYSQL website
    http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html.
    Thanks

Leave a Reply