Introduction

Adding leading zeros in MySQL is usually a formatting problem, not a numeric one. The correct approach depends on whether the zeros are just for display or whether they are a real part of the stored identifier.

That distinction matters because numeric columns do not preserve formatting. If you store 7 in an INT, MySQL stores the number 7, not the text 0007.

Use LPAD for formatted query output

If you only need zeros in reports, exports, or UI queries, use LPAD. It pads the value on the left until it reaches the target width.

SELECT code, LPAD(code, 4, '0') AS formatted_code
FROM products;

Examples:

• '7 becomes 0007'
• '85 becomes 0085'
• '1234 stays 1234'

That last case is important. LPAD does not break values that are already wide enough, so it naturally handles the "only some values" requirement when the rule is based on length.

Update stored values only when the column is text

If the leading zero is part of a business identifier, the column should usually be CHAR or VARCHAR, not INT. Then you can persist the padded value:

UPDATE products
SET code = LPAD(code, 6, '0')
WHERE CHAR_LENGTH(code) < 6;

This makes sense for identifiers such as:

• product codes
• invoice numbers
• external reference IDs
• postal-like codes

It does not make sense for true quantities or values that will be used for arithmetic.

Numeric columns discard the formatting

This is the point that causes most confusion. A numeric type stores the numeric value only.

1CREATE TABLE demo (
2    code INT
3);
4
5INSERT INTO demo (code) VALUES (0007);
6SELECT code FROM demo;

The result is still 7.

So if the requirement says "show a leading zero", keep the column numeric and format at query time. If the requirement says "the code itself is 0007", make the column textual.

Use a view when many queries need the same format

If several reports need the padded value, putting the formatting into a view can keep the application code cleaner.

1CREATE VIEW product_codes AS
2SELECT
3    id,
4    code,
5    LPAD(code, 6, '0') AS display_code
6FROM products;

Then consumers can query:

SELECT id, display_code
FROM product_codes;

This keeps the storage model simple while exposing a consistent presentation format.

What about ZEROFILL?

Older MySQL examples often use ZEROFILL for integer display. It can work, but it ties presentation to the schema in a way that is less flexible than explicit formatting in queries.

CREATE TABLE demo (
    code INT(5) ZEROFILL
);

Selecting a value may display as 00007, but this is still display-oriented behavior, not a different stored number. In most modern applications, LPAD is clearer because the formatting rule stays visible in the query or view where it is actually needed.

Common Pitfalls

The most common mistake is trying to update an INT column and expecting the leading zeros to remain in storage. They will not.

Another issue is rewriting stored data when the business requirement was only about presentation. That creates string-like identifiers unnecessarily and complicates sorting and validation.

Developers also reach for ZEROFILL because it looks convenient, but it is often less explicit than formatting with LPAD in the query layer.

Finally, make sure the width you choose is really a business rule. Padding a code to six characters is harmless only if all consumers agree on that width.

Summary

• Use LPAD when you want leading zeros in query output.
• Keep numeric columns numeric when the zeros are only a display concern.
• Store padded values only in text columns when the zeros are part of the identifier.
• Do not expect INT columns to preserve leading zeros.
• Prefer clear query-time formatting over schema-level display tricks in most cases.