Generating Unique and Random Code Digits with MySQL
I would like to generate random and unique codes (6 digits) in a table. I am using a SQL query like this:
SELECT SUBSTRING(CRC32(RAND()), 1, 6) as myCode FROM `codes` HAVING myCode NOT IN (SELECT code FROM `codes`)
I asked how this would react when there are no more codes available, so I am doing the following test
MySQL version: 5.5.20
CREATE TABLE `codes` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `code` VARCHAR( 10 ) NOT NULL , UNIQUE ( `code` ) ) ENGINE = InnoDB;
INSERT INTO `codes` (`id`, `code`) VALUES (NULL, '1'), (NULL, '2'), (NULL, '3'), (NULL, '4'), (NULL, '5'), (NULL, '6'), (NULL, '7'), (NULL, '8');
SELECT SUBSTRING(CRC32(RAND()), 1, 1) as myCode FROM `codes` HAVING myCode NOT IN (SELECT code FROM `codes`)
While doing this query, I expect it to always return 9, because that is the only one-digit code that doesn't exist.
But the result is:
- Sometimes it returns any strings
- Sometimes it returns strings with values that already exist
I don't understand this behavior, so if anyone can help :)
So the big question is:
How can MySQL return rows with values that already exist?
source to share
I would populate the table with
all possible values in sequence.
Then the random query just randomly fetches records from
, and every time it selects a record, it deletes it. Thus, you will definitely get all the digits without wasting time looking for a "hole" number (not having picked it up yet).
CREATE TABLE `sequencetable` ( `sequence` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (`sequence`) ) ENGINE=InnoDB AUTO_INCREMENT=1;
Complete the sequence (no need for AUTOINCREMENT).
DECLARE i INT; SET i=1; REPEAT INSERT INTO sequencetable VALUES (i); SET i=i+1; UNTIL i>999999 END REPEAT;
Select a random entry from the sequence (do this in a loop until no entries are available):
DECLARE sequencen INT; SET sequencen = (SELECT sequence FROM sequencetable ORDER BY RAND() LIMIT 1); DELETE FROM sequencetable WHERE sequence = sequencen;
source to share