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

我正在做一个游戏，游戏中有时会涉及到车辆。我有一个名为“ vehicle”的 MySQL 表，其中包含有关车辆的数据，包括存储车辆牌照的列“ plate”。

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

我需要的查询应该只是生成一个8个字符的字母数字字符串，这个字符串还没有在表中。我又想到了生成和检查循环的方法，但是我并没有把这个问题限制在那里，以防有更有效的方法。我已经能够通过定义一个包含所有允许的字符的字符串和随机子字符串来生成字符串，仅此而已。

感谢你的帮助。

331819 次浏览
小开

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

也就是说

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

等等。

警告: 我不知道在冲突发生之前可以分配多少(但它将是一个已知的常量值)。

编辑: 这现在是一个古老的答案，但我在手边的时间里又看到了它，所以，根据观察..。

所有数字的概率 = 2.35%

所有字母的概率 = 0.05%

MD5(82945) = “7b763dcb...”(与 MD5(25302)相同的结果)时的第一次碰撞

小开

这里有一种方法，使用字母数字作为有效的字符:

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;

请注意，不能保证唯一性，您必须分别检查这一点。

小开

你可以使用 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;
小开

考虑到您所需要的字符总数，生成两个完全相似的号码牌的可能性非常小。因此，您可以在 LUA 中生成数字。

你有36 ^ 8个不同的独一无二的号码牌(2,821,109,907,456，这是很多) ，即使你已经有了一百万个号码牌，你也只有很小的几率生成一个你已经有的号码牌，大约0.000035%

当然，这完全取决于您最终将创建多少个号码牌。

小开

我不会为碰撞的可能性而烦恼。只要生成一个随机字符串并检查它是否存在。如果是这样，再试一次，除非你已经分配了大量的盘子，否则你不需要多做几次。

用纯(My) SQL 生成8个字符长的伪随机字符串的另一种解决方案:

SELECT LEFT(UUID(), 8);

您可以尝试以下(伪代码) :

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

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

对于稍微不那么愚蠢的随机性，可以试试这样的方法:

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

对于真正的(加密安全的)随机性，使用 RANDOM_BYTES()而不是 RAND()(但之后我会考虑将这种逻辑上移到应用程序层)。

小开

如果你同意“随机”，但完全可以预测的车牌，你可以使用 线性反馈移位寄存器选择下一个车牌号码-它保证通过每个数字之前重复。然而，如果没有一些复杂的数学运算，您将无法遍历每个8个字符的字母数字字符串(您将得到36 ^ 8(78%)可能板块中的2 ^ 41)。为了更好地填充你的空间，你可以从盘子里排除一个字母(可能是 O) ，这样你就得到了97% 。

小开
最佳答案

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

  • 这条线看起来一定是随机的
  • 字符串必须是唯一的

虽然随机性很容易实现，但是没有重试循环的唯一性却不容易实现。这使我们首先集中于独特性。使用 AUTO_INCREMENT可以很容易地实现非随机唯一性。因此，使用保持唯一性的伪随机变换是可行的:

  • @ paul 建议使用 Hash
  • AES-加密也适用
  • 但有一个很好的: RAND(N)本身！

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

  • 可重现性
  • 在前8次迭代中有所不同
  • 如果种子是 INT32

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

例如，Assumin 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;
小开

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

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

您可以在 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 ;

现在只要像这样插入数据

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

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

(Sqlfiddle 演示)

如果列允许 NULL，那么这种方法就是这样工作的。如果您希望它为 NOT NULL，则需要定义一个默认值

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

如果不需要大写字母数字，还可以在触发器中使用任何其他随机字符串生成算法。但是触发器会解决独特性的问题。

小开

我使用来自另一列的数据来生成“ hash”或唯一字符串

UPDATE table_name SET column_name = Right( MD5(another_column_with_data), 8 )
小开

字母表中的8个字母-全部大写:

UPDATE `tablename` SET `tablename`.`randomstring`= concat(CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25)))CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))));
小开

