The implementation method of Mysql custom random string _mysql

A few days ago in the development of a system, need to use a random string, but the MySQL library function is not directly provided, it is simple to use the existing functions to scrape out a random string. The following simple to say the implementation of the time.

1. Simple and rough.

Select ..., substring (MD5 (rand ()), Floor (rand () *26) +1,6) as Rand_str ...

The appeal example produces a random string of 6-bit lengths.

Function Explanation:

RAND (): Produces a decimal between 0-1, referred to as the seed. Rand () *25 produces a number between 0-25 and does not include 25

Floor (Val): The largest integer that produces the most recently received Val

MD5 (): MD5 encryption (one-way) of strings, resulting in a string length of 32 bits.

SUBSTRING (Str,pos,len): Intercepts the string, the first argument: the string to be intercepted, the second argument: the starting position (here some different, subscript start position is 1, you can try), the third parameter: the length of the interception.

2. Wrap the Mode 1: Custom function

Drop function if exists rand_str;
#第一句: If duplicate functions are present, delete the 
create function rand_str (strlen smallint) returns varchar (255)
#第二句: Define a function, name ' RAND_STR ' , parameter name strlen argument type smallint, return value type varchar (255), pay special attention to here is the returns below is returns
#BEGIN
#相当于左大括号 ' {'
DECLARE re Sult_str VARCHAR (255) DEFAULT ';
#声明返回值对象, type, length, default value
SET result_str =substring (MD5 (RAND ()), 32-strlen,strlen);
#设置返回值对象的值 Mode 1 simple and rough function return 
result_str;
#返回 Result Object Here is the return
end # Ending ID equivalent to '} '

3. Custom function directly on the code

The implementation of each sentence is not explained, you can participate in Mode 2 code interpretation look at

DROP FUNCTION IF EXISTS rand_str;
Create FUNCTION rand_str (strlen SMALLINT) RETURNS VARCHAR (255)
BEGIN
DECLARE randstr VARCHAR (255) DEFAULT ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 ';
DECLARE i SMALLINT DEFAULT 0;
DECLARE resultstr VARCHAR (255) DEFAULT ";
While I<strlen do
SET resultstr=concat (SUBSTR (Randstr,floor (RAND () *length (RANDSTR)) +1,1), resultstr);
SET i=i+1;
End While;
return resultstr;
End

The above is a small set to introduce the MySQL custom random string implementation method, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!