What is ZEROFILL in MySQL: Explanation and Usage

ZEROFILL in MySQL is a column attribute that pads numeric values with leading zeros up to the column's defined width. It automatically fills empty spaces with zeros when the number is shorter than the specified length, making the output fixed-width and easier to read or sort.

How It Works

Imagine you have a number slot that always wants to show a fixed number of digits, like a digital clock showing 08 instead of 8. ZEROFILL in MySQL works similarly by adding zeros in front of numbers to fill the space defined by the column's width.

For example, if a column is defined as INT(5) ZEROFILL, and you insert the number 42, MySQL will display it as 00042. This helps keep numbers aligned and consistent in reports or user interfaces.

Behind the scenes, ZEROFILL also makes the column unsigned, meaning it cannot store negative numbers.

Example

This example shows how ZEROFILL pads numbers with zeros when stored in a column.

sql

CREATE TABLE example_zerofill (
  id INT(5) ZEROFILL
);

INSERT INTO example_zerofill (id) VALUES (7), (123), (99999);

SELECT * FROM example_zerofill;

Output

+-------+
| id    |
+-------+
| 00007 |
| 00123 |
| 99999 |
+-------+

When to Use

Use ZEROFILL when you want numbers to appear with leading zeros for better visual alignment or formatting, such as invoice numbers, product codes, or IDs that require fixed length.

It is helpful in reports or user interfaces where consistent digit width improves readability. However, avoid using it for calculations since the zeros are only for display.

Also, remember that ZEROFILL makes the column unsigned, so it cannot store negative values.

Key Points

• ZEROFILL pads numeric values with leading zeros to match the column width.
• It automatically makes the column unsigned (no negative numbers allowed).
• Useful for fixed-width numeric displays like IDs or codes.
• Does not affect the stored numeric value, only how it is displayed.
• Not recommended for columns where negative numbers or calculations are needed.

Key Takeaways

ZEROFILL pads numbers with leading zeros to a fixed width for consistent display.

It makes the column unsigned, so negative numbers are not allowed.

Ideal for formatting IDs, codes, or numbers that need fixed-length appearance.

The padding affects only display, not the actual stored value.

Avoid using ZEROFILL for columns requiring negative values or arithmetic.