该解决方案有点冗长,但有效,并且可以轻松适应各种要求。这是一些示例输出:
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 中不允许使用 RAND。所以这个解决方案需要两个db对象,一个view和一个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)