[SQL Server]更新数据库表的某一字段为限制范围的随机数

楼主^#

更多发布于：2012-09-24 15:02

[] 1

需求：用SQL脚本更新数据库某个字段为六位随机值
环境：SQL Server 2008，数据库内有上千条数据

问题1：六位随机值
步骤1：随机数的SQL函数为rand() ，而rand()生成的是0-1之间的小数。

图片：20120924010546763.png

步骤2：将rand()*1000000则看似可以得到有六位数了（小数部分暂时忽略不算）。可是，假设rand()得到的是一个类似0.0xxx的小数，rand()*1000000就会只有五位整数部分，或许更少。

图片：20120924010547862.png

步骤3：rand()*(999999-100000)+100000这样就可以保证这个随机数的整数部分一定有六位，当然随机数的第一位永远不可能为0，这只能说取舍吧，只能为1-9也不是什么大问题。总而言之，随机数的整数部分解决了。然后需要将随机数的小数部分去掉。

图片：20120924010547951.png

步骤4：cast(rand()*(999999-100000)+100000 as nvarchar(6)) cast的作用是类型转换，将随机数转化为nvarchar(6)，得到的结果就是我们需要的随机数了。

图片：20120924010548554.png

问题2：更新每一条数据的某一字段
想法1：

UPDATE [test].[dbo].[my_table] SET [test_rand] = cast(rand()*(999999-100000)+100000 as nvarchar(6))

这种想法是有多天真啊。这样更新下来只能导致这个字段变成相同的一个随机值。

图片：20120924010548411.png

想法2：写一段程序，每次调用想法1，直到所有数据发生变化，这个也是相当的天真。首先，需求本身要求的就是SQL脚本来实现，所有即使是写好了程序也是无用功，而且这样的做法本身就很垃圾。每次要连接一次数据库，如果上万条就要连接上万次。不可取。

想法3：想来想去，首先一定要有循环才能实现。但是循环标志是什么呢。经过向前辈的虚心求教（心虚～～），提示由游标这个东西可以实现。因为本身对SQL接触甚少，原谅我不知道有这个东西。
步骤1：声明游标

DECLARE user_extension_cursor CURSOR
FOR
SELECT id
FROM [mtrade].[dbo].[user]
声明游标的时候指定游标指的是数据库的哪一个字段。（在这里只能选择作为主键的id）

步骤2：需要用FETCH来获取游标

FETCH NEXT FROM user_extension_cursor
INTO @user_id
步骤3：循环更新字段的值

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [mtrade].[dbo].[user]
SET [user].[extension] = cast(rand()*(999999-100000)+100000 as nvarchar(6))
WHERE id = @user_id
FETCH NEXT FROM user_extension_cursor INTO @user_id
END

这样才算大功告成。
以下是完整的SQL游标更新随机数的SQL代码（具体细节没有过多的说明，主要鄙人对于SQL不是很熟）

-- 更新 my_table 的 test_rand 字段
DECLARE @user_id varchar(36)
DECLARE user_extension_cursor CURSOR
FOR
SELECT id
FROM [test].[dbo].[my_table]
OPEN user_extension_cursor;
FETCH NEXT FROM user_extension_cursor
INTO @user_id

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [test].[dbo].[my_table]
SET [my_table].test_rand = cast(rand()*(999999-100000)+100000 as nvarchar(6))
WHERE id = @user_id
FETCH NEXT FROM user_extension_cursor INTO @user_id
END
CLOSE user_extension_cursor;
DEALLOCATE user_extension_cursor;
---

图片：20120924010548558.png