I need some help in creating a MySQL function

This function generates a user id for my user, Which generates 5 digits unique id starting from A0001, A0002, B0001, C0001, and so on but the problem is it reaches F9999 as per my function the following number should be G0000

But my requirement is can't go past letter F We can't have a user id that is more than 5 'digits' and we can only use the letters A to F

Se I come with some Solution moving on to a range that is something like this: AA000, AA001, AA002.... and then AB000, AB001, AB002, AF999 BA000, etc.

This is my current function which I use to generate userid

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getNextID`() RETURNS varchar(10) CHARSET utf8
BEGIN
set @prefix := (select COALESCE(max(left(id, 1)), 'A') from users where left(id, 1) < 1);
set @highest := (select max(CAST(right(id, 4) AS UNSIGNED)) 1 from users where left(id, 1) = @prefix);
if @highest > 9999 then
    set @prefix := CHAR(ORD(@prefix) 1);
    set @highest := 0;
end if;
RETURN concat( @prefix , LPAD( @highest, 4, 0 ) );
END$$
DELIMITER ;

CodePudding user response：

Your ID can be thought of a hexadecimal number consisting of letters only, followed by a decimal number. Each hexadecimal digit starts a new series of decimal numbers, because the ID is of fixed length 5.

The first subproblem is to find the maximum ID, because it should be assumed that F9998 < F9999 < AA000 < AA001. We can calculate H*10000 D with H being the hexadecimal part and D the decimal part of the ID to get the right order.

SELECT id
FROM (
    SELECT 'AB999' as id UNION 
    SELECT 'AA000' UNION
    SELECT 'F9999' UNION
    SELECT 'AAA00' UNION
    SELECT 'FFFF9' UNION
    SELECT 'FFFF8' UNION
    SELECT 'FFFD3') user
ORDER BY conv(regexp_substr(id, '^[A-F]*'), 16, 10) * 10000   CAST(substring(id, length(regexp_substr(id, '^[A-F]*'))   1) AS unsigned) DESC
LIMIT 1;

The second subproblem is to find the successor of a given ID. We calculate the decimal number like above but use the correct factor (10^n with n being the length of the decimal part) this time, then we add one to this number and convert it back to the hex/dec representation. In the hexadecimal part there may be 0s and 1s which have to be replaced by 'A'. Whenever the hex part gets longer, the decimal part consists of 0s only. That is, we can just return a substring of the desired length and strip trailing 0es:

DELIMITER //
CREATE FUNCTION nextId(id VARCHAR(5)) RETURNS VARCHAR(5) NO SQL
BEGIN
  set @hexStr := regexp_substr(id, '^[A-F]*');
  set @digits := length(id) - length(@hexStr);
  set @decimalPart := CAST(right(id, @digits) AS UNSIGNED);
  set @factor := pow(10, @digits);
  set @hexPart := conv(@hexStr, 16, 10);
  set @n := @hexPart * @factor   @decimalPart   1; -- ID increased by 1
  set @decimalPart := mod(@n, @factor);
  set @hexStr := regexp_replace(conv(floor(@n / @factor), 10, 16), '[01]', 'A');
  return substring(concat(@hexStr, lpad(@decimalPart, @digits, '0')), 1, length(id));
END;
//
DELIMITER ;

Using this function

SELECT id, nextId(id) next_id
FROM (
    SELECT 'F9998' as id UNION
    SELECT 'F9999' UNION
    SELECT 'AA999' as id UNION 
    SELECT 'AB000' UNION
    SELECT 'AB999' UNION
    SELECT 'AF999' UNION
    SELECT 'FF999' UNION
    SELECT 'AAA00') user;

results in

Here's a fiddle