Generate random passwords in MySQL based on Rules

In MySQL, a random password is generated based on rules. MySQL 5.0 and later versions support stored procedures, which are consistent, efficient, and secure. MySQL 5.0 and earlier versions do not support stored procedures. However, with the increasingly sophisticated MySQL technology, stored procedures will be widely used in future projects. In my application, I need to generate a random password for this account when the user registers for the first time. The generated password must meet certain requirements. These requirements must be configured by the system administrator. We provide the following password-related rules that can be used in combination: 1-The upper case letter is required. The upper case letter is required. ==> abbreviation [N] 4-can be any character ANY_CHARACTER ======> abbreviation [A] 5-must contain NON_ALPHANUMERIC_CHARACTER = ==> [S]. Therefore, I want to create a dynamic function "RANDOM_PASSWORD" to return a random password as required. The system administrator of www.2cto.com only needs to pass the required password rules and returns the corresponding random password. For example, the requirements are as follows: the first character must be in uppercase ====> U. The second character must be in lowercase ======> L. The third character must be a number ======> N fourth characters ======> A. The fifth character must be A non-letter or number ======> S. The sixth character must be A number ======> n, you can use the "ULNASN" parameter to obtain the random password. The password generated is consistent with the length of the passed parameter. In this example, the password length is 6. You can use the following method to call this function: 1RANDOM_PASSWORD ('ulnasn') before writing function definitions on the MySQL console, delete the function definitions that may already exist in the database, then you must change the separator to $. In fact, I wanted to change the separator to #. Later I found that it was not successful. I don't know why. Delete RANDOM_PASSWORD () in the command line () use the following command to define and change the delimiter of a FUNCTION: www.2cto.com 1 mysql> drop function if exists RANDOM_PASSWORD; 2 mysql> delimiter $. Then you can write this FUNCTION. The source code after debugging is as follows: 01 create function RANDOM_PASSWORD (str VARCHAR (255) 02 returns varchar (255) 03BEGIN04 DECLARE UPPER_CASE VARCHAR (26) DEFAULT 'Authorization'; 05 DECLARE LOWER_CASE VARCHAR (26) DEFAULT 'authorization'; 06 declare numbers varchar (10) DEFAULT '2016'; 07 DECLARE TEMP_CHARACTER VARCHAR (0123456789) default ''; 08 DECLARE NON_ALPHANUMERIC_CHARACTERS VARCHAR (255) DEFAULT '~! @ # $ % ^ & * () _ +-= ':; <> ,.? /'; 09 www.2cto.com DECLARE ALL_STRING VARCHAR (255) DEFAULT' ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 ~! @ # $ % ^ & * () _ +-= ':; <> ,.? /'; 10 DECLARE STR_LENGTH int default 0; 11 DECLARE I INT DEFAULT 0; 12 DECLARE RANDOM_CHARACTER CHAR (1) DEFAULT ''; 13 DECLARE PASSWORD_RETURNED VARCHAR (255) DEFAULT ''; 14 15 SET STR_LENGTH = CHAR_LENGTH (str); 16 17 WHILE I <STR_LENGTH DO18 SET TEMP_CHARACTER = SUBSTR (str, I + 1, 1 ); 19 CASE TEMP_CHARACTER20 WHEN 'n' THEN21 SET RANDOM_CHARACTER = SUBSTR (NUMBERS, CEIL (RAND () * (LENGTH (NUMBERS)-1 ), 1); 22 WHEN 'U' THEN23 SET RANDOM_CHARACTER = SUBSTR (UPPER_CASE, CEIL (RAND () * (LENGTH (UPPER_CASE)-1), 1 ); 24 www.2cto.com WHEN 'l' THEN25 SET RANDOM_CHARACTER = SUBSTR (LOWER_CASE, CEIL (RAND () * (LENGTH (LOWER_CASE)-1), 1 ); 26 WHEN's 'then27 SET RANDOM_CHARACTER = SUBSTR (NON_ALPHANUMERIC_CHARACTERS, CEIL (RAND () * (LENGTH (NON_ALPHANUMERIC_CHARACTERS)-1), 1); 28 WHEN 'a THEN 29 SET RANDOM_CHARACTER = SUBSTR (ALL_STRING, CEIL (RAND () * (LENGTH (ALL_STRING)-1), 1); 30 ELSE31 SET RANDOM_CHARACTER = ''; 32 end case; 33 SET PASSWORD_RETURNED = CONCAT (PASSWORD_RETURNED, RANDOM_CHARACTER); 34 SET I = I + 1; 35 END WHILE; 36 37 RETURN PASSWORD_RETURNED; 38END39 $ usage: 1 mysql> select RANDOM_PASSWORD ('ulnasn') PASSWORD; 2 www.2cto.com + ---------- + 3 | PASSWORD | 4 + ---------- + 5 | bv1n' 8 | 6 + ---------- + 71 row in set (0.00 sec) Here I will return the random password: bv1n'8. Of course, the running result may be different because it is random. Author: Bairrfhoinn