How to Use, Benefits, and Alternative of the Zerofill in MySQL

Today, we will get educated about the use, benefits, and alternative of the ZEROFILL attribute in MySQL.

the Use and Benefit of the ZEROFILL Attribute in MySQL

The benefit of using the ZEROFILL attribute is that it has nothing to do with the input and the data storage, but it only affects how the output value will be displayed. The MySQL automatically adds the UNSIGNED when using the ZEROFILL while column definition.

Whenever we create a table by defining a column (attribute) with the ZEROFILL attribute, it pads the output (resulted value) of a column with zeros on the left up to the display width specified in a column definition.

Example Code:

CREATE TABLE table_one (
    column_one INT(8) ZEROFILL NOT NULL,
    column_two INT(8) NOT NULL
);

INSERT INTO table_one (column_one, column_two) VALUES
(9, 9),
(98, 98),
(987, 987),
(987654321, 987654321);

SELECT column_one, column_two FROM table_one;

Output:

+------------+------------+
| column_one | column_two |
+------------+------------+
|   00000009 |          9 |
|   00000098 |         98 |
|   00000987 |        987 |
|  987654321 |  987654321 |
+------------+------------+
4 rows in set (0.00 sec)

See the above example where the width of the column_one attribute is 8. The values that are longer than the specified display width are not truncated.

See the last record in the above table output, 987654321.

Suppose we store larger values than the display width in the integer field having the ZEROFILL attribute. We may experience issues when MySQL generates the temporary tables for complicated joins.

The MySQL server assumes that the data value fits within the field (column) display width. The columns involved in UNION queries or expressions will ignore the ZEROFILL attribute.

As for MySQL 8.0.17, the ZEROFILL attribute is deprecated for the numeric data types. So, we should look forward to the support for the ZEROFILL attribute because the integer data types’ display widths will be removed in MySQL’s future versions.

Alternative Function of the ZEROFILL Attribute in MySQL

So, we can use the alternatives that produce the same effects for these attributes. For instance, we can use the LPAD() to add zeros on the left up to the display width or store formatted numbers in the CHAR field.

See the following code snippet.

SELECT LPAD(column_one,8,0) FROM table_one;

Output:

+----------------------+
| LPAD(column_one,8,0) |
+----------------------+
| 00000009             |
| 00000098             |
| 00000987             |
| 98765432             |
+----------------------+
4 rows in set (0.001 sec)

We cannot only use the LPAD() to pad zeros on the left of the INT type column but also use it for the VARCHAR() type value as follows.

SELECT LPAD('Hello',8,'??') FROM table_one;

Output:

+----------------------+
| LPAD('Hello',8,'??') |
+----------------------+
| ???Hello             |
| ???Hello             |
| ???Hello             |
| ???Hello             |
+----------------------+
4 rows in set (0.00 sec)