【MySQL】编写随机密码生成脚本
数据需求:
密码规则是 12位 数字 + 字母 混合后MD5加密
然后导出一个表格或者记录文件,文件没明确要求
实现过程:
1、MD5加密函数使用
SET @txt = '123456'; SELECT @txt AS '明文', MD5(@txt) AS 'MD5密文'; Query OK, 0 rows affected (0.00 sec) +--------+----------------------------------+ | 明文 | MD5密文 | +--------+----------------------------------+ | 123456 | e10adc3949ba59abbe56e057f20f883e | +--------+----------------------------------+ 1 row in set (0.06 sec)
2、MD5加密后的密文本身就符合数字和字母的混合规则
配合RAND()函数, 使用LEFT 或者 RIGHT截取即可
SET @val = RAND(); SET @md5Val = MD5(@val); SELECT @val AS '随机值', @md5Val AS 'MD5加密值', LEFT(@md5Val, 12) AS '最终的密码'; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +--------------------+----------------------------------+--------------+ | 随机值 | MD5加密值 | 最终的密码 | +--------------------+----------------------------------+--------------+ | 0.9000418101437779 | 40de920815f9647c5ac97545e58b7b94 | 40de920815f9 | +--------------------+----------------------------------+--------------+ 1 row in set (0.03 sec)
3、创建密码本表,排除掉管理员账号
这一步已经生成了明文密码,由于字段不能复用操作,多一个步骤来更新密文
CREATE TABLE pwd_book AS SELECT id, us_name, us_username, LEFT(MD5(RAND()), 12) AS pwd, us_password AS md5, NOW() AS create_time FROM sys_pr_user WHERE sys_pr_user.id != 1 AND sys_pr_user.us_username != 'admin';
4、更新密码本表,赋值密文
UPDATE pwd_book SET md5 = MD5(pwd);
5、将系统用户表的密码字段覆写,使用UPDATE JOIN关联更新(排除管理员账号)
UPDATE sys_pr_user JOIN pwd_book ON sys_pr_user.id = pwd_book.id SET sys_pr_user.us_password = pwd_book.md5 WHERE sys_pr_user.id != 1 AND sys_pr_user.us_username != 'admin';
6、导出密码本
SELECT '用户名 ', '账号', '密码' UNION SELECT us_name, us_username, pwd FROM pwd_book INTO OUTFILE 'C:\\Users\\Administrator\\Desktop\\pwd_book.txt';
这一步导出MySQL默认是禁止的,需要更改配置文件开放:
show variables like "secure_file_priv"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | secure_file_priv | NULL | +------------------+-------+ 1 row in set (0.11 sec)
详细见文章:
https://blog.csdn.net/best_luxi/article/details/81479820
7、删除密码本表
DROP TABLE pwd_book;