What is the benefit of zerofill in MySQL?

When you select a column with type ZEROFILL it pads the displayed value of the field with zeros up to the display width specified in the column definition. Values longer than the display width are not truncated. Note that usage of ZEROFILL also implies UNSIGNED.

Using ZEROFILL and a display width has no effect on how the data is stored. It affects only how it is displayed.

Here is some example SQL that demonstrates the use of ZEROFILL:

CREATE TABLE yourtable (x INT(8) ZEROFILL NOT NULL, y INT(8) NOT NULL);INSERT INTO yourtable (x,y) VALUES(1, 1),(12, 12),(123, 123),(123456789, 123456789);SELECT x, y FROM yourtable;

Result:

        x          y 00000001          1 00000012         12 00000123        123123456789  123456789

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.

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

You insert

123123 

but when you select, it pads the values

000001000023000123