--测试数据
create table test
(
code bigint
)create procedure p_createrand
@seed bigint,@random_count int
as
declare @i int
declare @random_number bigint
set @i=0
begin
while(@i<@random_count)
begin
set @random_number=cast(rand()*@seed as bigint)
if not exists(select 1 from test where code=@random_number)
begin
insert into test values(@random_number)
set @i=@i+1
end
end
endexec p_createrand 100,60select * from test
33
93
6
42
1
47
31
85
64
82
95
79
92
99
9
94
41
3
50
2
10
83
65
52
28
66
30
4
43
39
15
67
78
20
11
84
68
70
81
71
56
21
19
35
13
0
32
74
51
60
97
72
40
8
57
45
23
77
80
38（所影响的行数为 60 行）
create table test
(
code bigint
)create procedure p_createrand
@seed bigint,@random_count int
as
declare @i int
declare @random_number bigint
set @i=0
begin
while(@i<@random_count)
begin
set @random_number=cast(rand()*@seed as bigint)
if not exists(select 1 from test where code=@random_number)
begin
insert into test values(@random_number)
set @i=@i+1
end
end
endexec p_createrand 100,60select * from test
33
93
6
42
1
47
31
85
64
82
95
79
92
99
9
94
41
3
50
2
10
83
65
52
28
66
30
4
43
39
15
67
78
20
11
84
68
70
81
71
56
21
19
35
13
0
32
74
51
60
97
72
40
8
57
45
23
77
80
38（所影响的行数为 60 行）
declare @random_number bigint
set @random_number=(power(10,@seed)-1-power(10,@seed-1))*rand()+power(10,@seed-1)
@seed bigint,@random_count int
as
declare @i int
declare @random_number bigint
set @i=0
begin
while(@i<@random_count)
begin
set @random_number=(power(10,@seed)-1-power(10,@seed-1))*rand()+power(10,@seed-1) if not exists(select 1 from #test where code=@random_number)
begin
insert into test values(@random_number)
set @i=@i+1
end
end
end
老陈的算法是: 2位 代表 10-99
那么 随机数=10+rand()*(99-10)3位依次类推
@seed 已经不需要bigint了,@seed是长度的意思,而不是具体的数值.
@random_count 为int也足够了,它是你要产生几个数字的意思.真正放随机数字的变量是@random_number bigint
bigint从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。也能满足20位长度了.
更长? 那得看你的计算机能支持到多少位长度的整数了,SQL SERVER 到
-2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807)