Generate 6 random numbers without duplicates MySQL

Question

I need a smart way to generate 6 numbers without duplicates between 1 and 49 and insert each number to column. Below is my procedere created in phpmyadmin, which insert numbers with duplicate.

BEGIN

DECLARE liczba1 INT;
DECLARE liczba2 INT;
DECLARE liczba3 INT;
DECLARE liczba4 INT;
DECLARE liczba5 INT;
DECLARE liczba6 INT;
DECLARE check INT;
DECLARE count1 INT;
set count1 = 0;



while count1 < howMany DO
SELECT FLOOR(RAND()*(49-1)+1) into liczba1;
SELECT FLOOR(RAND()*(49-1)+1) into liczba2;
SELECT FLOOR(RAND()*(49-1)+1) into liczba3;
SELECT FLOOR(RAND()*(49-1)+1) into liczba4;
SELECT FLOOR(RAND()*(49-1)+1) into liczba5;
SELECT FLOOR(RAND()*(49-1)+1) into liczba6;
INSERT INTO kupony VALUES(NULL, 1, liczba1, liczba2, liczba3, liczba4, liczba5, liczba6, -1, '2018-01-01', -1);
set count1 = count1 + 1;
end while;


END

Create a table with all 49 numbers and then just select 6 random rows. — Paul Spiegel, May 29 '18 at 16:07

score 0 · Answer 1 · answered May 29 '18 at 15:25

can you insert your numbers from 9 on 9, like liczba1 between 1 to 9 then liczba2 between 10 to 18 then liczba3 between 19 to 27 ... like :

BEGIN

DECLARE liczba1 INT;
DECLARE liczba2 INT;
DECLARE liczba3 INT;
DECLARE liczba4 INT;
DECLARE liczba5 INT;
DECLARE liczba6 INT;
DECLARE check INT;
DECLARE count1 INT;
set count1 = 0;



while count1 < howMany DO
SELECT FLOOR(RAND()*(9-1)+1) into liczba1;
SELECT FLOOR(RAND()*(9-1)+9) into liczba2;
SELECT FLOOR(RAND()*(9-1)+18) into liczba3;
SELECT FLOOR(RAND()*(9-1)+27) into liczba4;
SELECT FLOOR(RAND()*(9-1)+36) into liczba5;
SELECT FLOOR(RAND()*(5-1)+45) into liczba6;
INSERT INTO kupony VALUES(NULL, 1, liczba1, liczba2, liczba3, liczba4, liczba5, liczba6, -1, '2018-01-01', -1);
set count1 = count1 + 1;
end while;


END

score 0 · Answer 2 · answered May 29 '18 at 15:44

Option 1:

You can figure up random numbers into a loop, exit the loop when all numbers are differents:

while count1 < howMany DO
    /* loop until all are differents */
    myloop: while 
      SELECT FLOOR(RAND()*(49-1)+1) into liczba1;
      SELECT FLOOR(RAND()*(49-1)+1) into liczba2;
      SELECT FLOOR(RAND()*(49-1)+1) into liczba3;
      SELECT FLOOR(RAND()*(49-1)+1) into liczba4;
      SELECT FLOOR(RAND()*(49-1)+1) into liczba5;
      SELECT FLOOR(RAND()*(49-1)+1) into liczba6;
      select n = count( distinct n ) 
      from ( select liczba1 union select liczba2 union ... ) T
      if n = 9 then 
          LEAVE myloop;
      end if;
    end while myloop
    /* here all are differents */
    INSERT INTO kupony ...
    ...

Disclaimer: not tested, may be some typos.

Option 2:

You can use a view to figure up some different random numbers and insert it:

/* https://stackoverflow.com/a/9751493/842935 */
CREATE OR REPLACE VIEW generator_16
AS 
   SELECT 0 n 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   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW get_6 
AS
  SELECT n FROM generator_16 ORDER BY rand() LIMIT 6;

DECLARE b1, b2, b3 INT;
DECLARE cur1 CURSOR FOR select * from get_6
OPEN cur1;
FETCH cur1 INTO b1;
FETCH cur1 INTO b2;
FETCH cur1 INTO b3;
CLOSE cur1;    
INSERT INTO kupony ...

Generate 6 random numbers without duplicates MySQL

2 Answers2