主页 / dba / 问题 / 207408
Accepted
user129291
Asked: 2018-05-22 09:49:33 +0800 CST

创建最多 50 位的随机数并存储在 Varchar 中

  • 772

有人有代码吗？寻找随机数函数发生器。我只提供可变长度@NumberLength = 50 等。它可以创建最多 50 位的数字，并存储在 varchar 中。（Bigint 没有存储这么高）我正在使用 Floor Rand、Floor、NewId()，仍然没有收到超过 12 位的答案。

如果有人有解决方案，那就太好了。我需要随机化表中的一列。

谢谢，

注意：需要在函数中，函数由于某种原因不能接受 NewID 或 rand()，所以我有时会使用视图。如果再次使用随机数，没问题，我们可以有一点点重复。

sql-server data-masking

5 个回答

  1. Best Answer

    这是一种方法（演示）。

    创建一个CRYPT_GEN_RANDOM不允许在函数中使用的视图

    CREATE VIEW dbo.LongRandom
AS
  SELECT CONVERT(VARCHAR(500), CRYPT_GEN_RANDOM(500), 2) AS random_hex_string

    然后在函数中调用它并A-F用空字符串替换所有内容。下面使用TRANSLATE将所有映射B-F到A和最终REPLACE删除所有As。

    您也可以简单地使用 6 嵌套REPLACE（如果您使用的是 < 2017 的版本，则无论如何您都必须这样做）

    CREATE FUNCTION dbo.reallyBigRandInt(@Length TINYINT)
RETURNS VARCHAR(50)
AS
  BEGIN
      RETURN
        (SELECT LEFT(REPLACE(TRANSLATE(random_hex_string,'BCDEF','AAAAA'),'A',''), @Length)
         FROM   dbo.LongRandom)
  END

    从视图返回的字符串比最终所需的最大字符串长 10 倍，以使删除这些字符极不可能留下足够的字符。

    然后从中选择

    SELECT dbo.reallyBigRandInt(50);

    注意：您也可以放弃字符串解析，只使用CRYPT_GEN_RANDOM生成三个bigint粘合在一起的字符串（没有减号），但除非您小心，否则这不太随机。

    最大值bigint很9223372036854775807明显，最左边的数字是 9 的概率较低。类似地，第二个字符是1或0比任何其他数字的概率更大。因为3只有当前导字符是 时，第二个字符才可能是0-8。其他职位也存在类似问题，但随着您向右移动，重要性会下降。

    如果这对您来说不是问题，您可以将三个 bigint 的最右边 17 位连接在一起以获得 51 个字符的数字字符串，然后将其缩减到所需的长度。

    CREATE VIEW dbo.RandomBigInt
AS
  SELECT CONVERT(BIGINT, CRYPT_GEN_RANDOM(8)) AS number

    和功能

    CREATE FUNCTION dbo.reallyBigRandInt2(@Length TINYINT)
RETURNS VARCHAR(50)
AS
  BEGIN
      RETURN
        (SELECT RIGHT(RIGHT(FORMAT(number, 'D19'),17) + 
                      RIGHT(FORMAT(number, 'D19'),17) + 
                      RIGHT(FORMAT(number, 'D19'),17), @Length)
FROM  dbo.RandomBigInt)
  END
    • 4

  2. 使用在没有冲突的 SQL Server中生成随机数中的示例作为起点，这可能对您有用。

    --set up demo data
--using a recursive CTE, load a table with 1 million pre-allocated random number string
--this took about 10 seconds on my laptop
drop table if exists #temp
go
Declare @start int, @end int
Select @start=1, @end=1000000

;With NumberSequence( Number, RandNum ) as
(
    Select @start as Number,
    right(
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(@start ) * 1000000) % 1000000)) 
    ,50) as RandNum

        union all
    Select Number + 1,
    right(
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 1) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 2) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 3) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 4) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 5) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 6) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 7) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 8) * 1000000) % 1000000)) + 
    convert(varchar(10),1000000 + (CONVERT(INT, RAND(Number + 9) * 1000000) % 1000000)) 
    ,50) as RandNum

    from NumberSequence
    where Number < @end
)
select number, RandNum into #temp from NumberSequence
option (MaxRecursion 0)


--Declare a table that we intend to populate with the random string data
declare @Table table (id int, RandomColumn varchar(50))
insert into @Table(id, RandomColumn) values
    (1,' '),
    (2,' '),
    (3,' '),
    (4,' ')

