Generate random string in MySQL

Jul

2008

Submitted by Nick on Thu, 10th Jul 2008, 08:51 and has been commented on 3 times.

Tagged in Geek, Howto, Mysql, Programming & Security

Have you ever wanted to generate a random string in MySQL, say for assigning a random password to a list of users? Well here is a useful tip!

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6) AS password

This creates a 6 character (easy to change, as you can see) string where the characters are from the MD5 command (and therefore in the range a-z and 0-9).

3 Comments

The most recent comment was on Mon, 22nd Feb 2010, 22:26

Excellent tip - this would

Jul

2008

Submitted by CaptTofu (not verified) on Wed, 23rd Jul 2008, 23:29.

Excellent tip - this would allow you to have a function for generating md5 keys for sessions instead of perl code... put it all in the db.

Thank for MYSQL random string

Nov

2008

Submitted by Abhijeet (not verified) on Thu, 20th Nov 2008, 02:20.

Thank for MYSQL random string generator help.

Really nice

avoiding accidental bad words

Feb

2010

Submitted by Rob L (not verified) on Mon, 22nd Feb 2010, 22:26.

Great tip!

For randomly assigned codes & passwords, we use a restricted alphabet that does not include vowels (or the digits 0 and 1 because they can resemble vowels). This is to avoid accidentally assigning a bad word, since virtually all bad words have at least one vowel.

Furthermore, our defaults are all uppercase.

Luckily we can slightly modify your tip with the mysql REPLACE and UPPER functions. Unfortunately, REPLACE doesn't seem to support regex and UPPER requires a call to CONVERT before it will work properly. So the code is a little convoluted, but it does seem to work fine...

SELECT UPPER(CONVERT(SUBSTRING(
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                MD5(RAND())
              ,'1','')
            ,'0','')
          ,'a','')
        ,'e','')
      ,'i','')
    ,'o','')
  ,'u','')
FROM 1 FOR 6) USING latin1)) AS password