产生随机&使用MySQL的唯一8字符串 [英] Generating a random & unique 8 character string using MySQL

问题描述

我正在开发一款涉及车辆的游戏.我有一个名为车辆"的MySQL表，其中包含有关车辆的数据，其中包括存储车辆牌照的牌照"列.

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.

现在是我遇到问题的部分.在创建新车辆之前，我需要找到一个未使用的车牌-它应该是8个字母数字的随机字符串.我是通过使用Lua(我正在使用的语言)中的while循环生成字符串并查询数据库以查看是否使用它的，从而实现了这一目标.但是，随着车辆数量的增加，我希望这会变得目前效率更低.因此，我决定尝试使用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个字符的字母数字字符串，该字符串尚未在表中.我再次想到了generate& check循环方法，但是我并没有将这个问题限制为以防万一，这是一种更有效率的方法.我已经能够通过定义一个包含所有允许的字符的字符串并随机将其子串化来生成字符串，仅此而已.

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.

感谢您的帮助.

推荐答案

此问题包含两个非常不同的子问题:

This problem consists of two very different sub-problems:

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

虽然很容易实现随机性，但没有重试循环的唯一性却不容易.这使我们首先专注于独特性.使用AUTO_INCREMENT可以轻松实现非随机唯一性.因此，使用保留唯一性的伪随机转换就可以了:

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:

• @paul建议使用哈希
• AES加密也适用
• 但是有一个不错的选择:RAND(N)本身！

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

由同一种子创建的随机数序列保证为

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

• 可复制
• 与前8个迭代不同
• 如果种子是INT32

因此，我们使用@AndreyVolk或@GordonLinoff的方法，但是使用种子 RAND:

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

例如假设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;

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