Generate zero-fill auto-incremented id in MySQL

This tutorial shows how to generate zero-fill auto-incremented unique identifier in MySQL. Sometimes we need to generate such keys which are used in some part of the application for business logic.

Below example shows how to do that. I have created one table and also created one function. Function returns the zero-fill auto-incremented value from the table. Though I have used zerofill for key_id in the table but while I return the returned value is not actually zero-fill id. Hence it does not matter whether you zero-fill the id or not. But you must return zero-fill id from the function and for that I have used here MySQL LPAD() function.

Let’s see the code

Create table

 

You don’t need to insert these below values. Each time you execute the function the value gets populated in the table.

 

Here is the function which gives us the desired output

 

To execute the function

 

To get each unique identifier you have to execute the above statement everytime.

Thanks for your reading. Please do not forget to leave a comment.

Soumitra

Software Professional, I am passionate to work on web/enterprise application. For more information please go to about me. You can follow on Twitter. You can be a friend on Facebook or Google Plus or Linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.