How does the MySQL Database Retrieve random data from related tables?

This article mainly describes the actual operation scheme for MySQL database to retrieve random data from the relevant table. In order to achieve random data, we found the following statement in the manual, the task can be completed. The following describes the specific solution.

 
 

  
  
SELECT * FROM table_name ORDER BY rand() LIMIT 5;  
 
 

Rand says 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 (the best combination with PHP)> select RAND ();
  
  
-> 0.5925
  
  
MySQL (the best combination with PHP)> select RAND (20 );
  
  
-> 0.1811
  
  
MySQL (the best combination with PHP)> select RAND (20 );
  
  
-> 0.1811
  
  
MySQL (the best combination with PHP)> select RAND ();
  
  
-> 0.2079
  
  
MySQL (the best combination with PHP)> 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 MySQL (the best combination with PHP) 3.23, You can do: SELECT * FROM table_name order by rand (), which is conducive to obtaining a 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 after a try, it takes 0.08 sec to execute a record table, which is slower. Then 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 (the best combination with PHP). * And above support such subqueries.

The above content is an introduction to the MySQL database's random data extraction from the table. I hope you will gain some benefits.