MySQL 8 Recursive CTE generate number of rows

Question

I would like to generate n count of random rows by using MySQL CTE. So far I have:

WITH RECURSIVE cte AS
(
    SELECT LEFT(MD5(RAND()),8) AS `three`
    UNION ALL
    SELECT LEFT(MD5(RAND()),8) AS `three`
    WHERE `rn` < 10
)
SELECT * FROM cte
;

How to make the condition

WHERE `rn` < 10

working, assuming rn is a rows number returned by the recursive query?

I know that you may use:

SELECT ROW_NUMBER() OVER () AS `rn`

to get the row number but how to use it? Is there any alternative solution?

I'm using MySQL 8.

Answer 1

You can add a "control" column in the CTE, that counts from one up. Then you can limit the recursive step to end when the value in that column exceeds ten (or what ever n is). In the outer SELECT then just select the column with the random values, but not the "control" column.

WITH RECURSIVE cte
AS
(
SELECT left(md5(rand()), 8) three,
       1 i
UNION ALL
SELECT left(md5(rand()), 8) three,
       i + 1 i
       FROM cte
       WHERE i + 1 <= 10
)
SELECT three
       FROM cte;

db<>fiddle