sql - 使用 rand() 函数用随机 9 位数字更新多行

Question

我正在尝试使用以下代码用随机 9 位数字更新多行。

UPDATE SGT_EMPLOYER 
SET SSN = (CONVERT(NUMERIC(10,0),RAND()  * 899999999) + 100000000)
WHERE EMPLOYER_ACCOUNT_ID = 123456789;

预期结果:查询应使用 300 个随机 9 位数更新 300 行。

实际:查询正在更新 300 行，其数量与 ran() 函数仅执行一次相同。

请帮忙。谢谢。

Answer 1

正如您自己已经了解的那样，RAND 是 SQL Server 中的 run-time constant function。这意味着每个语句调用一次，生成的值用于每个受影响的行。

每行还调用其他函数。人们经常将 NEWID 与 CHECKSUM 一起使用来代替随机数，但我不推荐它，因为这种随机数的分布可能很差。

有一个专门用于生成随机数的好函数:CRYPT_GEN_RANDOM。它至少从 SQL Server 2008 开始可用。

它生成给定数量的随机字节。

在您的情况下，将随机数作为 [0;1] 范围内的浮点值会很方便，与 RAND 返回的值相同。

因此，CRYPT_GEN_RANDOM(4) 生成 4 个随机字节作为 varbinary。将它们转换为 int，除以 32 位整数的最大值 (4294967295)，然后加上 0.5 将范围从 [-0.5;+0.5] 移动到 [0;1]:

(CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5)

您的查询变为:

UPDATE SGT_EMPLOYER 
SET SSN = 
    CONVERT(NUMERIC(10,0),
    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 899999999.0 + 100000000.0)
WHERE EMPLOYER_ACCOUNT_ID = 123456789;