Generating Unique and Random Code Digits with MySQL
Initial target:
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
Test context:
MySQL version: 5.5.20
MySQL table:
CREATE TABLE `codes` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`code` VARCHAR( 10 ) NOT NULL ,
UNIQUE (
`code`
)
) ENGINE = InnoDB;
Initial data:
INSERT INTO `codes` (`id`, `code`)
VALUES (NULL, '1'), (NULL, '2'), (NULL, '3'), (NULL, '4'), (NULL, '5'), (NULL, '6'), (NULL, '7'), (NULL, '8');
SQL query:
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?
thank
source to share
I would populate the table with sequencetable
all possible values in sequence.
Then the random query just randomly fetches records from sequencetable
, 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