Insert unique random number with recursive MySQL

Question

I want to populate a table with unique random numbers without using a procedure.

I've tried using this reply to do it but not success.

What I'm trying to do is something like this but validating that numbers are not repeated:

INSERT into table (row1,row2)
WITH RECURSIVE
cte AS ( SELECT 0 num, LPAD(FLOOR(RAND() * 99999999),8,0) random_num 
         UNION ALL 
         SELECT num+1, LPAD(FLOOR(RAND() * 99999999),8,0) random_num 
         FROM cte WHERE num < 100000-1)
SELECT random_num, null
FROM cte;

In the example above, I am able to generate random values and insert them but without validating that the numbers are not repeated.

I have tried to do this:

INSERT into table (row1,row2)
WITH RECURSIVE
cte AS ( SELECT 0 num, LPAD(FLOOR(RAND() * 99999999),8,0) random_num 
         UNION ALL 
         SELECT num+1, LPAD(FLOOR(RAND() * 99999999),8,0) random_num 
         FROM cte WHERE num < 100000-1 AND random_num NOT IN (SELECT random_num FROM cte WHERE random_num IS NOT NULL))
SELECT random_num, null
FROM cte;

but the condition AND random_num NOT IN (SELECT random_num FROM cte WHERE random_num IS NOT NULL) in the where case, causes an SQL Error [4008] [HY000]: Restrictions imposed on recursive definitions are violated for table 'cte'

Any suggestions of how to do it? thank you!.

Use the logic from the above r-cte but (a) select a little more than 100000 rows (b) select distinct from the rcte `SELECT distinct random_num, null FROM cte`. BTW, you don't need rcte; just one table with a dozen rows. — Salman A, Dec 07 '21 at 13:11
Thank you @SalmanA I already found this solution but I was wondering if there is any better solution than this. — FdelS, Dec 07 '21 at 13:20
Not meaning to split hairs ... but you're using a fairly specific definition of random if you're insisting they're unique. Have you considered some sort of custom pseudo-random number generation using `RAND()` as a seed? (Like repeatedly rehashing or `XOR`?) — wally, Dec 07 '21 at 13:30

Salman A · Answer 1 · 2021-12-07T13:34:06.057

1

If you have a table - any table - with e.g. 100 rows then you can generate million distinct random numbers between 0 and 99999999 as follows:

select distinct floor(rand() * 100000000)
from t as t0, t as t1, t as t2
limit 1000000

Note that because of distinct you will need to generate a bigger number of rows so that you get desired number of rows after distinct.

edited Dec 07 '21 at 13:34

answered Dec 07 '21 at 13:24

Salman A

262,204
82
430
521

score 1 · Accepted Answer · answered Dec 07 '21 at 14:14

This could be an option. Generate all possible values, sort randomly and take desired number of entries.

CREATE TABLE random_data (
  row1 INT PRIMARY KEY AUTO_INCREMENT,
  row2 VARCHAR(10) NOT NULL,
  UNIQUE KEY _Idx1 ( row2 )
);

INSERT INTO random_data (row2)
SELECT LPAD(num, 8, 0)
FROM (
  SELECT h * 10000000 + g * 1000000 + f * 100000 + e * 10000 + d * 1000 + c * 100 + b * 10 + a AS num
  FROM (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) ta
  JOIN (SELECT 0 b UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tb
  JOIN (SELECT 0 c UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tc
  JOIN (SELECT 0 d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) td
  JOIN (SELECT 0 e UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) te
  JOIN (SELECT 0 f UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tf
  JOIN (SELECT 0 g UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tg
  JOIN (SELECT 0 h UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) th
) n
ORDER BY RAND()
LIMIT 100000;

Insert unique random number with recursive MySQL

2 Answers2