MySQL has a feature for numerical types known as zerofill which effects the display size of numerical types. Unlike string types, the number inside the parentheses is not the storage size in characters for the type. For numerical types the type name itself solely determines storage size.
| Column Type | Bytes On Disk | Signed Storage Range | Unsigned Storage Range |
| tinyint | 1 byte | -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 |
So zerofill with default width – the same as int(10):
mysql> create table test_table (t int zerofill);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test_table set t = 10;
Query OK, 1 row affected (0.02 sec)
mysql> select * from test_table;
+————+
| t |
+————+
| 0000000010 |
+————+
1 row in set (0.08 sec)
And without zerofill:
mysql> create table test_table (t int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_table set t = 10;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_table;
+——+
| t |
+——+
| 10 |
+——+
1 row in set (0.01 sec)
A common usage for is creating invoice ids such as INV00000945.
Notes:
If you do not have zerofill specified there is no difference between int(3) and int(10)
When doing comparisons: if compared as integers then the values are the same; if you compare as strings the values are different.