使用 MySQL 生成随机且唯一的 8 个字符串

Generating a random & unique 8 character string using MySQL

提问by funstein

I'm working on a game which involves vehicles at some point. I have a MySQL table named "vehicles" containing the data about the vehicles, including the column "plate" which stores the License Plates for the vehicles.

我正在开发一个在某些时候涉及车辆的游戏。我有一个名为“车辆”的 MySQL 表，其中包含有关车辆的数据，包括存储车辆牌照的“车牌”列。

Now here comes the part I'm having problems with. I need to find an unused license plate before creating a new vehicle - it should be an alphanumeric 8-char random string. How I achieved this was using a while loop in Lua, which is the language I'm programming in, to generate strings and query the DB to see if it is used. However, as the number of vehicles increases, I expect this to become even more inefficient it is right now. Therefore, I decided to try and solve this issue using a MySQL query.

现在是我遇到问题的部分。我需要在创建新车之前找到一个未使用的车牌 - 它应该是一个字母数字 8 字符的随机字符串。我如何实现这一点是在 Lua 中使用 while 循环（我正在编程的语言）来生成字符串并查询数据库以查看它是否被使用。然而，随着车辆数量的增加，我预计这会变得更加低效。因此，我决定尝试使用 MySQL 查询来解决这个问题。

The query I need should simply generate a 8-character alphanumeric string which is not already in the table. I thought of the generate&check loop approach again, but I'm not limiting this question to that just in case there's a more efficient one. I've been able to generate strings by defining a string containing all the allowed chars and randomly substringing it, and nothing more.

我需要的查询应该简单地生成一个 8 个字符的字母数字字符串，该字符串尚未出现在表中。我再次想到了 generate&check 循环方法，但我并没有将这个问题限制在这个问题上，以防万一有更有效的方法。我已经能够通过定义一个包含所有允许字符的字符串并随机将其子串化来生成字符串，仅此而已。

Any help is appreciated.

任何帮助表示赞赏。

采纳答案by Eugen Rieck

This problem consists of two very different sub-problems:

这个问题由两个非常不同的子问题组成：

• the string must be seemingly random
• the string must be unique

• 字符串必须看似随机
• 字符串必须是唯一的

While randomness is quite easily achieved, the uniqueness without a retry loop is not. This brings us to concentrate on the uniqueness first. Non-random uniqueness can trivially be achieved with AUTO_INCREMENT. So using a uniqueness-preserving, pseudo-random transformation would be fine:

虽然随机性很容易实现，但没有重试循环的唯一性则不然。这让我们首先关注唯一性。使用 可以轻松实现非随机唯一性AUTO_INCREMENT。因此，使用保留唯一性的伪随机转换就可以了：

• Hash has been suggested by @paul
• AES-encrypt fits also
• But there is a nice one: RAND(N)itself!

• @paul 建议使用哈希
• AES 加密也适合
• 但是有一个很好的：RAND(N)它本身！

A sequence of random numbers created by the same seed is guaranteed to be

由相同种子创建的随机数序列保证是

• reproducible
• different for the first 8 iterations
• if the seed is an INT32

• 可重复的
• 前 8 次迭代不同
• 如果种子是 INT32

So we use @AndreyVolk's or @GordonLinoff's approach, but with a seededRAND:

所以我们使用 @AndreyVolk 或 @GordonLinoff 的方法，但有一个种子RAND：

e.g. Assumin idis an AUTO_INCREMENTcolumn:

例如假设id是一AUTO_INCREMENT列：

INSERT INTO vehicles VALUES (blah); -- leaving out the number plate
SELECT @lid:=LAST_INSERT_ID();
UPDATE vehicles SET numberplate=concat(
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1)
)
WHERE id=@lid;

回答by RandomSeed

As I stated in my comment, I woudn't bother with the likelihood of collision. Just generate a random string and check if it exists. If it does, try again and you shouldn't need to do it more that a couple of times unless you have a huge number of plates already assigned.

正如我在评论中所说，我不会担心发生碰撞的可能性。只需生成一个随机字符串并检查它是否存在。如果是这样，请再试一次，除非您已经分配了大量的盘子，否则您不需要多次这样做。

Another solution for generating an 8-character long pseudo-random string in pure (My)SQL:

在纯 (My)SQL 中生成 8 字符长伪随机字符串的另一种解决方案：

SELECT LEFT(UUID(), 8);

