Eric Bergen

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.

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

This entry was posted on Friday, July 29th, 2005 at 7:26 pm and is filed under Geek, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.