用 MySQL 批量生成强密码

大家好，欢迎来到IT知识分享网。

目录

一、初始实现

1. 创建生成密码的函数

2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度

（1）安装密码验证插件

（2）设置密码验证策略参数

（3）批量生成密码并获取强度

二、改进实现

1. 创建生成密码的函数

2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度

3. 卸载密码验证插件

 三、不使用正则表达式

        本篇介绍如何使用 MySQL 批量生成强密码，在 MySQL 8.0.16 版本下实现。具体需求如下：

• 批量生成 100 个密码。
• 密码长度统一为 16 个字符。
• 密码中包含数字、大小写字母、特殊字符。

一、初始实现

1. 创建生成密码的函数

use test; drop function if exists fn_GenerateStrongPassword; delimiter // create function fn_GenerateStrongPassword(length int) returns varchar(255) no sql begin declare allowedchars varchar(128) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()-_=+[]{}\|;:,./?'; declare returnstr varchar(255) default ''; declare i int default 0; while i < length do set returnstr = concat(returnstr, substring(allowedchars, floor(1 + rand() * 87), 1)); set i = i + 1; end while; return returnstr; end // delimiter ;

        说明：

• 密码长度为入参。
• 从构成密码的 88 个预定义字符中随机取 16 个字符。

2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度

        validate_password 是 MySQL 默认的密码管理策略插件，可通过配置对用户密码长度、强度进行管理。

（1）安装密码验证插件

mysql> install plugin validate_password soname 'validate_password.so'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | ... | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ 45 rows in set (0.00 sec) mysql>

（2）设置密码验证策略参数

mysql> show variables like 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | ON | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec) mysql> set global validate_password_length=16; Query OK, 0 rows affected (0.01 sec) mysql> set global validate_password_policy=2; Query OK, 0 rows affected (0.00 sec) mysql>

        密码验证策略参数说明如下表：

        MySQL 密码强度等级定义如下表：

（3）批量生成密码并获取强度

