更新数据库表的某一字段为限制范围的随机数
需求:用SQL脚本更新数据库某个字段为六位随机值
环境:SQL Server 2008,数据库内有上千条数据
问题1:六位随机值
步骤1:随机数的SQL函数为rand() ,而rand()生成的是0-1之间的小数。
步骤2:将rand()*1000000则看似可以得到有六位数了(小数部分暂时忽略不算)。可是,假设rand()得到的是一个类似0.0xxx的小数,rand()*1000000就会只有五位整数部分,或许更少。
步骤3:rand()*(999999-100000)+100000这样就可以保证这个随机数的整数部分一定有六位,当然随机数的第一位永远不可能为0,这只能说取舍吧,只能为1-9也不是什么大问题。总而言之,随机数的整数部分解决了。然后需要将随机数的小数部分去掉。
步骤4:cast(rand()*(999999-100000)+100000 as nvarchar(6)) cast的作用是类型转换,将随机数转化为nvarchar(6),得到的结果就是我们需要的随机数了。
问题2:更新每一条数据的某一字段
想法1:
UPDATE
[test].[dbo].[my_table]
SET
[test_rand] =
cast
(rand()*(999999-100000)+100000
as
nvarchar(6))
|
这种想法是有多天真啊。这样更新下来只能导致这个字段变成相同的一个随机值。
想法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; ---
学艺不精,以此共勉。
作者:Ron Ngai
出处:http://rondsny.github.io/rondsny/
关于作者:断码码农一枚。
欢迎转载,但未经作者同意须在文章页面明显位置给出原文连接
如有问题,可以通过rondsny#gmail.com 联系我,非常感谢。
原网址:http://www.cnblogs.com/rond/archive/2012/09/24/2699849.html