MySQL randomly fetches one or more data

To get a random integer R in the range of I ≤r≤ J , you need to use the expression floor (RAND () * (J - i) +i), Rloor () Take the whole tree part, RAND (), ROUND (x,n) rounding,ROUND (x,n) to randomly generate a number of n decimal places between 0~x .

For example, to get a random integer within the range of 7 to 12, including 7 and 12, use the following statement:

SELECT Floor (RAND () * (12-7) +7)

It's not difficult to read data randomly from a Mysql table, but it's a lot of ways, but if you want to think about efficiency, get a quick and efficient way.

There are a number of ways to randomly get one or more records of a MySQL datasheet, with users (Userid,username,password ... ) table, Bedovin above records as an example:

1. General use method

SELECT * from the Users ORDER by RAND () LIMIT 1

Conclusion: The efficiency is slowest, and the rand () function cannot be used in an ORDER by clause because it causes the data column to be scanned multiple times, resulting in a fairly low efficiency!

2. SELECT * from users as U0

JOIN

(Select Floor (RAND () * (select MAX (U1.userid) from users as U1)-(select min (u2.userid) from user as U2) + (select min (U3. UserID) from users as U3) as UserID) as U4

On u0.userid>= U4.userid

LIMIT 1

Conclusion: A random data efficiency is OK, more than one can not

3. Obtain the maximum and minimum values via SQL, then generate a random number randnum via PHP rand, and then through the select * from users WHERE userId >= randnum LIMIT 1

SELECT * from Users WHERE

users.userid>= Floor (RAND () * ([Select MAX (U1.userid) from users as U1)-(select MIN (U2.userid) from user as U2) + (Sele CT MIN (U3.userid) from users as U3))

ORDER by Users.userid ASC

LIMIT 1

Conclusion: Random take one or more records, the method is good! (Best solution)

Attached: Field (Column,value1,value2,value3,......) represents the order in which the columns are customized according to column

MySQL randomly fetches one or more data