[mysql] What is the benefit of zerofill in MySQL?

I just want to know what is the benefit/usage of defining ZEROFILL for INT DataType in MySQL?

`id` INT UNSIGNED ZEROFILL NOT NULL 

I know I'm late to the party but I find the zerofill is helpful for boolean representations of TINYINT(1). Null doesn't always mean False, sometimes you don't want it to. By zerofilling a tinyint, you're effectively converting those values to INT and removing any confusion ur application may have upon interaction. Your application can then treat those values in a manner similar to the primitive datatype True = Not(0)

It's a feature for disturbed personalities who like square boxes.

You insert

1
23
123 

but when you select, it pads the values

000001
000023
000123

When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

It helps in correct sorting in the case that you will need to concatenate this "integer" with something else (another number or text) which will require to be sorted as a "text" then.

for example,

if you will need to use the integer field numbers (let's say 5) concatenated as A-005 or 10/0005

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.

Above description is taken from MYSQL official website.

mysql> CREATE TABLE tin3(id int PRIMARY KEY,val TINYINT(10) ZEROFILL);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO tin3 VALUES(1,12),(2,7),(4,101);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tin3;
+----+------------+
| id | val        |
+----+------------+
|  1 | 0000000012 |
|  2 | 0000000007 |
|  4 | 0000000101 |
+----+------------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT LENGTH(val) FROM tin3 WHERE id=2;
+-------------+
| LENGTH(val) |
+-------------+
|          10 |
+-------------+
1 row in set (0.01 sec)


mysql> SELECT val+1 FROM tin3 WHERE id=2;
+-------+
| val+1 |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)

One example in order to understand, where the usage of ZEROFILL might be interesting:

In Germany, we have 5 digit zipcodes. However, those Codes may start with a Zero, so 80337 is a valid zipcode for munic, 01067 is a zipcode of Berlin.

As you see, any German citizen expects the zipcodes to be displayed as a 5 digit code, so 1067 looks strange.

In order to store those data, you could use a VARCHAR(5) or INT(5) ZEROFILL whereas the zerofilled integer has two big advantages:

1. Lot lesser storage space on hard disk
2. If you insert 1067, you still get 01067 back

Maybe this example helps understanding the use of ZEROFILL.

ZEROFILL

This essentially means that if the integer value 23 is inserted into an INT column with the width of 8 then the rest of the available position will be automatically padded with zeros.

Hence

23

becomes:

00000023