SQL 生成随机字符串

MySQL

use test;

set global log_bin_trust_function_creators=1;

delimiter $$
CREATE function rs(n int)
returns varchar(1024)
begin
declare chars char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ0123456789';
declare res varchar(1024) default '';
declare i int default 0;
repeat
set i = i + 1;
set res = concat(res,substring(chars,floor(1+rand()*62),1));
until i=n end repeat;
return res;
end
$$

delimiter ;
select rs(10); 

SQLServer

方法一：和mysql类似使用rand()随机数

--第一步:创建视图
CREATE VIEW [dbo].[View_Rand]
AS
SELECT RAND() AS frand;
GO

--第二步:自定义的随机函数
CREATE FUNCTION [dbo].[Random](@n INT)
RETURNS INT
AS BEGIN
    SELECT @n=@n * frand FROM dbo.View_Rand;
    RETURN @n;
END;
GO

--第三步:生成字符串
CREATE FUNCTION rs(@n INT)
RETURNS VARCHAR(1024) BEGIN
    DECLARE @chars CHAR(62) ='abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE @res VARCHAR(1024) ='';
    DECLARE @i INT=0;
    WHILE(@i<@n)BEGIN
        SET @i=@i+1;
        SET @res=@res+SUBSTRING(@chars, FLOOR(1+dbo.Random(62)), 1);
    END;
    RETURN @res;
END;
GO

SELECT dbo.rs(10);

方法二：使用newid()随机数，结合checksum()使用

--第一步:创建视图
CREATE VIEW [dbo].[View_NewID]
AS
SELECT NEWID() AS frand;
GO

--第二步:自定义的随机函数
CREATE FUNCTION [dbo].[GetRandom](@n INT)
RETURNS INT
AS BEGIN
    SELECT @n=ABS(CHECKSUM(frand)%@n) FROM dbo.View_NewID;
    RETURN @n;
END;
GO

--第三步:生成字符串
CREATE FUNCTION getrs(@n INT)
RETURNS VARCHAR(1024) BEGIN
    DECLARE @chars CHAR(62) ='abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE @res VARCHAR(1024) ='';
    DECLARE @i INT=0;
    WHILE(@i<@n)BEGIN
        SET @i=@i+1;
        SET @res=@res+SUBSTRING(@chars, FLOOR(1+dbo.GetRandom(62)), 1);
    END;
    RETURN @res;
END;
GO

SELECT dbo.getrs(10);

 

转载于:https://www.cnblogs.com/Canyon/p/11208646.html