MySQL randomly extracts a certain number of records

In the past, I used to directly order by rand() to handle this kind of usage scenario, but the efficiency was really not satisfactory. So I encountered this scenario again recently and looked for a better solution online.

1.order by rand()

Writing method:

SELECT
  id
FROM
  `table`
ORDER BY
  rand()

The disadvantage of this writing method is that the rand function is executed multiple times in the order by, affecting efficiency.

2. max(id) * rand() using join

Writing method:

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;

The big guys on the Internet all recommend the second way of writing, so I'd like to record it down. I feel that the maximum id and the minimum id can be calculated in the program.

The problem here is that if you take multiple records, they must be continuous, so if you don't want to take continuous data, you have to loop. However, this statement is extremely efficient, so loop query can be done.

The above are all the relevant knowledge points. Friends in need can learn from them. Thank you for your support of 123WORDPRESS.COM.