List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 15 2011 7:05pm
Subject:Re: MySQL RAND() Issues [was Re: How to Shuffle data]
View as plain text  
In the last episode (Jul 15), Arthur Fuller said:
> This would be sooo much simpler to solve in MS-SQL, given the function
> NewID(), which is guaranteed to return a unique value.  I have used this
> in a few web sites and it works splendidly; something along the lines of
> 
> SELECT TOP 10 *, NewID() FROM User_Messages ORDER BY NewID
> 
> which is guaranteed to produce a new GUID for each row, and then order by
> said values.  It is not guaranteed to produce 10 results different than
> the previous SELECT, but nevertheless works extremely well on a web site.
> 
> I have Googled this and that but not yet succeeded in finding the
> equivalent in the MySQL world.  In theory, this should be relatively easy,
> since the GUID docs are available, but I'm out of my depth here on how to
> write the MySQL equivalent function.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid

The UUID() function is that you want.  It generates a GUID just like
NewID(), but the problem is that a GUID is only guaranteed to be unique, not
random:

mysql> select uuid() union select uuid() union select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 11a5cfd1-af13-11e0-80f5-0019b9df7547 |
| 11a5d054-af13-11e0-80f5-0019b9df7547 |
| 11a5d092-af13-11e0-80f5-0019b9df7547 |
+--------------------------------------+

(your output may vary depending on your OS'es preferred type of UUID to
generate).  You could use md5(uuid()), however, which hashes your unique
values to get a nice large pseudorandom number:

mysql> select md5(uuid()) union select md5(uuid()) union select md5(uuid());
+----------------------------------+
| md5(uuid())                      |
+----------------------------------+
| 6faefaf3f7bb9ba0d1e7a44cf6a9b1da |
| 740135ab69a1825630aeaf475b39f8b8 |
| 5c91a9132ad3e49e098e41d573de8e00 |
+----------------------------------+


-- 
	Dan Nelson
	dnelson@stripped
Thread
MySQL RAND() Issues [was Re: How to Shuffle data]Jan Steinman15 Jul
  • Re: MySQL RAND() Issues [was Re: How to Shuffle data]Arthur Fuller15 Jul
    • Re: MySQL RAND() Issues [was Re: How to Shuffle data]Michael Dykman15 Jul
    • Re: MySQL RAND() Issues [was Re: How to Shuffle data]Dan Nelson15 Jul
  • Re: MySQL RAND() Issues [was Re: How to Shuffle data]Reindl Harald15 Jul