--Assign a row number to each row that we can use to join against the random string table
;with TableToUpdate as
(
select *, ROW_NUMBER() over (order by id) as rn from @Table
)
UPDATE t
SET t.RandomColumn = tmp.RandNum
FROM TableToUpdate t
JOIN #temp tmp ON tmp.Number = t.rn

--check the results
SELECT * FROM @Table

    | id | RandomColumn                                       |
|----|----------------------------------------------------|
| 1  | 11713591171359117135911713591171359117135911713591 |
| 2  | 91713647171366617136851713703171372217137411713759 |
| 3  | 71713666171368517137031713722171374117137591713778 |
| 4  | 61713685171370317137221713741171375917137781713796 |

    如果您需要少于 50 个字节的随机数，您可以尝试SUBSTRING使用所需的长度，并查看可能存在多少重复项以及这些重复项是否可以接受。我对 100 万行进行了测试，发现没有 17 个字符或 40 个字符的重复项（根据您的评论）。

    select substring(RandNum,1,17) as RandNum, count(*)
from #temp
group by substring(RandNum,1,17) 
having count(*) > 1

select substring(RandNum,1,40) as RandNum, count(*)
from #temp
group by substring(RandNum,1,40) 
having count(*) > 1
    • 3

  3. 以下代码创建一个视图以提供对副作用函数的访问，CRYPT_GEN_RANDOM. 该函数多次调用视图CYRPT_GEN_RANDOM，每次调用都获取一个字节。

    DROP FUNCTION IF EXISTS dbo.gen_ran_tvf;
DROP VIEW IF EXISTS dbo.gen_ran_view;
GO
CREATE VIEW dbo.gen_ran_view
WITH SCHEMABINDING
AS
SELECT cgr = CONVERT(int, CRYPT_GEN_RANDOM(1));
GO
CREATE FUNCTION dbo.gen_ran_tvf
(
    @digits int
    , @randomizer int
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    WITH nums AS (
        SELECT n = CONVERT(int, v.n)
        FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(n)
    )
    , digits AS (
        SELECT TOP(@digits)
            v.cgr
        FROM dbo.gen_ran_view v
            CROSS JOIN nums n1
            CROSS JOIN nums n2
    )
    SELECT 
        t = LEFT(
            (
            SELECT '' + digits.cgr
            FROM digits
            FOR XML PATH('')
            )
            , @digits)
        , r = @randomizer
);
GO

    该函数本身是一个模式绑定的表值函数，可以由 SQL Server 查询优化器“内联”，因此与这种类型的操作一样快。

    我创建了一个测试表并用 1,000,000 行填充它：

    DROP TABLE IF EXISTS dbo.SampleData;
GO
CREATE TABLE dbo.SampleData
(
    SampleDataID int NOT NULL
        CONSTRAINT PK_SampleData
        PRIMARY KEY 
        CLUSTERED
        IDENTITY(1,1)
    , SomeVal varchar(50) NOT NULL
);
GO

TRUNCATE TABLE dbo.SampleData;
GO

INSERT INTO dbo.SampleData (SomeVal)
SELECT TOP(1000000) CONVERT(varchar(50), '')
FROM sys.syscolumns c1
    , sys.syscolumns c2;
GO

    这是您可能期望使用 TVF 更新SampleData表的方式：

    UPDATE dbo.SampleData
SET SomeVal = tvf.t
FROM dbo.SampleData sd
    CROSS APPLY dbo.gen_ran_tvf(50, sd.SampleDataID) tvf

    但是，对于大量行，生成的执行计划使用性能表假脱机从 TVF 生成单行。这导致每一行都具有相同的值，这不是所需的状态。

    对于这个特定的设置，如果您只在表中插入 57 行，并运行上述语句，您确实会看到每一行的随机值，因为表假脱机不再存在，并且查询优化器选择为每行执行一次 TVF排在SampleData.

    如果您在 SQL Server 2016 或更高版本上运行，则可以使用新的表提示，NO_PERFORMANCE_SPOOL以确保永远不会使用此“优化”。当您将该提示与 join-order-enforcing 提示 结合使用时，无论存在多少行，结果都是表中每一FORCE ORDER行的随机行。

    因此，更新语句变为：

    UPDATE dbo.SampleData
SET SomeVal = tvf.t
FROM dbo.SampleData sd
    CROSS APPLY dbo.gen_ran_tvf(50, sd.SampleDataID) tvf
OPTION (
    NO_PERFORMANCE_SPOOL
    , FORCE ORDER
    );
GO

    “实际”执行计划：

    在此处输入图像描述

    看表：

    SELECT *
