Insert unique random number with recursive MySQL-CodePudding

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-1AND 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!.

CodePudding user response：

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.

CodePudding user response：

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;