Introduction of the RAND function random fetching data in MySQL

The code is as follows	Copy Code
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.

code is as follows	copy code
mysql> Select RAND ();        -> 0.5925 mysql> select RAND;         -> 0.1811 mysql> select RAND;        - > 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.

The net is basically querying max (ID) * RAND () to get data randomly.

The code is as follows	Copy Code
SELECT * From ' table ' as T1 JOIN (select ROUND (RAND () * (SELECT MAX (ID) from ' table ') as ID) as T2 WHERE t1.id >= t2.id ORDER BY T1.id ASC LIMIT 5;

But this will produce a continuous 5 records. The solution can only be one query at a time, query 5 times. Even so, because of the 150,000 table, the query only needs 0.01 seconds.

The above statement uses a join,mysql forum where someone uses

The code is as follows	Copy Code
SELECT * From ' table ' WHERE ID >= (SELECT FLOOR (MAX (ID) * RAND ()) from ' table ' ORDER by ID LIMIT 1;

I tested it, it takes 0.5 seconds, and it's pretty good, but there's still a big gap with the statement above.

Later consulted Baidu, get the following code

The full query statement is:

The code is as follows	Copy Code
SELECT * from ' table ' WHERE ID >= (select floor (ID) from ' table ') + (select min (ID) () () + (()) + (() () + (() () () () + ("()") M ' table ')) ORDER by ID LIMIT 1;

The code is as follows	Copy Code
SELECT * From ' table ' as T1 JOIN (select ROUND (select MAX (ID) from ' table ') (select min (IDs) from ' tables ')) + (select min () d) from ' table ') as ID) as T2 WHERE t1.id >= t2.id ORDER by t1.id LIMIT 1;

Finally in PHP, the two statements are queried separately 10 times,
The former takes 0.147433 seconds.
The latter takes time 0.015130 seconds

Execution efficiency requires 0.02 sec. Unfortunately, only MySQL 4.1.* above supports such subqueries.

Note View the official manual, also said Rand () in the ORDER BY clause will be executed many times, natural efficiency and very low.