创建一个随机字符串

这里有一个 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

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

您可以自定义 @allowedChars。

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

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

如果我们不想让碰撞检查代码出现在应用程序中，我们可以创建一个触发器:

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 ;
小开

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

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

由内而外的解释:

  1. RAND生成一个介于0和1之间的随机数
  2. MD5计算(1)、 a-f 和0-9中的32个字符的 MD5和
  3. UNHEX将(2)转换为16字节，值从00到 FF
  4. TO_BASE64将(3)编码为 base64,22个字符来自 A-Z 和 A-Z，0-9加上“/”和“ +”，后面跟着两个“ =”
  5. 这三个 REPLACE将删除(4)中的“/”、“ +”和“ =”字符
  6. LEFT取(5)中的前8个字符，如果需要随机字符串中的多个或少个字符，则将8更改为其他字符
  7. 如果(6)的长度小于8个字符，则 LPAD在(6)的开头插入0; 同样，如果需要，将8更改为其他值
小开

如果您没有 id 或种子，比如它的 for 值列表在 insert 中:

REPLACE(RAND(), '.', '')
小开

下面是另一种生成随机字符串的方法:

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

小开 
DELIMITER $$


USE `temp` $$


DROP PROCEDURE IF EXISTS `GenerateUniqueValue`$$


