Category Archives: MySQL

Zerofill in MySQL databases

I have had this problem for a long time and there are barely, if at all any, resources on the Internet that can aid in resolving this issue. I came across “Zerofilling” when creating my backend MySQL database for brute force attacks. To make it a lot easier to query the primary keys in my database, I felt it necessary to populate the field with nine digits off the bat. Rather than having to wait until there are 99,999,999 entries in my database before the primary key finally utilised all nine digits assigned to the primary key, the zerofill option will fill the a zero in every place the number currently in the primary key doesn’t physically take up. So, instead of having 1, 2, 3…. 56, 57…. 1098, 1099 as your primary keys, you will now have 000001, 000002, 000003…. 000056, 000057…. 001098, 001099. Which in my opinion looks a lot better and allows querying to be tailored to search for six digits, rather than 1 to 6 digits.

mysqlSyntaxError

The problems with zerofilling fields occurs when initially creating the table. I have encountered numerous posts on the Internet where users are constantly stuck with Syntax errors in their MySQL code with no clue as to why it is happening as you can see above.

I noticed something odd when looking back at the table I created for my dissertation and where the unsigned zerofill was located when I “described” the table.

describeTable

As you can see, the unsigned zerofill is associated with the data type that is assigned to that particular tuple. By rearranging the previous MySQL statement when I was getting the syntax error, I can now create the table without any errors by using the following statement:

create table Mastah (P_ID int(6) unsigned zerofill primary key auto_increment not null);

This conclusion or workaround, as far as I know, as not been detailed on any forums but you now have the solution to the problem =)

-M^