Generate unique and random code digit with MySQL

Question

Initial goal:

I would like to generate random and unique codes (6 digits) in a table. I use a SQL query like this one to do that:

SELECT SUBSTRING(CRC32(RAND()), 1, 6) as myCode
FROM `codes`
HAVING myCode NOT IN (SELECT code FROM `codes`)

I asked me about how it will react when there will be no more available codes so I do 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`)

By execute this query, I expect that it will always return 9 because it is the only code of one digit which does not exists.

But the result is:

Sometime it return any rows
Sometime it return rows with values that already exists

I don't understand this behavior so if someone can help :)

So the big question is:

How MySQL can return rows with values that already exists?

Thanks

Just use AUTO_INCREMENT. While not random it will be unique. The requirement to be random appears to be value free. — DwB
– DwB, Commented Mar 9, 2012 at 9:54

vulkanino · Accepted Answer · 2012-03-09 10:08:12Z

2

I would fill a sequencetable table with all the possible values, in sequence.

Then the random query just randomly selects records from the sequencetable, and each time it picks a record it deletes it. This way you will surely get all the numbers, without wasting time in finding a "hole" number (not already picked up).

CREATE TABLE `sequencetable` 
(
    `sequence` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sequence`)
)
ENGINE=InnoDB
AUTO_INCREMENT=1;

Fill the sequence (no need for the AUTOINCREMENT actually).

DECLARE i INT;

SET i=1;
REPEAT
    INSERT INTO sequencetable VALUES (i);
    SET i=i+1;
UNTIL i>999999 END REPEAT;

Select a random record from the sequence (do this in a loop until records are available):

DECLARE sequencen INT;

SET sequencen = 
    (SELECT sequence FROM sequencetable ORDER BY RAND() LIMIT  1);

DELETE FROM sequencetable WHERE sequence = sequencen;

edited Mar 9, 2012 at 10:08

answered Mar 9, 2012 at 9:44

vulkanino

9,1347 gold badges47 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CocoRambo Over a year ago

Thanks for your answer Vulkanino I will certainly go through your solution but more than an alternative solution I don't understand the MySQL behavior... How MySQL can return values that I excluded with this part of my query: HAVING myCode NOT IN (SELECT code FROM codes)

vulkanino Over a year ago

SQL doesn't work that way, you want to exclude records as they are selected!

CocoRambo Over a year ago

What do you mean by "you want to exclude records as they are selected" ?

Collectives™ on Stack Overflow

Generate unique and random code digit with MySQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related