Introduction

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 generate zero-fill auto-incremented id in MySQL table.

Prerequisites

MySQL 5.6

Creating MySQL Table

We are creating below table where we have only one column but in real application you may have more columns along with this primary column.

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 below function the value gets populated in the table automatically.

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

Creating MySQL Function

Here is the function which gives us the desired output. This function returns the zero-fill auto-incremented value from the table.

Though I have used zerofill AUTO_INCREMENT for the column key_id in the table but the returned value is not actually zero-fill id. Hence it does not matter whether you make the key_id column zero-fill AUTO_INCREMENT or not. But you must return zero-filled values from the function for the column key_id and for that I have used here MySQL’s LPAD() function.

DELIMITER $$
USE `roytuts`$$
DROP FUNCTION IF EXISTS `get_key`$$
CREATE
    FUNCTION `roytuts`.`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 ;

Testing the Function

Execute the function using below statement.

SELECT get_key();

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

Thanks for reading.

Tags:

Leave a Reply

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