JIYIK CN >

In this article we will understand the uses, advantages and alternatives of ZEROFILL attribute in MySQL.

Use and benefits of the ZEROFILL attribute in MySQL

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

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

Sample 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 example above, where the column_one attribute has a width of 8. Values ​​longer than the specified display width are not truncated.

Look at the last record in the table output above, 987654321.

Suppose we store values ​​larger than the display width in an integer field with the ZEROFILL attribute. We may encounter problems when MySQL generates temporary tables for complex joins.

The MySQL server assumes that the data value fits within the field (column) display width. Columns involved in a UNION query or expression ignore the ZEROFILL attribute.

As of MySQL 8.0.17, the ZEROFILL attribute of numeric data types is deprecated. So, we should expect support for the ZEROFILL attribute as the display width of integer data types will be removed in a future version of MySQL.

Alternative functionality for the ZEROFILL attribute in MySQL

Therefore, we can use alternatives that produce the same effect on these properties. For example, we can use LPAD() to add zeros on the left until the display width or to store formatted numbers in a 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)

Not only can we use LPAD()to fill zeros on the left side of INT type columns, we can also use it for VARCHAR()type values ​​as shown below.

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

Output:

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