Mysql random Query

Welcome to the Linux community forum and interact with 2 million technical staff. 1. Randomly query a data method. Method 1: SELECT * FROM 'table' ORDERBYRAND () limit1. Evaluation: not recommended, the efficiency is very low. It is explained in the official documentation that the combination of OrderBy and RAND () will scan the table multiple times, resulting in a slow speed. Method

Welcome to the Linux community forum and interact with 2 million technical staff> go to 1. Randomly query a data method 1: SELECT * FROM 'table' order by rand () limit 1. Comments: it is not recommended, and the efficiency is very low. It is described in the official documents that Order By and RAND () can be used to scan the table multiple times, resulting in slow speed. Method

Welcome to the Linux community forum and interact with 2 million technicians>

1. Randomly query a piece of data

Method 1:

SELECT * FROM 'table' order by rand () limit 1

Rating: it is not recommended and the efficiency is very low. It is described in the official document that Order By and RAND () can be used to scan the table multiple times, resulting in slow speed.

Method 2:

SELECT * FROM 'table'

WHERE id> = (SELECT floor (RAND () * (select max (id) FROM 'table ')))

Order by id LIMIT 1;

Explanation: select max (id) FROM 'table' queries the largest id value.

SELECT floor (RAND () * (select max (id) FROM 'table '))

Obtain a random number smaller than MAX (id ).

WHERE id> = (SELECT floor (RAND () * (select max (id) FROM 'table ')))

This statement filters out all rows with IDs greater than the generated random number.

In the end, query the rows greater than this random id, sort the rows by id, and select the first one, which is equivalent to obtaining a random row in all rows.

Rating: there is a problem. If the id is not FROM 0, for example, FROM 10000, SELECT floor (RAND () * (select max (id) FROM 'table '))

The result will be a very high probability of getting a value smaller than 10000. The where-qualified query results will increase the probability of all query results, the last result obtained by limit 1 is that the probability of data in the first row increases.

Method 3:

SELECT * FROM 'table'

WHERE id> = (SELECT floor (RAND ()*

(Select max (id) FROM 'table')-(select min (id) FROM 'table') + (SELECTMIN (id) FROM 'table ')))

Order by id LIMIT 1;

Method 4:

SELECT *

FROM 'table' AS t1 JOIN (select round (RAND ()*

(Select max (id) FROM 'table')-(select min (id) FROM 'table') + (select min (id) FROM 'table '))

AS id)

AS t2

WHERE t1.id> = t2.id

Order by t1.id LIMIT 1;

Comment: solved the problem of MAX (id) in method 2, RAND () * (select max (id) FROM 'table')-(select min (id) FROM 'table') + (SELECTMIN (id) FROM 'table') can obtain random numbers in MAX (id) and MIN (id.

The above solutions all have a non-repeated numeric field by default. In fact, many tables are designed to use an auto-incrementing segment as the primary key, and some of them use uuid as the primary key, without a number key, you can use the mysql function to convert the uuid string to a number. Another problem is that, if the number distribution of the id field is uneven (such as distribution by, and 45), it will also lead to unreasonable random queries, however, we will not discuss such complicated issues here.

Ii. Randomly query multiple data entries

Method 1: query the limit of a random data record.

1 to limit 5

Comment: the obtained data will be continuous.

Method 2:

SELECT *

FROM 'table' AS t1 JOIN (select round (RAND ()*

(Select max (id) FROM 'table')-(select min (id) FROM 'table') + (select min (id) FROM 'table '))

AS id from 'table' limit 50)

AS t2 on t1.id = t2.id order by t1.id LIMIT 1;

Explanation:

Select round (RAND () * (select max (id) FROM 'table')-(select min (id) FROM 'table') + (select min (id) FROM 'table') AS id

From 'table' limit 50) to obtain 50 random numbers, and then on

T1.id = t2.id: select random data of no more than 50 rows, and then take 5 rows.