MySQL removes random data from a table

Guide: MySQLIt is open source code, and MySQL has received much attention due to its speed, reliability and adaptability. Most people think that MySQL is the best choice for managing content without the need for transactional processing. How to ImplementTableTo retrieve random data?

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

But I tried it. It takes 0.08 sec to execute a-thousand-record table. It's a little slower.

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. * And above support such subqueries.

Using the above method, we can retrieve random data from the MySQL implementation table. I hope everyone can master this technique, so that we can easily solve similar problems in future work.