I'am working on project. There is a person who sends the code under a bottle cap. I want to generate 1 million unique codes for it. The first 3 indexes should be uppercase letters and the last 2 should be number. Actually i want all the values which is 26*26*26*10*10
I tried the code below but it gave me non-unique values. I saw it when i counted.
DELIMITER $$
CREATE PROCEDURE randomizer()
BEGIN
DECLARE i INT DEFAULT 0 ;
DECLARE random CHAR(20) ;
myloop: loop
SET random=CONCAT(
CHAR(round(rand()*25)+65),
CHAR(round(rand()*25)+65),
CHAR(round(rand()*25)+65),
(round(rand()*25)+65)
);
INSERT INTO `passwords` (`pass`) VALUES (i+1,random) ;
SET i=i+1;
IF i=1757600 THEN
LEAVE myloop;
END IF;
END LOOP myloop;
END $$
DELIMITER;
I tried to make the column unique key but it showed an error which was 1136 - Column count doesn't match value count at row 1
. In my code it's generating the codes but they're all not unique.
I mentioned up in my comment that a Cross Join/Cartesian product would be a more efficient way to do this. I think something like the following would do the trick:
CREATE TABLE alpha (bet char(1));
CREATE TABLE numbers (num tinyint);
INSERT INTO alpha VALUES
('A'),
('B'),
('C'),
('D'),
('E'),
('F'),
('G'),
('H'),
('I'),
('J'),
('K'),
('L'),
('M'),
('N'),
('O'),
('P'),
('Q'),
('R'),
('S'),
('T'),
('U'),
('V'),
('W'),
('X'),
('Y'),
('Z');
INSERT INTO numbers VALUES
(0),
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9);
SELECT CONCAT(t1.bet,t2.bet,t3.bet,t4.num,t5.num)
FROM alpha t1, alpha t2, alpha t3, numbers t4, numbers t5;