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

CREATE TABLE `key_generator` (
  `key_id` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`key_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

 

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

insert  into `key_generator`(`key_id`) values (0001),(0009),(0010),(0011),(0012),(0013),(0014),(0015),(0016),(0017),(0018),(0019),(0020);

 

Here is the function which gives us the desired output

DELIMITER $$
USE `cdcol`$$
DROP FUNCTION IF EXISTS `get_key`$$
CREATE
    FUNCTION `cdcol`.`get_key`()
    RETURNS VARCHAR(4)
    BEGIN
    DECLARE gen_key VARCHAR(4);
    INSERT INTO key_generator() VALUE();
    SELECT CONVERT(MAX(key_id),CHAR(4)) INTO gen_key FROM key_generator;
    RETURN LPAD(gen_key,4,'0');
    END$$
DELIMITER ;

 

To execute the function

SELECT get_key();

 

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 Roy Sarkar

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials Twitter Facebook  Google Plus Linkedin

Leave a Reply

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