FROM dbo.SampleData;

    我们看：

    ╔══════════════╦══════════════════════════════════ ══════════════════╗
║ SampleDataID ║ SomeVal ║
╠══════════════╬══════════════════════════════════ ══════════════════╣
║ 1 ║ 13112011187125181161243421302232222710240208203146 ║
║ 2 ║ 46314918617612853143741876746110662154749221200135 ║
║ 3 ║ 51061817319197421023124056174411660871198014321011 ║
║ 4 ║ 42184243211535022623816320713413918322511811717948 ║
║ 5 ║ 50931761021417838201791946726229222231676112631621 ║
║ 6 ║ 13588442531751073017338155821851591207315016221382 ║
║ 7 ║ 12418133401459429211173481131611316869160118221209 ║
║ 8 ║ 82271435818112225210622167252113138163226124182352 ║
║ 9 ║ 75220220124661172206422425299201988022810670231532 ║
║ 10 ║ 21610776198239186174931291616122930332049222921229 ║
║ 11 ║ 22811311396795182941996034109261472352503620625436 ║
║ 12 ║ 74612472525863716112125157233126171220494114848272 ║
║ 13 ║ 25119215323633306520710920720911421423524322717016 ║
║ 14 ║ 57250150114123725014912523398921624261693927878515 ║
║ 15 ║ 13116129240304813115918225022257130174017136111245 ║
...
║ 660731 ║ 23916221915121421617721762187898720350232178132462 ║
║ 660732 ║ 17016020013213211911920940141102196558714414847243 ║
║ 660733 ║ 16108202204200984770211104216131122159591931201861 ║
║ 660734 ║ 15524112614417119114811316419015619112023520711342 ║
╚══════════════╩══════════════════════════════════ ══════════════════╝

    在我旧的慢速工作站上，使用此 TVF 更新 1,000,000 行大约需要 40 秒才能完成。

    由于我们将 0 到 255 范围内的各个数字“粘合”在一起，因此数字 1 和 2 的出现次数将高于其他数字；但是，由于您使用它来混淆数据而不是对其进行加密，因此我认为这不会是一个破坏交易的问题。

    • 3

  4. 知道了。嗯..我有一个方法。在大片上可能有点慢：

    因为你不能在函数中使用 rand()，所以我在这篇文章中从@Pரதீப் 偷了一个视图：

    https://stackoverflow.com/questions/31468836/use-rand-in-user-defined-function#31468878

    并对其进行了一些修改，使其看起来像这样：

        create VIEW random_val_view
    AS
    SELECT cast(floor(RAND()*10) as char(1)) as  random_value

    然后我写了这个小曲子，连接视图的结果：

        create function dbo.reallyBigRandInt(@iterations int)
    returns varchar(255)
    as begin
    declare @len int,
    @string varchar(255)

    set @len = 0
    set @string = ''

    while @len <@iterations

    begin

    set @string = @string + (select top 1 * from  random_val_view) 

    set @len +=1

    end

    return @string
    end

    然后你可以这样称呼它：

        select dbo.reallyBigRandInt(50)

    更改您输入函数的数字将更改输出的字符串的长度。

    显然，正如 Aaron & Erik 所提到的，它不会处理重复项。

    现在，它可以处理最多 255 个字符的字符串，但我想你可以返回 varchar(max)。不过，我没有足够的勇气在 SE 上使用 varchar(max) ......

    • 1

  5. 这是我的建议。

        ALTER FUNCTION dbo.GetRandomValue (

@input UNIQUEIDENTIFIER

,@ReqLen INT

)

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @RandomValue VARCHAR(max)

SELECT TOP (cast((10 / 50.0) * @ReqLen AS INT)) @RandomValue = COALESCE(@RandomValue + '', '') + cast(abs(CHECKSUM(@input)) AS VARCHAR(mAX))

FROM dbo.TABLE1

RETURN left(@RandomValue, @ReqLen)

END

    此处 Table1 可以是您的任何表。如果它不包含超过 50-60 行并且列数较少，则性能会更好。

    这就是我没有选择系统表的原因。

    用法

    select dbo.GetRandomValue (newid(),33)

select dbo.GetRandomValue (newid(),200)

select dbo.GetRandomValue (newid(),5000)

    等等。

    替代解决方案

    它将返回一个字母数字。但它会按你的意愿工作。

    declare @RandomValue varchar(max)

declare @RequireLength int=33

select @RandomValue=left(convert(varchar(max), CRYPT_GEN_RANDOM(@RequireLength) ,2),@RequireLength)

select @RandomValue,len(@RandomValue)
    • 0

相关问题