mysql> select password, strength, if(r=1,n,'') n -> from (select password, strength, -> row_number() over (partition by strength order by password desc) n, -> rank() over (partition by strength order by password) r -> from (select password, validate_password_strength(password) strength -> from (select test.fn_GenerateStrongPassword(16) password from mysql.help_topic limit 100) t -> order by strength, password) t) t; +------------------+----------+----+ | password | strength | n | +------------------+----------+----+ | ,/bn@p0/&?3m(5(h | 50 | 13 | | C.=IK]tLPhb-jd=n | 50 | | | cfveQz/gH@Qu#?%) | 50 | | | E(?W&x.eCQO=},hU | 50 | | | H:YeTO%I@K}B=DiO | 50 | | | Jc{NUCco!lRg}B=A | 50 | | | Mkxar?F=nv$a*;C( | 50 | | | OFLhjyj7clYQ0gwf | 50 | | | rCeyxOVDet.paOAl | 50 | | | sLUEn#t[#glN+.wF | 50 | | | TGCRVsKNd*}m?V#h | 50 | | | tooBiSy[Vj{qsT:r | 50 | | | Ulhd|U+@eizri]pt | 50 | | | _JQw&pTf%1%?6C5x | 100 | 87 | | -#u|}*w+1Iq#oYIx | 100 | | | -7{NXS)1U=7(d$5. | 100 | | | ,{4PO}pnv#?Oij | 100 | | | !^@BJixcD1auoia! | 100 | | | ?[ICQL7jQmt!]B|7 | 100 | | | .Y.k6/]VbQFF0Mim | 100 | | | (8/_x)y.cr|bARWx | 100 | | | (rYx4F}O11tz^c(/ | 100 | | | [S}cJ.ZgY88E0Os7 | 100 | | | ]:fRz:[Wk:E:8Y+W | 100 | | | @fkK@I^0s:h2^fa( | 100 | | | #6hPifc,07sg!F6t | 100 | | | #AB4wFcgAAX.k8fw | 100 | | | %9s:l*ZYk+/C7G_i | 100 | | | ^*6}1wQ6_sXooBgJ | 100 | | | ^EMnK4:)x[Yz9Z+Z | 100 | | | =d2:|$/7Ja)h:PUw | 100 | | | =moJVP$CG]EhH7ra | 100 | | | |^js$jyh2&hglJ7l | 100 | | | |99H[rGvk]f4cs?B | 100 | | | 1.dyz3xMJ@L.V(L@ | 100 | | | 15KmO}oh|Fcfu.n_ | 100 | | | 1A@!K}D|3E{LOc%! | 100 | | | 2l*14G;Zk--35H.8 | 100 | | | 4^?2k#ETWtLSw^im | 100 | | | 6PJ3]Zy2vFiI0CT3 | 100 | | | 8r.wHmUy%b$$QrS/ | 100 | | | 90K.3tvXh!DW$jyg | 100 | | | 9I+nqP-#p6^|xYg5 | 100 | | | 9Z=Uh)+=0;XdSIPp | 100 | | | AF}P4$-|m}l:E{MT | 100 | | | AMyi3=IJ=f#TJOkp | 100 | | | bm6+HyxU)Rf-;rqC | 100 | | | c#3[Vi_|h6fAB0X0 | 100 | | | c+w0#X5V$lJ3:)w_ | 100 | | | C=z}9#Z^f?*b8G(: | 100 | | | C$&0n5*c$6gH8vsA | 100 | | | CKlFJ.3rkb0QDtg@ | 100 | | | CMw,btdW62k%L]om | 100 | | | D!W2LBzT!b|Yj)$C | 100 | | | d/&?2fRBfG9CY}*x | 100 | | | e),C%)-9/_x)x[2M | 100 | | | e%9n9ceo8{O5(lu# | 100 | | | f{Ea/}4Kq7*?W*Ao | 100 | | | fEV60TQ++$pZNPb5 | 100 | | | GTTbUZHmXHvg2-Ew | 100 | | | i^Z4JmRfZXeW3P | 100 | | | Ip9;1rrHCPK4;%a# | 100 | | | J_bU1MI5gJ_.A5C6 | 100 | | | Jeehyl-^FQFE7BW_ | 100 | | | k(8/=ChK[i]mf]vX | 100 | | | L%VN4*igjBvA^.U% | 100 | | | LVLRu80QAgOg.5C0 | 100 | | | m{g9tf2[XpsS|fY9 | 100 | | | M&16OHSKUM197xC- | 100 | | | M%Pls68Jc[DeueSK | 100 | | | md%@CMu=YA(kr2Q1 | 100 | | | Mfa)g;NPa3gUKTDk | 100 | | | N!t.rg&0p#s(L9s, | 100 | | | nnBl9iI0DZ{$fc/- | 100 | | | o,yV[^s#hq9}OYR& | 100 | | | O*25Ida;4JlK@E1: | 100 | | | q7)f=kb@1_INe;R9 | 100 | | | qLY3Igs)P{j-_0b; | 100 | | | Rh;Kxg1^f?&.QRf* | 100 | | | s%q45D$!COHSKXZs | 100 | | | s2Js-Uov!=lhd:2s | 100 | | | STh+f9zHfmSnu!]z | 100 | | | t#ea]N13C^[h&7bg | 100 | | | tFkTv6V%q7^[cV4T | 100 | | | TGyyW.qbUZHmVB|7 | 100 | | | TM4=Es/zR48TVnoF | 100 | | | Ty+P^N:IlP.NDC1e | 100 | | | tZC)lr2OP/OK8o*Q | 100 | | | U_46H,2suU;j@DRO | 100 | | | u_Ule_j/1fSGDZ}& | 100 | | | u$fdcb?|4JjDE#6j | 100 | | | Ulf|LI9yB#^4+JM. | 100 | | | V(L9p-1U[&Ao{aD9 | 100 | | | V=31i9q[*EG(|sx9 | 100 | | | wQ9.(jjs@a-mu$d. | 100 | | | WT|i9p&J9vnb3hXZ | 100 | | | x*nHOpQ|nbZ]0=ox | 100 | | | X8#5cr{;,/eywLJ$ | 100 | | | X9%#J*(3@#SDo(Vz | 100 | | | Z|]5Y.iZ4Kq0;WaF | 100 | | +------------------+----------+----+ 100 rows in set (0.00 sec) mysql> 

        从查询结果可以看到，有 13 个密码的强度得分为 50，原因是使用随机函数循环取得 16 个字符，有一定的概率不能完全覆盖到数字、大小写字母和特殊字符。如 Mkxar?F=nv$a*;C( 中就没有数字。经过多次测试，约有 10% – 20% 会出现这种情况。

二、改进实现

1. 创建生成密码的函数

use test; drop function if exists fn_GenerateStrongPassword; delimiter // create function fn_GenerateStrongPassword(length int) returns varchar(255) no sql begin -- ASCII 码 33 - 126 对应数字、英文字母、特殊字符 declare rand_min int default 33; declare rand_max int default 126; declare regexp_str varchar(128) default concat('^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[^a-zA-Z0-9]).{',length,'}$'); declare return_str varchar(255) default ''; add_loop:loop with recursive cte1(n1) as (select 1 union all select n1 + 1 from cte1 where n1 < length) select group_concat(char(rand_min + rand() * (rand_max - rand_min)) separator '') into return_str from cte1; -- 用正则函数判断强度，区分大小写 if not regexp_like(return_str,regexp_str,'c') then iterate add_loop; -- 不满足需求则重新生成密码 else leave add_loop; -- 满足需求则退出循环 end if; end loop add_loop; -- 返回符合需求的密码 return return_str; end // delimiter ;

        说明：

• 最外层循环用于迭代生成一个完整的密码，并判断其强度。
• ASCII 码 33 – 126 对应数字、英文字母、特殊字符。
• 使用随机函数 rand() 和向下取整函数 floor() 随机选取 33 – 126 范围内的整数。
• 使用递归和 group_concat() 函数根据需要的长度拼接密码字符串。
• 用正则函数 regexp_like 判断强度，不满足需求则重新生成密码，满足需求则退出循环，然后返回结果。注意要使用区分大小写的匹配类型（regexp_like 的第三个参数设置为 ‘c’）。
• 正则表达式说明：
  • (?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[^a-zA-Z0-9]) 用四个肯定顺序环视零宽断言对字符进行判定。
  • (?=.*[a-z]) 判断小写字母是否存在。
  • (?=.*[A-Z]) 判断大写字母是否存在。
  • (?=.*[0-9]) 判断数字是否存在。
  • (?=.*[^a-zA-Z0-9]) 判断特殊字符是否存在。
  • .{16} 代表任意 16 个字符。
  • $ 代表结尾。

  ^ 代表开始。

2. 批量生成密码，并用 MySQL 密码策略管理插件验证密码强度

mysql> select password, strength, if(r=1,n,'') n -> from (select password, strength, -> row_number() over (partition by strength order by password desc) n, -> rank() over (partition by strength order by password) r -> from (select password, validate_password_strength(password) strength -> from (select test.fn_GenerateStrongPassword(16) password from mysql.help_topic limit 100) t -> order by strength, password) t) t; +------------------+----------+-----+ | password | strength | n | +------------------+----------+-----+ | _i:MJ7m9ciGX#?$& | 100 | 100 | | _mpO*XO206rbQGI+ | 100 | | | ,MFNr3T@gs)N)$yn | 100 | | | ;OSoAal5=CfEU4X| | 100 | | | ;rsN3^?4rh-]?xF? | 100 | | | !a}U:m[:i7cn7[JJ | 100 | | | !DSXB[KQnBjZ2D)g | 100 | | | .;$.1k^UDhJ&!t,k | 100 | | | .{1B$)+_59TTe9zK | 100 | | | (8,%|C=z|$.1k&XS | 100 | | | (J2=KTHF3fMc&[eZ | 100 | | | (Vx#+mjhmM*5-u89 | 100 | | | [MYXg9ug0yxV{=XA | 100 | | | {1D=w#}D/{UaH{Hr | 100 | | | }cJ;PZS%yl)@o5@0 | 100 | | | @xoc5p|j$M.QXEdo | 100 | | | *d*]bLkzj6,)u9*} | 100 | | | &r3V_YHo9,@%3+LW | 100 | | | #u||[ZC+GxrsMY1v | 100 | | | %1!({h!CRQ.IfiDG | 100 | | | ^(9eo9?=Cewm,E+A | 100 | | | ^*6|3D_qJQqS,zZg | 100 | | | ^v})FD1cDY]9)b4j | 100 | | | +@fiCE%^Z1vMN,Jp | 100 | | | =Q[,k#DPFG%0wqmm | 100 | | | |_N%K-{aD7C2i7cm | 100 | | | |R@mTsO8chDNt&x. | 100 | | | $,Y/re4fI*&WKM;H | 100 | | | 0{JzyR4!6o_#mQd@ | 100 | | | 26NyeJ[qA;*qTe8x | 100 | | | 2l=_8+z+O!r_32ob | 100 | | | 2MGUYy8W@?$))6_v | 100 | | | 3hTIH$1^c=qGAJlK | 100 | | | 4_Dm0kO/T6#!G0K. | 100 | | | 4+LYXk{mc!Ps7!Z& | 100 | | | 48U3R6^{sB.[P#zw | 100 | | | 5C0W79Ot!;T)Tpy{ | 100 | | | 5V%s#iu[9=pBeCNy | 100 | | | 7(d%8m5*hc{IAB7L | 100 | | | 72eJ}B_w*qVnlr3T | 100 | | | 8,$]ooDo=%xeOpP- | 100 | | | 89LhlHTO^O/V&yfM | 100 | | | 8gztx7V!:1pf&#CK | 100 | | | 9/_u80QCp[=43rmk | 100 | | | asbPBm^K(&Qg}C|4 | 100 | | | bzMBwE,$]oqM2&f. | 100 | | | c-g]uO6]R&P?X=YB | 100 | | | C3n]aF^&181:,;(w | 100 | | | CiSv0^%Sv80Py,,| | 100 | | | dAJkFNoP]cOv-M9n | 100 | | | DZ,/ezA7Ox,?iRu4 | 100 | | | eBKmP|tC/:$:Q2Xa | 100 | | | F{MPh.2oaQN(7:@^ | 100 | | | fnYMRlpP=_47KkEF | 100 | | | GCQO+,qg*^Ob7AS6 | 100 | | | gRBdyz1n,D-mqTe7 | 100 | | | gZ!-aV9-h:QXIvh8 | 100 | | | hAvB)pL2^e{GmZT+ | 100 | | | IhwbzLyi7ds};:{9 | 100 | | | ijyhZ87yJs(M#K=c | 100 | | | Ir_1OTrO@t|:am0l | 100 | | | JOhe/$-]/tpu1D]M | 100 | | | Jxn;tA_Dm8?;7X-O | 100 | | | k0n9.*f|LFV52k^R | 100 | | | k296q.zW|;]4U%r0 | 100 | | | kFKc[Cbfxm;wP22v | 100 | | | l3#-}j%Sw^imN(!n | 100 | | | lGQzan^H1=Q]:i2& | 100 | | | liis#hq0/^|z!8yC | 100 | | | LK(&Rmu!}JAxMH4b | 100 | | | MlDxC_u5U#hr%r@e | 100 | | | ng]w3F]JDOAn-!gt | 100 | | | nJY73i4]Ve2;(w-K | 100 | | | nnAgM?3qd5iWQ&N{ | 100 | | | o%EOx.cvnd@VUd1{ | 100 | | | oQ}i%UFtdTP(3!9B | 100 | | | pnv^ju_SbW#d,02? | 100 | | | qL198ARXFjM+e4dz | 100 | | | r%t)K2_FxwQ8|Ynb | 100 | | | RZNQje}NTu3Lzm+[ | 100 | | | S+&EG_ja8Ke/^;D} | 100 | | | s0]C.+KTChL}y8V9 | 100 | | | Smt51gX1A#%3(oJT | 100 | | | Su5W*FI{A-w^kzgS | 100 | | | T6$$TFuh@G8zKu}+ | 100 | | | TJM?4trCds,n].sk | 100 | | | TN6.=IG0L?!Y$b_q | 100 | | | Tv6Y[9-f-:xQ39X& | 100 | | | U4Y?wxYk__9|V/xG | 100 | | | Uj,PSljlFI{C{Wf5 | 100 | | | V!|Q6-qO%J#Rv#.4 | 100 | | | VJJ-|j%Tz}0$7hI# | 100 | | | vpnt9^)^K^184l$F | 100 | | | W@:V+#ht=ZHn1Yj^ | 100 | | | W9*|vKG3an$zux7V | 100 | | | X5T9]EgFV54tsJI% | 100 | | | Y{(Bp;l[?D9MnK5/ | 100 | | | yQZQ8,@*&XMVGug4 | 100 | | | Z87ARWC,(nDuolly | 100 | | | zcwsx!*-%CB4spu6 | 100 | | +------------------+----------+-----+ 100 rows in set (0.01 sec) mysql>

        从查询结果可以看到，所有 100 个密码强度得分都是 100，完全满足需求。因为不满足需求的情况不到 20%，所以不用担心循环陷入无休止的迭代中。当然必须指出，这版实现使用的是“判错重来”方式，会有不到 20% 的密码生成是做了无用功。

3. 卸载密码验证插件

mysql> uninstall plugin validate_password; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show plugins; +---------------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | ... | mysqlx | ACTIVE | DAEMON | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | +---------------------------------+----------+--------------------+---------+---------+ 44 rows in set (0.00 sec) mysql> show variables like 'validate_password%'; Empty set (0.00 sec) mysql>

 三、不使用正则表达式

        本场景使用正则表达式判断密码强度是最简便和高效的方法。不用正则表达式，使用ASCII码依次判断条件也行，但代码不够简洁，效率也不高。这里给出一种实现权当练习。

use test; drop function if exists fn_GenerateStrongPassword1; delimiter // create function fn_GenerateStrongPassword1(length int) returns varchar(255) no sql begin -- ASCII 码 33 - 126 对应数字、英文字母、特殊字符 declare rand_min int default 33; declare rand_max int default 126; declare return_str varchar(255) default ''; declare i int default 1; declare a int default 0; declare f int default 0; add_loop:loop with recursive cte1(n1) as (select 1 union all select n1 + 1 from cte1 where n1 < length) select group_concat(char(rand_min + rand() * (rand_max - rand_min)) separator '') into return_str from cte1; set i=1; set a=0; set f=0; -- 判断强度，区分大小写 in_loop: while i <= length do set a=ascii(substring(return_str,i,1)); case when a between 48 and 57 then -- 数字 if (f & 1)=1 then -- 第一位为 1，说明已包含 set i=i+1; iterate in_loop; else set f=f|(1 << 0); -- 将 1 赋值给第一位 end if; when a between 65 and 90 then -- 大写字母 if (f & 2)=2 then -- 第二位为 1，说明已包含 set i=i+1; iterate in_loop; else set f=f|(1 << 1); -- 将 1 赋值给第二位 end if; when a between 97 and 122 then -- 小写字母 if (f & 4)=4 then -- 第三位为 1，说明已包含 set i=i+1; iterate in_loop; else set f=f|(1 << 2); -- 将 1 赋值给第三位 end if; else -- 特殊字符 if (f & 8)=8 then -- 第四位为 1，说明已包含 set i=i+1; iterate in_loop; else set f=f|(1 << 3); -- 将 1 赋值给第四位 end if; end case; set i=i+1; end while; if f<15 then iterate add_loop; -- 不满足需求则重新生成密码 else leave add_loop; -- 满足需求则退出循环 end if; end loop add_loop; -- 返回符合需求的密码 return return_str; end // delimiter ;