MySQL random string longer than 32 characters

Question

I am trying to generate a 36 character random string in MySQL using:

UPDATE my_table SET entity_uid = substring(MD5(RAND()) FROM 1 FOR 36);

but the result is always a 32 character string. Is there a way to get a longer string?

Answer 1

If you use MySQL with version higher than 5.7.4, you can use the newly added RANDOM_BYTES function:

 SELECT TO_BASE64(RANDOM_BYTES(40));

This will result in a random string such as r633j3sfgE85f3Jz+3AEx6Xo6qPXPUZruNimhId18iy+J1qOgZyCgg==.

Answer 2

One option would be to generate two MD5 hashes, concatenate them together (for a total of 64 hex characters), and then take the first 36 characters of that:

SELECT SUBSTR(CONCAT(MD5(RAND()),MD5(RAND())),1,36)

(NOTE: an MD5 hash is 128-bits; the MySQL MD5() function returns 32 hex characters.)

Answer 3

UPDATE my_table SET entity_uid = UUID();

Answer 4

MD5 Returns the hash as a 32-character hexadecimal number.

According to MySQL

Calculates an MD5 128-bit checksum for the string. The value is returned as a string of 32 hex digits, or NULL if the argument was NULL. The return value can, for example, be used as a hash key. See the notes at the beginning of this section about storing hash values efficiently.