Zero-Fill Auto-Incremented Id

This tutorial shows how to generate zero-fill auto-incremented unique identifier in MySQL. Sometimes you may 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 8.0.17 – 8.0.26

MySQL Table

I am creating below table where you 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 unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`key_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

You don’t need to insert these below values and you get these values each time you call the function which you will see in the next section. Each time you execute the function the value gets populated in the table automatically as well.

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

MySQL Function

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

Though I have used AUTO_INCREMENT for the column key_id in the table but the returned value is not actually zero-filled id. Hence it does not matter whether you make the key_id column AUTO_INCREMENT or not when you use zerofill in older than MySQL version 8. In MySQL version 8 onward, the zerofill has been deprecated and if you use you will see warning. But as per the example I am explaining here 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 //
CREATE DEFINER=`root`@`localhost` FUNCTION `get_key`() RETURNS varchar(4) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    DETERMINISTIC
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 to generate auto-incremented zero-filled id.

SELECT get_key();

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

Source Code

Download