MySQL Random Unique String

Question

I'm writing a 'reset password' facility for a website and as part of it I need to generate a random, unique string of characters to be inserted into MySQL database.

As of now this is simply done using SHA1(RAND()) and it works perfectly. However that only gives around 1.46150164E48 (for SHA1 I'm not so sure what the unique range for RAND is) different combinations and being pedantic I want to ensure that a situation where two identical values are inserted never happens.

Now I could easily do it with PHP (as this is PHP-based website), iterating through a loop until a random string is generated that doesn't exist - which in most cases would be 1 iteration, but I was wondering if this is possible with MySQL?

That is: Is it possible, in MySQL, to generate a pseudo-random string of characters that MySQL itself will ensure is unique in the given table and field?

Answer 1

You can also make use of UUID() global function that generates a unique value every time:

SELECT UUID()