What is the benefit of zerofill in MySQL?

Question

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

`id` INT UNSIGNED ZEROFILL NOT NULL 

Empress

Readers should also note that zerofill is nonstandard. "The ZEROFILL attribute is ignored when a column is involved in expressions or UNION queries. If you store values larger than the display width in an integer column that has the ZEROFILL attribute, you may experience problems when MySQL generates temporary tables for some complicated joins. In these cases, MySQL assumes that the data values fit within the column display width." – Ephesian

Good question. Related questions that could also be asked: Are there good reasons not to use ZEROFILL? What are the drawbacks and potential pitfalls of using ZEROFILL? Do the benefits outweigh the drawbacks? – Silverman

See the explanation #5634604 – Stoa

short explanation: tutorialspoint.com/what-is-the-benefit-of-zerofill-in-mysql – Bijugate

Answer 1

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        123
123456789  123456789

Plugboard

@diEcho: If for example you want all your invoice numbers to be displayed with 10 digits then you can declare the type of that column as INT(10) ZEROFILL. – Plugboard

I highly recommend you stay away from this feature - how a numerical value is displayed/formatted is a presentation concern, and absolutely not something that belongs at the database-level; at least not if you're using the database to back a piece of software. Be aware that this can cause problems - if you parse a value with a leading zero as an integer, many parsers will regard the value as octal, which is probably not what you want. The only time you should use this feature, is as a (storage) optimization, when what you're actually storing is effectively a (fixed-length) string of digits. – Fusillade

@mindplay.dk: Depends. If you're storing something like a GTIN, they have to be 14 digits long but can also be just 8 digits and left-padded with zeros. Relying on the output side wouldn't be right in this case, because it's not simply a decimal, but a key. – Subsolar

@MarkByers, Practically speaking though, wouldn't most client-libraries (e.g. PHP) simply strip the zeros off before they hand it over to the application code? If so, then it really seems somewhat pointless. A bad design in the early days of MySQL. – Ephesian

@MarkByers I have the same question as @Pacerier. For all practical purposes, an int column would map to a numeric type in the application language. Wouldn't display properties be ignored? I can't think of a numeric type in Javascript, C# or Java that comes with display attributes. – Ress

"pads the displayed value of the field with zeros up to the display width specified in the column definition". Funny, I've never thought of the length as the "display width" before. I thought this gets changed / set automatically based on the integer type you choose, and so I thought it had more to do with the actual size than a desired display width. Although I have always used PhpMyAdmin and left it blank except when using char or varchar. – Beggary

Answer 2

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.

Teocalli

It's worth adding that the SQL client displaying the data is responsible for formatting the numbers with leading zeros. This is not something that "automagically" happens with each and every application tretrieving the data. – Tribe

Fixed width columns are also better, because they reduce on-disk fragmentation. – Subsolar

@Phil, Practically speaking though, wouldn't most client-libraries (e.g. PHP) simply strip the zeros off before they hand it over to the application code? If so, then it really seems somewhat pointless. A bad design in the early days of MySQL. – Ephesian

@Ephesian This depends on how you want the data in your client library to be represented: As number or as string. A german zipcode is not a number, it's a string consisting of digits, only. Therefore I disagree with your statement of bad design in early days of MySQL. It's still a valid approach but for rare cases. – Teocalli

@Ephesian PHP will by default retrieve all values as strings when working with native extensions like mysqli but you can use mysqli::options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE) to retrieve the integer and float values as numbers. – Important

Answer 3

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

Schaffhausen

Answer 4

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

Vincevincelette

Answer 5

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)

Hirst

Thanks, but unfortunately I just ran some tests against a mysql db and it doesnt zerofill null values :-/. That goal is still accomplished by constraining the field to not allow null. I should know better than to try and answer questions about dbs i dont usually use. Gonna delete my answer before people get too downvote happy. lol – Hirst

Useful? It's incorrect. Zerofill does nothing (except add Unsigned) when the display length is 1. – Unsound

@Hirst and you should always set a default value for true or false... if you don't use default values then the problem you mentioned is NOT going to be the only one... NOT NULL is a must as well :) – Recreation

I would downvote but your rep is at 666 so I'd prefer to keep this answer as is ;-) – Proterozoic

Answer 6

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)

Leddy

Answer 7

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

Ryun

Nice and simple explanation! – Medarda

Answer 8

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

Campinas

the link would be dev.mysql.com/doc/refman/5.0/en/numeric-type-attributes.html instead – Hachure

Answer 9

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.

Boettcher