如何生成随机Base36 ID

Question

是否有一种在Server中使用定义的字符计数生成随机 base36标识符的方法？

我搜索并找到了许多将基36转换为int的例子，反之亦然，但不是为了随机生成唯一的ID。

Answer 1

这个解决方案有点冗长，但工作有效，可以很容易地适应各种需求。下面是一些示例输出：

aapx0k  k4fdbb  vzbl5x
8vr1bs  gbix1q  g5kctv
he6e50  m9j0m0  2vz53l
yw72hs  hgbo5h  3oen9v
6t4q75  337670  5sf3h4
yqr35s  xoh4hh  tc0wtf
w7trkj  lnnpdk  zk2ln1
1gt7qr  l6m72n  ja5kvm
kg6f9y  6t3b7a  ujfr0i
2jatgo  0yv8rv  wvbjfa

注意，您需要创建一个视图来包装RAND的使用，这在UDF中是不允许的。因此，该解决方案需要两个db对象，一个视图和一个udf。

CREATE VIEW ViewRandInt AS (SELECT RAND() * 36 as RandInt)
GO

CREATE FUNCTION GetRandomBase36Id
(
    @charCount AS INT
) 
RETURNS VARCHAR(50) AS BEGIN

    DECLARE @characters CHAR(36),
            @result VARCHAR(MAX),
            @counter INT,
            @randNum INT

    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
           @result = '',
           @counter = 0;


    WHILE @counter < @charCount
    BEGIN
        SELECT @randNum = RandInt FROM ViewRandInt
        SET @result = @result + SUBSTRING(@characters, @randNum+1, 1)
        SET @counter = @counter + 1
    END

    RETURN @result;

END

-- Test:
SELECT dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)
, dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6), dbo.GetRandomBase36Id(6)

Answer 2

MySQL版本的Paul的答案：

注意视图和：FLOOR(RandInt * 36) INTO v_randNum中的重要更改

CREATE VIEW `ViewRandInt` AS (select rand() AS `RandInt`);

DROP function IF EXISTS `GetRandomBase36Id`;
DELIMITER $$
CREATE FUNCTION `GetRandomBase36Id`(
    p_charCount INT
) RETURNS varchar(50) CHARSET latin1
BEGIN

    DECLARE v_characters CHAR(36);
    DECLARE v_result LONGTEXT;
    DECLARE v_counter INT;
    DECLARE v_randNum INT;

    SET v_characters = '0123456789abcdefghijklmnopqrstuvwxyz',
           v_result = '',
           v_counter = 0;
           
    WHILE v_counter < p_charCount
    DO
        SELECT FLOOR(RandInt * 36) INTO v_randNum FROM ViewRandInt;
        SET v_result = Concat(v_result , SUBSTRING(v_characters, v_randNum +1, 1));
        SET v_counter = v_counter + 1;
    END WHILE;

    RETURN v_result;

END$$
DELIMITER ;

-- Test
SELECT GetRandomBase36Id(6)
, GetRandomBase36Id(6)
, GetRandomBase36Id(6)
, GetRandomBase36Id(6)
, GetRandomBase36Id(6)
, GetRandomBase36Id(6);