使用 T-SQL 生成随机字符串

问题描述

如果您想使用 T-SQL 生成伪随机字母数字字符串，您会怎么做?您将如何从中排除美元符号、破折号和斜线等字符?

If you wanted to generate a pseudorandom alphanumeric string using T-SQL, how would you do it? How would you exclude characters like dollar signs, dashes, and slashes from it?

推荐答案

在生成随机数据时，专门用于测试，使数据随机化，但可重现非常有用.秘诀是为随机函数使用显式种子，这样当使用相同的种子再次运行测试时，它会再次产生完全相同的字符串.下面是一个以可重现方式生成对象名称的函数的简化示例:

When generating random data, specially for test, it is very useful to make the data random, but reproducible. The secret is to use explicit seeds for the random function, so that when the test is run again with the same seed, it produces again exactly the same strings. Here is a simplified example of a function that generates object names in a reproducible manner:

alter procedure usp_generateIdentifier
    @minLen int = 1
    , @maxLen int = 256
    , @seed int output
    , @string varchar(8000) output
as
begin
    set nocount on;
    declare @length int;
    declare @alpha varchar(8000)
        , @digit varchar(8000)
        , @specials varchar(8000)
        , @first varchar(8000)
    declare @step bigint = rand(@seed) * 2147483647;

    select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
        , @digit = '1234567890'
        , @specials = '_@# '
    select @first = @alpha + '_@';

    set  @seed = (rand((@seed+@step)%2147483647)*2147483647);

    select @length = @minLen + rand(@seed) * (@maxLen-@minLen)
        , @seed = (rand((@seed+@step)%2147483647)*2147483647);

    declare @dice int;
    select @dice = rand(@seed) * len(@first),
        @seed = (rand((@seed+@step)%2147483647)*2147483647);
    select @string = substring(@first, @dice, 1);

    while 0 < @length
    begin
        select @dice = rand(@seed) * 100
            , @seed = (rand((@seed+@step)%2147483647)*2147483647);
        if (@dice < 10) -- 10% special chars
        begin
            select @dice = rand(@seed) * len(@specials)+1
                , @seed = (rand((@seed+@step)%2147483647)*2147483647);
            select @string = @string + substring(@specials, @dice, 1);
        end
        else if (@dice < 10+10) -- 10% digits
        begin
            select @dice = rand(@seed) * len(@digit)+1
                , @seed = (rand((@seed+@step)%2147483647)*2147483647);
            select @string = @string + substring(@digit, @dice, 1);
        end
        else -- rest 80% alpha
        begin
            declare @preseed int = @seed;
            select @dice = rand(@seed) * len(@alpha)+1
                , @seed = (rand((@seed+@step)%2147483647)*2147483647);

            select @string = @string + substring(@alpha, @dice, 1);
        end

        select @length = @length - 1;
    end
end
go

在运行测试时，调用者会生成一个与测试运行相关联的随机种子(将其保存在结果表中)，然后传递种子，类似于:

When running the tests the caller generates a random seed it associates with the test run (saves it in the results table), then passed along the seed, similar to this:

declare @seed int;
declare @string varchar(256);

select @seed = 1234; -- saved start seed

exec usp_generateIdentifier
    @seed = @seed output
    , @string = @string output;
print @string;
exec usp_generateIdentifier
    @seed = @seed output
    , @string = @string output;
print @string;
exec usp_generateIdentifier
    @seed = @seed output
    , @string = @string output;
print @string;

2016-02-17 更新:请参阅下面的评论，原始程序在推进随机种子的方式上存在问题.我更新了代码，还修复了提到的一对一问题.

Update 2016-02-17: See the comments bellow, the original procedure had an issue in the way it advanced the random seed. I updated the code, and also fixed the mentioned off-by-one issue.

这篇关于使用 T-SQL 生成随机字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！