Mysql 生成批量随机密码

介绍

来自上一篇文章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()存储过程输入要的三个参数就可得到所需要的密码。