ZEROFILL and UNSIGNED in MySQL - A Practical Explanation

In several years working with MySQL, for a long time, I didn't notice the ZEROFILL and UNSIGNED attributes of MySQL until I needed to use ZEROFILL for an application.

What are these attributes? UNSIGNED is an optional attribute for integers in MySQL used to define only positive numbers. ZEROFILL is used to fill an integer with zeros so that all the numbers in the column have the same number of digits.

Given what they are, unsigned means "no sign", i.e. without the minus sign (-), so it only stores positive numbers. While zero-fill is used more specifically to fill with zeros on the left in this case.

Why use UNSIGNED

Many developers are not interested in adjusting attributes like this since even if they are wrong, most of the time they don't break the application.

But anyway, there is always the right way to go, and in this case, the most optimized way will give us more consistency in our database and save space for a larger volume of data.

Always use UNSIGNED to represent whole values that will never be negative. For example, an id AUTO_INCREMENT that starts at 1 will never be negative.

The main advantage of using this attribute is that MySQL will not allocate unnecessary space for negative numbers from -2147483648 to zero, and will double the space for positive numbers from zero to 4294967295. Source: MySQL.

Why use ZEROFILL

The role of the ZEROFILL attribute is only to indicate to MySQL how the information should be shown. It does not affect how the data is stored.

If you need to do a WHERE 0001=1, SQL will still work as expected.

Therefore, for aesthetic purposes only, you can use this attribute to always have consistency in the digits of the integers of your application. Nothing that can't be solved in the frontend in my opinion, but if you want to keep the view of the columns with data in the format 00010, 00011, 00012, etc. it's valid to use this approach.

Number of zeros with ZEROFILL

To specify how many leading zeros you want to show, you need to specify the column size when creating or changing a table, for example: INT(5). See the next paragraphs for how to create or change a table.

If a number is greater than 5, the number will not be cut off, but will be displayed normally with 6 digits or more. And if it's smaller, it'll be filled with zeros.

This behavior of INT(5) is only for viewing purposes because an integer is always the same size, but I understand that it is totally different from a VARCHAR(5), where the string is cut off and I agree this is kind of confusing in MySQL.

Modifying an existing table

To change the UNSIGNED or ZEROFILL attributes in a table you have already created, use the ALTER TABLE syntax:

ALTER TABLE table_name MODIFY COLUMN column_name INT { NOT NULL | UNSIGNED | ZEROFILL | DEFAULT };

UNSIGNED

For example, the command below will change the table and column number to UNSIGNED. Keeping the data if the column was already of type INT.

Warning: You must also modify DEFAULT, NOT_NULL, and AUTO_INCREMENT if applicable. If you don't specify these attributes they will be modified to the MySQL default. Consider testing with a test table before changing important data.

Warning 2: Any negative number stored in this column will be converted to "0", so use it with caution.

ALTER TABLE table_name MODIFY column_name INT UNSIGNED NOT NULL;

ZEROFILL

As stated above, when a column has ZEROFILL it automatically has UNSIGNED checked.

Note that the only difference in the example to change to unsigned is that I used the size/length in INT, with 5 digit width. This does not mean that you will be able to enter numbers up to 5 digits or you will lose data greater than 99999, but it just means that MySQL will always try to fill in zeros until it reaches 5 digits, e.g. 00123.

Warning: You must also modify DEFAULT, NOT_NULL, and AUTO_INCREMENT if applicable. If you don't specify these attributes they will be modified to the MySQL default. Consider testing with a test table before changing important data.

Warning 2: Any negative number stored in this column will be converted to "0" because of the automatic unsigned conversion, so use it with caution.

ALTER TABLE table_name MODIFY column_name INT(5) ZEROFILL NOT NULL;

Creating columns with ZEROFILL and UNSIGNED

This example is for reference purposes only, as we do the same thing as in the ALTER TABLE.

To create a table specifying these attributes is easier than modifying an existing one because you don't have to worry about data loss (in case of negative numbers).

Below is the code to create a table using ZEROFILL and consequently UNSIGNED. To change the formatting size of the zeros, change the display size of the integer in INT(5):

CREATE TABLE table_name (column_name INT(5) ZEROFILL);

To create an UNSIGNED only column:

CREATE TABLE table_name (column_name INT(5) UNSIGNED);