You can try the following (pseudo-code):

您可以尝试以下操作（伪代码）：

DO 
    SELECT LEFT(UUID(), 8) INTO @plate;
    INSERT INTO plates (@plate);
WHILE there_is_a_unique_constraint_violation
-- @plate is your newly assigned plate number

---

---

Since this post has received a unexpected level of attention, let me highlight ADTC's comment: the above piece of code is quite dumb and produces sequential digits.

由于这篇文章受到了意想不到的关注，让我强调一下 ADTC 的评论：上面的一段代码非常愚蠢，并且会产生连续的数字。

For slightly less stupid randomness try something like this instead :

对于稍微不那么愚蠢的随机性，请尝试使用以下方法：

SELECT LEFT(MD5(RAND()), 8)

And for true (cryptograpically secure) randomness, use RANDOM_BYTES()rather than RAND()(but then I would consider moving this logic up to the application layer).

对于真正的（密码学上安全的）随机性，使用RANDOM_BYTES()而不是RAND()（但随后我会考虑将此逻辑移至应用程序层）。

回答by paul

What about calculating the MD5 (or other) hash of sequential integers, then taking the first 8 characters.

如何计算连续整数的 MD5（或其他）散列，然后取前 8 个字符。

i.e

IE

MD5(1) = c4ca4238a0b923820dcc509a6f75849b => c4ca4238
MD5(2) = c81e728d9d4c2f636f067f89cc14862c => c81e728d
MD5(3) = eccbc87e4b5ce2fe28308fd9f2a7baf3 => eccbc87e

etc.

等等。

caveat: I have no idea how many you could allocate before a collision (but it would be a known and constant value).

警告：我不知道在碰撞之前可以分配多少个（但它是一个已知且恒定的值）。

edit: This is now an old answer, but I saw it again with time on my hands, so, from observation...

编辑：这现在是一个旧答案，但我有时间再次看到它，所以，从观察...

Chance of all numbers = 2.35%

所有数字的机会 = 2.35%

Chance of all letters = 0.05%

所有字母的几率 = 0.05%

First collision when MD5(82945) = "7b763dcb..." (same result as MD5(25302))

当 MD5(82945) = "7b763dcb..." 时的第一次碰撞（结果与 MD5(25302) 相同）

回答by Paddy Mann

Create a random string

创建一个随机字符串

Here's a MySQL function to create a random string of a given length.

这是一个 MySQL 函数，用于创建给定长度的随机字符串。

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `RandString`(length SMALLINT(3)) RETURNS varchar(100) CHARSET utf8
begin
    SET @returnStr = '';
    SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    SET @i = 0;

    WHILE (@i < length) DO
        SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
        SET @i = @i + 1;
    END WHILE;

    RETURN @returnStr;
END

Usage SELECT RANDSTRING(8)to return an 8 character string.

用法SELECT RANDSTRING(8)返回一个8字符串。

You can customize the @allowedChars.

您可以自定义@allowedChars.

Uniqueness isn't guaranteed - as you'll see in the comments to other solutions, this just isn't possible. Instead you'll need to generate a string, check if it's already in use, and try again if it is.

不保证唯一性 - 正如您将在其他解决方案的评论中看到的，这是不可能的。相反，您需要生成一个字符串，检查它是否已被使用，如果是，则再试一次。

---

---

Check if the random string is already in use

检查随机字符串是否已被使用

If we want to keep the collision checking code out of the app, we can create a trigger:

如果我们想将碰撞检查代码保留在应用程序之外，我们可以创建一个触发器：

DELIMITER $$

CREATE TRIGGER Vehicle_beforeInsert
  BEFORE INSERT ON `Vehicle`
  FOR EACH ROW
  BEGIN
    SET @vehicleId = 1;
    WHILE (@vehicleId IS NOT NULL) DO 
      SET NEW.plate = RANDSTRING(8);
      SET @vehicleId = (SELECT id FROM `Vehicle` WHERE `plate` = NEW.plate);
    END WHILE;
  END;$$
DELIMITER ;

回答by beingalex

Here's another method for generating a random string:

这是生成随机字符串的另一种方法：

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 8) AS myrandomstring

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 8) AS myrandomstring

回答by Gordon Linoff

Here is one way, using alpha numerics as valid characters:

这是一种使用字母数字作为有效字符的方法：

select concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1)
             ) as LicensePlaceNumber;

