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

2021-11-20 00:00:00 sql 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) 本身！

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

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

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

所以我们使用@AndreyVolk 或 @GordonLinoff 的方法，但使用 seed RAND:

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

例如假设 id 是一个 AUTO_INCREMENT 列:

e.g. Assumin id is an AUTO_INCREMENT column: 

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;

相关文章