There’s no built-in way to generate a random string in MySQL, so you need to create a workaround using the provided functions.
One of the easiest ways to generate a random string is to use a combination of the SUBSTR() function, the MD5() function, and the RAND() function.
MySQL random string with MD5 and RAND functions
The MD5() function is used to generate an MD5 128-bit checksum representation of a string.
The example below shows how the MD5() function returns the checksum for the string garden:
SELECT MD5("garden") AS checksumResult;
-- +----------------------------------+
-- | checksumResult |
-- +----------------------------------+
-- | e2704f30f596dbe4e22d1d443b10e004 |
-- +----------------------------------+
The checksum result is always 32 alphanumeric characters generated from the argument you passed into the MD5() function.
To always generate a random result each time you call the MD5() function, you need to use the RAND() function to generate a random number as shown below:
SELECT MD5(RAND()) AS checksumResult;
-- +----------------------------------+
-- | checksumResult |
-- +----------------------------------+
-- | ca2df17b8a83eb5ed566ce398c34f19d |
-- +----------------------------------+
SELECT MD5(RAND()) AS checksumResult;
-- +----------------------------------+
-- | checksumResult |
-- +----------------------------------+
-- | a7c7693485902ac7ec5d10d8bd40088f |
-- +----------------------------------+
Now you only need to use the SUBSTR() method to extract a part of the MD5 string as you require.
Let’s replace the RAND() function with the string garden again so you can see how the SUBSTR() method works.
Notice how the example below uses SUBSTR() to extract the first 10 characters from the checksum:
SELECT MD5("garden") AS checksumResult;
-- +----------------------------------+
-- | checksumResult |
-- +----------------------------------+
-- | e2704f30f596dbe4e22d1d443b10e004 |
-- +----------------------------------+
SELECT SUBSTR(MD5("garden"), 1, 10) AS checksumResult;
-- +----------------+
-- | checksumResult |
-- +----------------+
-- | e2704f30f5 |
-- +----------------+
Now replace the string garden with RAND() again. This time, you’ll have 10 random characters each time you call the function:
SELECT SUBSTR(MD5(RAND()), 1, 10) AS randomString;
-- +--------------+
-- | randomString |
-- +--------------+
-- | 20a105a43e |
-- +--------------+
If you want only 8 characters, then change the third argument for the SUBSTR() function from 10 to 8 as shown below:
SELECT SUBSTR(MD5(RAND()), 1, 8) AS randomString;
And that’s how you generate a random string of characters using the MD5() and RAND() functions.
