You cannot use a column with a RAND() value in an ORDER BY clause because ORDER BY will recompute the column multiple times. However, in MySQL3.23, you can do: SELECT * FROM table_name ORDER BY RAND(), which is helpful to get a SELECT * FROM table1,table2 WHERE a=b AND c to achieve randomness. I found the following from the manual Statement to complete the task:
SELECT * FROM table_name ORDER BY rand() LIMIT 5;
rand says this in the manual::
RAND()
RAND(N)
Returns a random floating point value in the range 0 to 1.0. If an integer parameter N is specified, it is used as the seed value.
mysql> select RAND();
-> 0.5925
mysql> select RAND(20);
-> 0.1811
mysql> select RAND(20);
-> 0.1811
mysql> select RAND();
-> 0.2079
mysql> select RAND();
-> 0.7888
You cannot use a column with a RAND() value in an ORDER BY clause because ORDER BY will recompute the column multiple times. However, in MySQL3.23, you can do: SELECT * FROM table_name ORDER BY RAND(), which is helpful to get a SELECT * FROM table1,table2 WHERE a=b AND c
Note that a RAND() in a WHERE clause will be re-evaluated each time WHERE is executed.
But I tried it. For a table with 8,000 records, it takes 0.08 sec to execute once, which is a bit slow. Later, I consulted Google and got the following code:
SELECT * FROM table_name AS r1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM table_name)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 5;
The execution efficiency requires 0.02 sec. Unfortunately, only mysql 4.1.* and above support such subqueries.