CREATE PROCEDURE `GenerateUniqueValue`(IN tableName VARCHAR(255),IN columnName VARCHAR(255))
BEGIN
DECLARE uniqueValue VARCHAR(8) DEFAULT "";
WHILE LENGTH(uniqueValue) = 0 DO
SELECT CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1)
) INTO @newUniqueValue;
SET @rcount = -1;
SET @query=CONCAT('SELECT COUNT(*) INTO @rcount FROM  ',tableName,' WHERE ',columnName,'  like ''',@newUniqueValue,'''');
PREPARE stmt FROM  @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @rcount = 0 THEN
SET uniqueValue = @newUniqueValue ;
END IF ;
END WHILE ;
SELECT uniqueValue;
END$$


DELIMITER ;

使用此存储过程，并在以下情况下每次使用该存储过程

Call GenerateUniqueValue('tableName','columnName')
小开

为了生成随机字符串，可以使用:

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

你得到的东西是这样的:

353E50CC

小开

生成唯一数字的简单方法

set @i = 0;
update vehicles set plate = CONCAT(@i:=@i+1, ROUND(RAND() * 1000))
order by rand();
小开

该函数根据输入长度和允许的字符生成一个随机字符串，如下所示:

SELECT str_rand(8, '23456789abcdefghijkmnpqrstuvwxyz');

功能代码:

DROP FUNCTION IF EXISTS str_rand;


DELIMITER //


CREATE FUNCTION str_rand(
u_count INT UNSIGNED,
v_chars TEXT
)
RETURNS TEXT
NOT DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
DECLARE v_retval TEXT DEFAULT '';
DECLARE u_pos    INT UNSIGNED;
DECLARE u        INT UNSIGNED;


SET u = LENGTH(v_chars);
WHILE u_count > 0
DO
SET u_pos = 1 + FLOOR(RAND() * u);
SET v_retval = CONCAT(v_retval, MID(v_chars, u_pos, 1));
SET u_count = u_count - 1;
END WHILE;


RETURN v_retval;
END;
//
DELIMITER ;

这个代码是基于 洗牌字符串函数洗牌字符串函数发送的“罗斯史密斯 II”

小开

生成8个字符键

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

如何为 MySql 表列之一生成唯一的随机字符串？

小开

我正在寻找一些类似的东西，我决定做我自己的版本，你也可以指定一个不同的种子，如果需要(字符列表)作为参数:

CREATE FUNCTION `random_string`(length SMALLINT(3), seed VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
NO SQL
BEGIN
SET @output = '';


IF seed IS NULL OR seed = '' THEN SET seed = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; END IF;


SET @rnd_multiplier = LENGTH(seed);


WHILE LENGTH(@output) < length DO
# Select random character and add to output
SET @output = CONCAT(@output, SUBSTRING(seed, RAND() * (@rnd_multiplier + 1), 1));
END WHILE;


RETURN @output;
END

可用作:

SELECT random_string(10, '')

它将使用大小写字母 + 数字的内置种子。 NULL 也将是值而不是”。

但是可以在调用时指定一个定制种子:

SELECT random_string(10, '1234')
小开

创建一个随机的 10位字母数字，不包括相似的字符01oOlI:

LPAD(LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TO_BASE64(UNHEX(MD5(RAND()))), "/", ""), "+", ""), "=", ""), "O", ""), "l", ""), "I", ""), "1", ""), "0", ""), "o", ""), 10), 10, 0)

这正是我创建 凭证代码凭证代码所需要的。将混淆字符删除，以减少在将其输入到凭单代码表单时出现的错误。

希望这对某些人有帮助，基于 Jan Uhlig 的绝妙回答。

请参阅 Jan 关于此代码如何工作的详细说明。

小开

简单而有效的解决方案获得一个随机的10个字符串与大写和小写字母和数字:

select substring(base64_encode(md5(rand())) from 1+rand()*4 for 10);
小开

SQL 触发器复杂且资源密集，针对基于 MySQL“触发器”的解决方案，这里有一个更简单的解决方案。

  1. 在 MySQL 表列上创建一个 UNIQUE INDEX，它将保存车辆登记牌照字符串。这将确保只有唯一的值进入。
  2. 只需用 Lua (或任何其他编程语言，如 ASP、 PHP、 Java 等)生成标准的字母数字随机字符串
  3. 使用生成的字符串执行 INSERT 语句，并使用错误捕捉代码解析失败(在 UNIQUE INDEX 冲突的情况下)
  4. 如果 INSERT 失败，则生成一个新的随机字符串并重新插入。8个字符的长度本身是很难重复的，一旦发现在表中生成另一个字符，几乎不可能再重复。

这在 DB 服务器上将更轻便、更有效。

下面是 PHP 中的一个示例(伪)代码:

function refercode()
{
$string = '';
$characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$max = strlen($characters) - 1;
for ($i = 0; $i < 8; $i++) {
$string .= $characters[mt_rand(0, $max)];
}
$refer = "select * from vehicles where refer_code = '".$string."' ";
$coderefertest = mysqli_query($con,$refer);


if(mysqli_num_rows($coderefertest)>0)
{
return refercode();
}
else
{
return $string;
}
}
$refer_by = refercode();
小开 
UPPER(HEX(UUID_SHORT()))

给你一个16个字符的字母数字字符串，它是唯一的。它有一些不太可能的警告，参见 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short

“下一个”值通常是可以预测的:

mysql> SELECT UPPER(HEX(UUID_SHORT()));
+--------------------------+
| UPPER(HEX(UUID_SHORT())) |
+--------------------------+
| 161AA3FA5000006          |
+--------------------------+


mysql> SELECT UPPER(HEX(UUID_SHORT()));
+--------------------------+
| UPPER(HEX(UUID_SHORT())) |
+--------------------------+
| 161AA3FA5000007          |
+--------------------------+

转换为 BASE64可以将字符串减少到11个字符:

Https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_to-base64

mysql> SELECT TO_BASE64(UNHEX(HEX(UUID_SHORT())));
+-------------------------------------+
| TO_BASE64(UNHEX(HEX(UUID_SHORT()))) |
+-------------------------------------+
| AWGqP6UAABA=                        |
+-------------------------------------+

这是12个字符，去掉’=’得到11。

这些可能使它不适合您的使用: “下一个”板是有点可预测的。字符串中可能有一些句读(+，/)。小写字母可能包括在内。

小开

这项工作形式我，生成6位数字的数字和更新在 MySQL:

产生:

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6)

更新:

UPDATE table_name
SET column_name = SUBSTRING(MD5(RAND()) FROM 1 FOR 6)
WHERE id = x12

提交答案