使用MySQL生成唯一且随机的代码数字 [英] Generate unique and random code digit with MySQL

问题描述

初始目标:

我想在表格中生成随机且唯一的代码(6位数字). 我使用像这样的SQL查询来做到这一点:

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

测试环境:

MySQL版本:5.5.20

MySQL version: 5.5.20

MySQL表:

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');

SQL查询:

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

---

通过执行此查询，我希望它将始终返回9，因为它是唯一不存在的一位数字代码.

---

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.

但是结果是:

• 有时它返回任何行
• 有时它返回具有已经存在的值的行

我不了解这种行为，因此如果有人可以提供帮助:)

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

所以最大的问题是:

MySQL如何返回具有已经存在的值的行?

How MySQL can return rows with values that already exists?

谢谢

推荐答案

我将依次用所有可能的值填充sequencetable表.

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

然后，随机查询仅从sequencetable中随机选择记录，并且每次选择记录时都会将其删除.这样，您肯定会得到所有数字，而不会浪费时间寻找空"数字(尚未捡起).

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;

填写顺序(实际上不需要AUTOINCREMENT).

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;

这篇关于使用MySQL生成唯一且随机的代码数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！