Mysql函数生成自定义id

Question

我需要一些帮助来创建MySQL函数

该函数为我的用户生成一个用户id，它生成从A0001、A0002、B0001、C0001等开始的5位唯一id，但问题是根据我的函数，它达到了F9999，以下数字应该是G0000

但我的要求是不能超过字母F我们的用户id不能超过5个"数字"，我们只能使用字母a到F

Se我带来了一些解决方案，它的范围是这样的：AA000、AA001、AA002……然后是AB000、AB001、AB002、AF999 BA000等。

这是我当前用于生成用户ID 的函数

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 ;

Answer 1

您的ID可以被认为是一个仅由字母组成的十六进制数字，后面跟着一个十进制数字。每个十六进制数字开始一系列新的十进制数字，因为ID的长度固定为5。

第一个子问题是找到最大ID，因为应该假设F9998<F9999<AA000<AA001。我们可以计算H*10000+D，其中H是ID的十六进制部分，D是ID的十进制部分，以获得正确的顺序。

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;

第二个子问题是找到给定ID的后继。我们像上面一样计算十进制数，但这次使用正确的因子(10^n，n是十进制部分的长度(，然后我们将这个数字加一，并将其转换回hex/dec表示。在十六进制部分中，可能有0和1，必须用"A"代替。每当十六进制部分变长时，十进制部分仅由0组成。也就是说，我们可以只返回所需长度的子字符串，并带尾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 ;

使用此功能

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;

中的结果

id	next_id
F9998	F9999
F9999	AA000
AA999	AB000
AB000	AB001
AB999	AC000
AF999	BA000
FF999	AAA00
AAA00	AAA01