Note there is no guarantee of uniqueness. You'll have to check for that separately.

请注意，不保证唯一性。你必须单独检查。

回答by Andrey Volk

You may use MySQL's rand()and char()function:

你可以使用 MySQL 的rand()和char()函数：

select concat( 
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97)
) as name;

回答by Paul Spiegel

You can generate a random alphanumeric string with:

您可以使用以下命令生成随机字母数字字符串：

lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0);

You can use it in a BEFORE INSERTtrigger and check for a duplicate in a while loop:

您可以在BEFORE INSERT触发器中使用它并在 while 循环中检查重复项：

CREATE TABLE `vehicles` (
    `plate` CHAR(8) NULL DEFAULT NULL,
    `data` VARCHAR(50) NOT NULL,
    UNIQUE INDEX `plate` (`plate`)
);

DELIMITER //
CREATE TRIGGER `vehicles_before_insert` BEFORE INSERT ON `vehicles`
FOR EACH ROW BEGIN

    declare str_len int default 8;
    declare ready int default 0;
    declare rnd_str text;
    while not ready do
        set rnd_str := lpad(conv(floor(rand()*pow(36,str_len)), 10, 36), str_len, 0);
        if not exists (select * from vehicles where plate = rnd_str) then
            set new.plate = rnd_str;
            set ready := 1;
        end if;
    end while;

END//
DELIMITER ;

Now just insert your data like

现在只需插入您的数据，例如

insert into vehicles(col1, col2) values ('value1', 'value2');

And the trigger will generate a value for the platecolumn.

触发器将为该plate列生成一个值。

(sqlfiddle demo)

( sqlfiddle 演示)

That works this way if the column allows NULLs. If you want it to be NOT NULL you would need to define a default value

如果列允许 NULL，则以这种方式工作。如果您希望它不是 NULL，则需要定义一个默认值

`plate` CHAR(8) NOT NULL DEFAULT 'default',

You can also use any other random string generating algorithm in the trigger if uppercase alphanumerics isn't what you want. But the trigger will take care of uniqueness.

如果大写字母数字不是您想要的，您还可以在触发器中使用任何其他随机字符串生成算法。但是触发器会处理唯一性。

回答by Nikita G.

For generate random string, you can use:

要生成随机字符串，您可以使用：

SUBSTRING(MD5(RAND()) FROM 1 FOR 8)

SUBSTRING(MD5(RAND()) FROM 1 FOR 8)

You recieve smth like that:

你收到这样的消息：

353E50CC

353E50CC

回答by Jan Uhlig

For a String consisting of 8 random numbers and upper- and lowercase letters, this is my solution:

对于由 8 个随机数和大小写字母组成的字符串，这是我的解决方案：

LPAD(LEFT(REPLACE(REPLACE(REPLACE(TO_BASE64(UNHEX(MD5(RAND()))), "/", ""), "+", ""), "=", ""), 8), 8, 0)

Explained from inside out:

从里到外解释：

1. RANDgenerates a random number between 0 and 1
2. MD5calculates the MD5 sum of (1), 32 characters from a-f and 0-9
3. UNHEXtranslates (2) into 16 bytes with values from 00 to FF
4. TO_BASE64encodes (3) as base64, 22 characters from a-z and A-Z and 0-9 plus "/" and "+", followed by two "="
5. the three REPLACEs remove the "/", "+" and "=" characters from (4)
6. LEFTtakes the first 8 characters from (5), change 8 to something else if you need more or less characters in your random string
7. LPADinserts zeroes at the beginning of (6) if it is less than 8 characters long; again, change 8 to something else if needed

1. RAND生成 0 到 1 之间的随机数
2. MD5计算 (1) 的 MD5 总和，32 个字符来自 af 和 0-9
3. UNHEX将 (2) 转换为 16 个字节，值从 00 到 FF
4. TO_BASE64将 (3) 编码为 base64，来自 az 和 AZ 以及 0-9 的 22 个字符加上“/”和“+”，后跟两个“=”
5. 这三个REPLACEs 删除了 (4) 中的“/”、“+”和“=”字符
6. LEFT从 (5) 中获取前 8 个字符，如果您的随机字符串中需要更多或更少的字符，请将 8 更改为其他字符
7. LPAD如果长度小于 8 个字符，则在 (6) 的开头插入零；再次，如果需要，将 8 更改为其他内容