SQL ZEROFILL

What is SQL ZEROFILL?

ZEROFILL is a MySQL column attribute that left-pads numeric values with leading zeros up to the column’s declared display width and implicitly sets the column to UNSIGNED.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ZEROFILL:

SQL ZEROFILL Full Explanation

ZEROFILL is specified in a column definition for numeric data types (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE). When selected, numbers are returned as strings padded on the left with zeros so the total length equals the column’s display width. The attribute changes only the way data is displayed; internal storage remains unchanged. Because negative numbers would conflict with zero padding, MySQL automatically applies the UNSIGNED attribute whenever ZEROFILL is used.In MySQL 8.0 the numeric display width feature is deprecated and will be removed in a future release, but ZEROFILL continues to work as long as a display width is present. Migrating code to string functions such as LPAD is recommended for future-proofing.

SQL ZEROFILL Syntax

CREATE TABLE table_name (
  column_name data_type(display_width) ZEROFILL
);

SQL ZEROFILL Parameters

Example Queries Using SQL ZEROFILL

-- Create a table with an account number padded to 6 digits
CREATE TABLE accounts (
  acct_no INT(6) ZEROFILL,
  balance DECIMAL(10,2)
);

INSERT INTO accounts (acct_no, balance) VALUES (42, 250.75), (1234, 19.99);

-- Query the data
SELECT acct_no, balance FROM accounts;

Expected Output Using SQL ZEROFILL

acct_no displays as 000042 and 001234 even though the stored numeric values are 42 and 1234
balance shows normal numeric formatting

Use Cases with SQL ZEROFILL

Displaying fixed-length account, order, or invoice numbers without converting to CHAR
Ensuring numeric identifiers align lexicographically when sorted as text
Quickly prototyping legacy schemas that expect zero-padded numbers

Common Mistakes with SQL ZEROFILL

Assuming ZEROFILL changes the stored value; it only affects display
Forgetting that ZEROFILL forces the column to UNSIGNED, which rejects negative inserts
Using ZEROFILL without specifying a display width (required)
Relying on ZEROFILL in new code despite deprecation in MySQL 8.0 and later

First Introduced In

MySQL 3.23

Frequently Asked Questions

What is the purpose of ZEROFILL?

ZEROFILL pads numeric values with leading zeros to reach the specified display width and automatically sets the column to UNSIGNED.

Is ZEROFILL still safe to use in MySQL 8.0?

The feature is deprecated in MySQL 8.0. It still works but may be removed, so migrate to LPAD or store identifiers as CHAR.

Why did my column become UNSIGNED after adding ZEROFILL?

MySQL implicitly applies UNSIGNED when ZEROFILL is present to avoid conflicts between negative signs and leading zeros.

How can I emulate ZEROFILL in future MySQL versions?

Use LPAD(CONVERT(num_col, CHAR), width, '0') in SELECT statements or store the identifier in a CHAR/VARCHAR column.