MySQL random query matches the criteria of several records

Random query, the method can have many kinds. For example, all records are queried, and N records are randomly taken from the list. The use of the program can be implemented. However, the program implementation must query all qualifying records (at least all eligible record IDs) and then randomly fetch n IDs to query the database. But efficiency, after all, no database directly query fast. Here's how to randomly query N records in MySQL.

1. The simplest way to order by rand (), example

Select *  from where Q. 'level '=1orderbyRand1;

This writing, can be the query out of the result set, limit n Records, get n Random Records, the N records are random order, is a bit slow, but very random.

2. If the record ID keeps growing continuously, the middle is uninterrupted, you can substitute the above statement in other ways, example

#随机查询 (record greater than a certain number, high efficiency)SelectQ1.*  fromQuestion Q1Inner Join(Select(min(q2.id)+ round(Rand()*(Max(q2.id)- min(q2.id)))) asId fromQuestion Q2whereQ2. ' Level`=1) asT onQ1.id>=T.id limit1; #效率略低SelectQ.*  fromQuestion QwhereQ.id>(SelectT.id from (    Select(min(q2.id)+ round(Rand()*(Max(q2.id)- min(q2.id)))) asId fromQuestion Q2whereQ2. ' Level`=1) t) limit1; #效率极低, than Order byRand is also low (may be subquery for each record, results are not discontinuous, very random)SelectQ.*  fromQuestion QwhereQ.id>(Select(min(q2.id)+ round(Rand()*(Max(q2.id)- min(q2.id)))) fromQuestion Q2whereQ2. ' Level`=1) limit1;

The implementation of Method 2 is to find the ID range of the qualifying record [MINID,MAXID], and then randomly generate an ID in the range, the algorithm is Id=minid+[0,maxid-minid], [0,maxid-minid] Can be implemented using the round rounding function and the rand random function. Then a record greater than or equal to this ID is a random record that matches the criteria. The above notation is only for querying a record. If the query has n records, the SQL statement changes to:

SelectQ1.*  fromQuestion Q1Inner Join(Select(min(q2.id)+ round(Rand()*(Max(q2.id)-2 - min(q2.id)))) asId fromQuestion Q2whereQ2. ' Level`=1) asT onQ1.id>=T.id limit3;

As above, randomly take 3 consecutive records, Max's value minus two, that is, the range is reduced by 2, to ensure that the random ID, greater than equal to it can still detect 3 records.

If maxid-(n-1)-minid is negative, that is, there is no N records in the data record range, the above statement is an error, the improved version is as follows:

SelectQ1.*  fromQuestion Q1Inner Join (    Select(min(q2.id)+ round(Rand()*( Case  when(Max(q2.id)-2)>min(q2.id) Then Max(q2.id)-2 - min(q2.id)Else 0 End))) asIdmin(q2.id) asMiniD,Max(q2.id) asMaxid fromQuestion Q2whereQ2. ' Level`=2)  asT onQ1.id>=T.id andQ1.idbetweenT.minid andT.maxid limit3;

Enclose the test code for the random function:

Select Ten*RAND(); #[0,10) Select Floor (10*rand ()); #[0,9]Select CEILING(Ten*RAND()); #[1,10]Select ROUND(Ten*RAND()); #[0,10]

MySQL random query matches the criteria of several records