mysql密码存储过程_三种随便密码生成sql存储过程

本文章分享了几款关于三种随便密码生成sql存储过程，有需要学习的同学可以参考一下下哈。

随机产生密码，Insus.NET总结了三个，并分别写成了存储过程。

第一个，

代码如下

复制代码

usp_RandomPassword 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

第二个，

代码如下

复制代码

usp_RandomPassword 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

第三个，

代码如下

复制代码

usp_RandomPassword 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

最后一个也可以重构写成：

代码如下

复制代码

usp_RandomPassword 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

Also reference:

本文原创发布php中文网，转载请注明出处，感谢您的尊重！