MySQL random query is a frequently used query method. The following describes the MySQL random query method. Let's first understand the basic syntax of MySQL random query:
- SELECT * FROM table_name ORDER BY rand() LIMIT 5;
-
Rand said this in the manual:
RAND ()
RAND (N)
Returns a random floating point value ranging from 0 to 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 the column with the RAND () value in an order by clause, because order by will calculate the column multiple times. However, in MySQL3.23, You can do: SELECT * FROM table_name order by rand (), which is helpful for obtaining a data from select * FROM table1, table2 WHERE a = B AND c <d ORDER BY RAND () LIMIT 1000 random samples of the set. Note that an RAND () in a WHERE clause will be re-evaluated every time the WHERE clause is executed.
But I tried it. It takes 0.08 sec to execute a-thousand-record table. It's a little slower.
Use the following statement:
- 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. * And above support such subqueries.
Optimization of MySQL query Paging
MySQL query results are sorted by a certain value
Use functions to query row numbers in MySQL
Non-empty question in MySQL Query
Troubleshooting of MySQL query timeout