介绍
来自上一篇文章Python 生成批量随机密码的启发,生成批量随机密码是否可以再Mysql上实现呢?经研究答案是可以的,因此本文将使用Mysql简单查询和储存过程给大家进一步实现生成批量随机密码。
1.了解熟悉字符集
首先我们先熟悉下Mysql中存在的字符集,使用char()查看哪一些字符是可以用于生成密码的;
# 查看Mysql的字符集有哪些
with recursive cte (n) as
(
select 1
union all
select n + 1 from cte where n < 200
)
select n,char(n) t
from cte;
-- 48:57 0-9
-- 65:90 A-Z
-- 97:122 a-z
-- 33:126 数字、英文字母、特殊字符
描述:这个查询语句使用了递归和char()函数,查看1-200对应的字符有哪些,由于行记录太多在这里不做显示,可以前往数据库操作查看,通过查看可知 48:57对应0-9数字、65:90对应A-Z大写英文字母、97:122对应a-z小写英文字母、33:126对应数字、英文字母、特殊字符;
2.根据字符集进行选取和测试
利用随机函数rand()和向下取整函数floor()选取步骤1中需要的字符对应的整数;
select char(floor(rand() * 9)+48) a -- 随机生成48:57对应0-9数字
union all
select char(floor(rand() * 89)+33) a -- 随机生成33:126对应数字、英文字母、特殊字符
;
然后我们可以巧妙的利用递归和group_concat()根据需要的长度进行拼接成字符串;
with recursive cte1(n1) as
(select 1
union all
select n1 + 1 from cte1 where n1 < 10), -- 递归出需要生成的用户id长度的10条记录
cte2(n2) as
(select 1
union all
select n2 + 1 from cte2 where n2 < 30) -- 递归出需要生成的密码长度的30条记录
select group_concat(char(floor(rand() * 9)+48) separator '') t
from cte1 -- 生成长度为10的用户id
union all
select group_concat(char(floor(rand() * 89)+33) separator '') t
from cte2 -- 生成长度为30的用户密码
;
3.根据测试结果进一步扩展生成批量
在这里,我们定义三个常见易变的变量,根据步骤2的测试结果,进一步利用笛卡尔积将用户id和密码进行合并;
-- 定义用户长度、密码长度和生成的总数量三个变量
set @user_length = 10;
set @password_length = 30;
set @total_num = 10;
with recursive
cte1(n1) as
(select 1
union all
select n1 + 1
from cte1
where n1 < @user_length), -- 递归出需要生成的用户id长度数量记录
cte2(n2) as
(select 1
union all
select n2 + 1
from cte2
where n2 < @password_length), -- 递归出需要生成的密码长度数量记录
cte3(n3) as
(select 1
union all
select n3 + 1
from cte3
where n3 < @total_num), -- 递归出需要生成的总数量记录
user_id as
(select n3,
group_concat(char(floor(rand() * 9) + 48) separator '') user_id
from cte3
join cte1
group by n3), -- 根据总数量记录与用户id长度记录做笛卡尔积,再根据总数量汇总拼接每个数量对应的用户id长度字符组
password as
(select n3,
group_concat(char(floor(rand() * 89) + 33) separator '') password
from cte3
join cte2
group by n3) -- 根据总数量记录与密码长度记录做笛卡尔积,再根据总数量汇总拼接每个数量对应的密码长度字符组
select n3, user_id, password
from user_id
join password using (n3)
;
描述:这里是通过简单的查询实现,可以定义和修改三个变量,重新执行进行复用,也用使用存储过程进一步实现;
4.使用存储过程实现生成批量密码
创建有用户长度、密码长度和总数量三个参数的存储过程;
create procedure generate_random_passwords(in user_length int,in password_length int,in total_num int)
begin
set @user_set := (with recursive cte (n) as
(select 48
union all
select n + 1
from cte
where n < 57)
select group_concat(char(n) separator '') a
from cte); -- 定义用户id字符集
set @password_set := (with recursive cte (n) as
(select 33
union all
select n + 1
from cte
where n < 126)
select group_concat(char(n) separator '') a
from cte); -- 定义密码字符集
set @num := 1;
set @sql := '';
-- 创建一个临时表用于存储用户id和密码信息
create temporary table if not exists temp_passwords
(
id int auto_increment primary key,
user_id varchar(255) comment '用户id',
`password` varchar(255) comment '密码'
);
-- 使用while循环产出一定量的用户id和密码
while @num <= total_num do
set @user_id := '';
set @password := '';
-- 根据给定的用户id长度,循环产出对应数量的用户id
while char_length(@user_id) <= user_length do
set @user_id := concat(@user_id, substring(@user_set, floor(1 + rand() * char_length(@user_set)), 1));
end while;
-- 根据给定的密码长度,循环产出对应数量的密码
while char_length(@password) <= password_length do
set @password := concat(@password, substring(@password_set, floor(1 + rand() * char_length(@password_set)), 1));
end while;
-- 将生成的用户id和密码录入到临时表
insert into temp_passwords(user_id,`password`) values (@user_id,@password);
-- 循环满足截止条件
set @num := @num + 1;
end while;
-- 查询查看结果
select user_id,`password` from temp_passwords;
-- 清空临时表
truncate table temp_passwords;
end;
call generate_random_passwords(10,30,10)
描述:这里用到了存储过程和循环的知识,实现的整体思路还是和步骤2差不多一致。调用generate_random_passwords()存储过程输入要的三个参数就可得到所需要的密码。