MySQL random with specific options

Question

I needed to get 20 random rows from the database. I got this query:

SELECT * FROM `table_name` WHERE 1 ORDER BY rand() LIMIT 20

Everything works fine, but there is 1 problem.

• If there are less than 20 rows in the database, for example 13, then how to duplicate 7 more random rows until in the answer is exactly 20 rows?

P.S. Don't worry about the emptiness. The table is never empty.

Answer 1

You could achieve this with a stored procedure. This one selects random rows into a temporary table until the desired number of rows has been inserted. If there are enough rows in the source table, only one insert into the temporary table, of enough random rows to satisfy the request. If not, the entire contents of the table are inserted into the temporary table enough times to fill it. For example, when selecting 20 random entries from a table with only 8 rows, it will insert all 8 rows of the table (in random order) into the temporary table, then all 8 rows again (in a different random order) and then finally 4 random rows. Then the entire contents of the temporary table are returned.

DELIMITER //
DROP PROCEDURE IF EXISTS select_random //
CREATE PROCEDURE select_random (IN tablename VARCHAR(256), IN numrows INT)
BEGIN
  DECLARE rowcnt INT DEFAULT numrows;
  SET @q = CONCAT('CREATE TEMPORARY TABLE random LIKE ', tablename);
  PREPARE stmt FROM @q;
  EXECUTE stmt;
  SET @q = CONCAT('SELECT COUNT(*) INTO @totrows FROM ', tablename);
  PREPARE stmt FROM @q;
  EXECUTE stmt;
  REPEAT
    SET @q = CONCAT('INSERT INTO random SELECT * FROM ', tablename, ' ORDER BY RAND() LIMIT ', LEAST(@totrows, rowcnt));
    PREPARE stmt FROM @q;
    EXECUTE stmt;
    SET rowcnt = rowcnt - @totrows;
  UNTIL rowcnt <= 0
  END REPEAT;
  SELECT * FROM random;
  DROP TABLE random;
END
//
CALL select_random('table_name', 20);