mysql's method of getting random data

  • 2020-06-01 11:12:03
  • OfStack

1.order by rand()
Data is very slow, randomness is very good, suitable for very small data volume.

SELECT * FROM table_name AS r1 JOIN (SELECT (ROUND(RAND() * (SELECT ABS(MAX(id)-MIN(id)-$limit) FROM table_name))+(SELECT MIN(id) from table_name)) AS id) AS r2 WHERE >= ORDER BY ASC LIMIT $limit; 

The speed was very fast, but the data obtained was continuous with id, which could not be solved.
The subquery generates a random number.
rand() is a random number from 0 to 1, which can be equal to 0 to 1.
MAX(id)-MIN(id). You should subtract $limit from MAX(id) so that you don't end up with less than $limit
Because the main table id may be discontinuous, is required >
Also note that sometimes the minimum id does not start at 1, but may start at a very large number, so you must add the value of M(id)
To obtain a random integer R in the range i ≤ R ≤ j, the expression FLOOR(i + RAND() * (j, i + 1)) is required.
For example, to get a random integer in a range of 7 to 12 (including 7 and 12), use the following statement:

SELECT FLOOR(7 + (RAND() * 6));
select * from test where rand()>0.0005 limit 10;

0.0005 is a calculated ratio directly related to the random quality. For example, if there are 10000 pieces of data in the data table and 300 pieces need to be randomly selected, then 0.0005 needs to be changed to (300/10000=0.03).
This method doesn't guarantee how many you can get at a time. You need to get it multiple times.
The speed of 2 and 3 is as fast as 1, the main difference is that 2 can always return limit data, 3 is not (in accordance with the normal distribution). Two is continuous data, three is random rows.

Related articles: