协助一个朋友做一个小网站,其中一个功能,是让用户注册成功之后,系统将随机产生一个登录密码,并自动发送至注册邮箱中,朋友的做法是为了用户使用真实邮箱。
随机产生密码,Insus.NET总结了三个,并分别写成了存储过程。
第一个,
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE
PROCEDURE
[
dbo
].
[
usp_RandomPassword
]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR( MAX) = N '', @L INT = 1
WHILE @L <= @Length -- 循环密码长度
BEGIN
-- 随机产生每一位字符,ASCII码48至122
DECLARE @RndChar CHAR( 1) = CHAR( ROUND( RAND() * ( 122 - 48 + 1) + 48, 0))
-- 随机产生的字符不包括下面字符
IF ASCII( @RndChar) NOT IN( 58, 59, 60, 61, 62, 63, 64, 91, 92, 93, 94, 95, 96) -- : , ; , < , = , > , ? ,@ , [ , \ , ] , ^ , _ , `
BEGIN
SET @RandomPassword = @RandomPassword + @RndChar
SET @L = @L + 1
END
END
SELECT @RandomPassword
END
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR( MAX) = N '', @L INT = 1
WHILE @L <= @Length -- 循环密码长度
BEGIN
-- 随机产生每一位字符,ASCII码48至122
DECLARE @RndChar CHAR( 1) = CHAR( ROUND( RAND() * ( 122 - 48 + 1) + 48, 0))
-- 随机产生的字符不包括下面字符
IF ASCII( @RndChar) NOT IN( 58, 59, 60, 61, 62, 63, 64, 91, 92, 93, 94, 95, 96) -- : , ; , < , = , > , ? ,@ , [ , \ , ] , ^ , _ , `
BEGIN
SET @RandomPassword = @RandomPassword + @RndChar
SET @L = @L + 1
END
END
SELECT @RandomPassword
END
第二个,
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE
PROCEDURE
[
dbo
].
[
usp_RandomPassword
]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR( MAX) = N '', @L INT = 1
-- 随机密码将由下面字符串产生,数字0-9,大写字母A-Z,小写字母a-z
DECLARE @BaseString VARCHAR( 255) = ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz '
WHILE @L <= @Length -- 循环密码长度
BEGIN
-- 61是变量@BaseString的长度减一
SET @RandomPassword = @RandomPassword + SUBSTRING( @BaseString, CONVERT( INT, ROUND( RAND() * 61 + 1, 0)), 1)
SET @L = @L + 1
END
SELECT @RandomPassword
END
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR( MAX) = N '', @L INT = 1
-- 随机密码将由下面字符串产生,数字0-9,大写字母A-Z,小写字母a-z
DECLARE @BaseString VARCHAR( 255) = ' 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz '
WHILE @L <= @Length -- 循环密码长度
BEGIN
-- 61是变量@BaseString的长度减一
SET @RandomPassword = @RandomPassword + SUBSTRING( @BaseString, CONVERT( INT, ROUND( RAND() * 61 + 1, 0)), 1)
SET @L = @L + 1
END
SELECT @RandomPassword
END
第三个,
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE
PROCEDURE
[
dbo
].
[
usp_RandomPassword
]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR( MAX) = N ''
DECLARE @R TINYINT, @L INT = 1
WHILE @L <= @Length -- 循环密码长度
BEGIN
SET @R = ROUND( RAND() * 2, 0) -- 随机产生0,1,2整数
IF @R = 0 -- 当变量为0时,将随机产生一位数字
SET @RandomPassword = @RandomPassword + CHAR( ROUND( RAND() * 9 + 48, 0))
ELSE IF @R = 1 -- 当变量为1时,将随机产生一位大写字母
SET @RandomPassword = @RandomPassword + CHAR( ROUND( RAND() * 25 + 65, 0))
ELSE IF @R = 2 -- 当变量为2时,将随机产生一位小写字母
SET @RandomPassword = @RandomPassword + CHAR( ROUND( RAND() * 25 + 97, 0))
SET @L = @L + 1
END
SELECT @RandomPassword
END
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR( MAX) = N ''
DECLARE @R TINYINT, @L INT = 1
WHILE @L <= @Length -- 循环密码长度
BEGIN
SET @R = ROUND( RAND() * 2, 0) -- 随机产生0,1,2整数
IF @R = 0 -- 当变量为0时,将随机产生一位数字
SET @RandomPassword = @RandomPassword + CHAR( ROUND( RAND() * 9 + 48, 0))
ELSE IF @R = 1 -- 当变量为1时,将随机产生一位大写字母
SET @RandomPassword = @RandomPassword + CHAR( ROUND( RAND() * 25 + 65, 0))
ELSE IF @R = 2 -- 当变量为2时,将随机产生一位小写字母
SET @RandomPassword = @RandomPassword + CHAR( ROUND( RAND() * 25 + 97, 0))
SET @L = @L + 1
END
SELECT @RandomPassword
END
最后一个也可以重构写成:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE
PROCEDURE
[
dbo
].
[
usp_RandomPassword
]
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR( MAX) = N '', @L INT = 1
WHILE @L <= @Length -- 循环密码长度
BEGIN
DECLARE @R INT = ROUND( RAND() * 2, 0)
SET @RandomPassword = @RandomPassword + CASE @R
WHEN 0 THEN CHAR( ROUND( RAND() * 9 + 48, 0))
WHEN 1 THEN CHAR( ROUND( RAND() * 25 + 65, 0))
WHEN 2 THEN CHAR( ROUND( RAND() * 25 + 97, 0)) END
SET @L = @L + 1
END
SELECT @RandomPassword
END
(
@Length INT = 8
)
AS
BEGIN
DECLARE @RandomPassword NVARCHAR( MAX) = N '', @L INT = 1
WHILE @L <= @Length -- 循环密码长度
BEGIN
DECLARE @R INT = ROUND( RAND() * 2, 0)
SET @RandomPassword = @RandomPassword + CASE @R
WHEN 0 THEN CHAR( ROUND( RAND() * 9 + 48, 0))
WHEN 1 THEN CHAR( ROUND( RAND() * 25 + 65, 0))
WHEN 2 THEN CHAR( ROUND( RAND() * 25 + 97, 0)) END
SET @L = @L + 1
END
SELECT @RandomPassword
END
Also reference:
MS SQL随机数 http://www.cnblogs.com/insus/archive/2012/01/27/2330058.html
不重复的随机数 http://www.cnblogs.com/insus/archive/2012/01/27/2330104.html