Last Updated on 24th August 2020 at 06:03 pm
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.
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);
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
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
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.
To get each unique identifier you have to execute the above statement every time.
Thanks for reading.