mysql| Data | It is interesting to have discussed this problem in the group before randomly. MySQL's syntax is really fun. They all wanted to use PHP to implement the random, but take out more than two times as if to do the query. Turn the manual, find the following statement, you can complete the task.
SELECT * FROM table_name ORDER BY RAND () LIMIT 5;
That's what Rand said in the handbook:
RAND ()
RAND (N)
Returns a random floating-point value in the range 0 through 1.0. If an integer parameter n is specified, it is used as a 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 the rand () value in an ORDER BY clause, because an order by will repeatedly compute the column multiple times. In MySQL3.23, however, you can do this: SELECT * FROM table_name ORDER BY RAND (), which is advantageous to getting a from SELECT * from Table1,table2 WHERE a=b and C<d O A random sample of the collection of Rder by RAND () LIMIT 1000. Note that a rand () in a WHERE clause will be reassessed each time the where is executed.
But I tried it, 8,000 records of the table, the execution once needed 0.08 sec. a little slower.
Later consulted Google, get 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;
Execution efficiency requires 0.02 sec. Unfortunately, only MySQL 4.1.* above supports such subqueries.