JIYIK CN >

Today, we will learn to use various functions in MySQL to generate random and unique strings. These functions include MD5(), RAND(), , SUBSTR()and UUID().

There is no inbuilt method to generate random string in MySQL, but there are many other methods which can give us an advantage to meet the requirement. Here, we will learn about the simplest and most efficient way to generate random and unique string in MySQL.

Generate a random and unique string in MySQL using MD5(), RAND()andSUBSTR()

Sample code:

SELECT SUBSTR(MD5(RAND()),1,8) AS RandomString;

Output:

+--------------+
| RandomString |
+--------------+
| 7d192f5f     |
+--------------+
1 row in set (0.00 sec)

The MD5() function generates a 128-bit checksum representation of its argument. In the above example, the value generated by the RAND() function is MD5()the function's argument; RAND()the function generates a random value.

However, the checksum result is MD5()32 alphanumeric characters generated from the parameter passed to the function. Furthermore, we use SUBSTR()extract a portion of the string as per the requirement of our project MD5.

SUBSTR() takes three parameters: the string, the starting position to extract, and the length. In the sample code above, we MD5extracted 8 characters from the string, starting at the first character.

UUID()Generate a random and unique string in MySQL using

Sample code:

SELECT LEFT(UUID(), 8)

Output:

+-----------------+
| LEFT(UUID(), 8) |
+-----------------+
| 4a1f35bc        |
+-----------------+
1 row in set (0.00 sec)

Another way to generate an 8-character string in MySQL is LEFT(UUID(),8)as described above. RFC 4122 (Universally Unique Identifier URN Namespace) specifies UUID() (Universally Unique Identifier), which is a 128-bit long value.

It generates a globally unique value across time and space. Since UUID()the values ​​produced are unique, they are unpredictable or unguessable.

This means that UUID()a random value is always generated. We can use the following query to get a string of 8 or 10 characters long.

Sample code:

-- for 8 characters long string
SELECT LEFT(UUID(),8) random_string ;
-- for 10 characters long string
SELECT RIGHT(UUID(),10) random_string ;

Remember that UUID()we output a hexadecimal value consisting of five parts separated by hyphens. Therefore, we can get a string containing numbers, hyphens, and letters, depending on the desired string length.

Sample code:

SELECT UUID(),  LEFT(UUID(), 8), right(uuid(),20);

Output:

+--------------------------------------+-----------------+----------------------+
| UUID()                               | LEFT(UUID(), 8) | right(uuid(),20)     |
+--------------------------------------+-----------------+----------------------+
| b3e73af1-d811-11ec-b138-d8d09042fdaa | b3e73b03        | ec-b138-d8d09042fdaa |
+--------------------------------------+-----------------+----------------------+
1 row in set (0.00 sec)