Mysql custom random string and mysql custom string

Mysql custom random string and mysql custom string

A system was developed a few days ago and requires random strings. However, if mysql database functions are provided directly, the existing functions are used to create random strings.

1. simple and crude.

select ..., substring(MD5(RAND()),floor(RAND()*26)+1,6) as rand_str .....

The appeal example generates a 6-digit random string.

Function explanation:

Rand (): generates decimal places between 0 and 1. for short, the number of seeds. rand () * 25 is between 0 and 25, excluding 25.

Floor (val): generates the maximum integer of the nearest val.

Md5 (): Performs Md5 encryption on the string (one-way). The length of the generated string is 32 characters.

Substring (str, pos, len): truncates a string. The first parameter is the string to be truncated, and the second parameter is the start position (here there are some differences: the start position of the subscript is 1, you can try). The third parameter is the truncation length.

 

2. Wrap Method 1: User-Defined Functions

Drop function if exists rand_str; # first sentence: if a duplicate function exists, delete the create function rand_str (strlen smallint) returns varchar (255) # second sentence: Define a function, name 'rand _ str', parameter name strlen parameter type smallint, Return Value Type varchar (255 ), note that the return # BEGIN # below returns is equivalent to the left braces '{'Clare result_str VARCHAR (255) default'; # declares the returned object, type, length, default Value: SET result_str = SUBSTRING (MD5 (RAND (), 32-strlen, strlen); # RETURN result_str, a simple and crude function in method 1, which sets the value of the returned object; # return END: return END # The end id is equivalent '}'

　　

3. The implementation of each sentence in the code of a user-defined function is not explained. You can refer to the Code explanation in method 2 to see it.

DROP FUNCTION IF EXISTS rand_str;create FUNCTION rand_str(strlen SMALLINT ) RETURNS VARCHAR(255)BEGINDECLARE randStr VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';DECLARE i SMALLINT DEFAULT 0;DECLARE resultStr VARCHAR(255) DEFAULT '';WHILE i<strlen DOSET resultStr=CONCAT(SUBSTR(randStr,FLOOR(RAND()*LENGTH(randStr))+1,1),resultStr);SET i=i+1;END WHILE;RETURN resultStr;END