Implementation of Mysql custom random string, mysql string

Implementation of Mysql custom random string, mysql 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; # returned result object here is the returnEND # The end ID is equivalent '}'

3. Add the Code directly to the custom function

The implementation of each sentence is not explained. You can refer to the Code explanation in method 2 for a look.

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

The above is the implementation method of Mysql custom random string introduced by xiaobian. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!