PHP/MySQL - 创建唯一随机字符串的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7893257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP/MySQL - Best way to create unique random string?
提问by John
How do I create a random unique string in MySQL?
如何在 MySQL 中创建随机唯一字符串？
when I need to create a random string in PHP I use this function:
当我需要在 PHP 中创建一个随机字符串时，我使用这个函数：
public function generateString($length)
{
$charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
for($i=0; $i<$length; $i++)
$key .= $charset[(mt_rand(0,(strlen($charset)-1)))];
return $key;
}
Then I would take the generated string and store it in a MySQL database.
然后我将生成的字符串存储在 MySQL 数据库中。
What is the best way to make sure the generated random string is unique to all the other random strings created for other entries in the database?
确保生成的随机字符串对于为数据库中的其他条目创建的所有其他随机字符串是唯一的最佳方法是什么？
Maybe something like this?
也许是这样的？
while(++$i < 100)
{
//query db with random key to see if there is a match
//if no match found break out of loop
break;
}
This seems messy and long, and I could potentially hit the database multiple times. How can I quickly be sure my new random string is unique?
这看起来又乱又长，我可能会多次访问数据库。如何快速确定我的新随机字符串是唯一的？
采纳答案by Salman A
Assuming 10 characters from the character set a-z, A-Z, 0-9mean there are (26 + 26 + 10)10= 8.39299366 × 1017possible combinations. To calculate the odds of a collision... just 1/x the afore-mentioned number. So I would not be worrying about getting the same string twice. Even if do get the same string again I'll just run the function again in a loop, the only exit condition being that a unique string is found.
假设字符集中的 10 个字符a-z, A-Z, 0-9意味着有 (26 + 26 + 10) 10= 8.39299366 × 10 17 种可能的组合。要计算碰撞的几率……只是上述数字的 1/x。所以我不会担心两次获得相同的字符串。即使再次获得相同的字符串，我也会在循环中再次运行该函数，唯一的退出条件是找到了唯一的字符串。
回答by Mark Biek
回答by rudyrockstar
SET rand_str = SUBSTRING(MD5(NOW()),1,$LENGTH);-- Where LENGTH is 1 to 32 as per MD5
SET rand_str = SUBSTRING(MD5(NOW()),1,$LENGTH);-- 根据 MD5，LENGTH 为 1 到 32
Some examples are below:
一些例子如下：
SET rand_str = SUBSTRING(MD5(NOW()),1,5);-- 5 character string
SET rand_str = SUBSTRING(MD5(NOW()),1,5);-- 5个字符串
SET rand_str = SUBSTRING(MD5(NOW()),1,15);-- 15 character string
SET rand_str = SUBSTRING(MD5(NOW()),1,15);-- 15个字符串
回答by Luca C.
I usually use:
我通常使用：
SELECT LEFT(MD5(id), 8)
variants by needings:
按需求变化：
SELECT LEFT(UUID(), 8)
SELECT LEFT(MD5(RAND()), 8)
回答by Simon
If you want to use these strings for security purpose, you should use openssl_random_pseudo_byteswhich will indicate you if PHP was able to use a strong algorithm to generate it:
如果您想将这些字符串用于安全目的，您应该使用 openssl_random_pseudo_bytes这将指示您 PHP 是否能够使用强大的算法来生成它：
Ouput needs some cleaning though. Have a look at this questionfor more info.
输出需要一些清洁。看看这个问题了解更多信息。
回答by HARDIK
Unique random strings can be used as character keys or tokens to identify database records and check to database table and provides Unique key with store $refer_by variable.
唯一随机字符串可以用作字符键或令牌来识别数据库记录和检查数据库表，并通过存储 $refer_by 变量提供唯一键。
define('DB_SERVER', "localhost");
define('DB_USER', "root");
define('DB_PASS', "");
define('DB_DATABASE', "student");
$con = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
function refercode()
{
$string = '';
$characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
$max = strlen($characters) - 1;
for ($i = 0; $i < 6; $i++) {
$string .= $characters[mt_rand(0, $max)];
}
$refer = "select * from user_detail where refer_code = '".$string."' ";
$coderefertest = mysqli_query($con,$refer);
if(mysqli_num_rows($coderefertest)>0)
{
return refercode();
}
else
{
return $string;
}
}
$refer_by = refercode();
回答by Hariramprasath Nandhagopalan
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 ;
Use this stored procedure and call this stored procedure as
使用此存储过程并将此存储过程称为
Call GenerateUniqueValue('tableName','columnName')
回答by RJStanford
Luckily databases already have the ability to create unique IDs (numeric) - I suggest the approach that we took, which is to create a two-way conversion between a gently increasing numeric ID and an alpha-numeric ID. Having it be two-way assures that the alpha-numeric "random" versions are also unique without having to explicitly test them. Indeed, I only ever store the numeric version in the database (since you get it for free with a SERIAL column) and only ever print the alpha version.
幸运的是，数据库已经具备创建唯一 ID（数字）的能力——我建议我们采用的方法，即在逐渐增加的数字 ID 和字母数字 ID 之间创建双向转换。将其设为双向可确保字母数字“随机”版本也是唯一的，而无需明确测试它们。实际上，我只在数据库中存储数字版本（因为您可以通过 SERIAL 列免费获得它）并且只打印 alpha 版本。
This example generates seven-byte IDs but the approach can be trivially tweaked to fit almost any set of circumstances.
此示例生成 7 字节 ID，但可以对该方法进行微调以适应几乎所有情况。
回答by Tim Trampedach
I would make the column of this id unique in your DB. Then you can do something like this to safeguard against collisions:
我会让这个 id 的列在你的数据库中是唯一的。然后你可以做这样的事情来防止碰撞：
$row_count = 0;
while ($row_count == 0) {
error_reporting(0);
$id_string = substr(uniqid(), 0, 10);
$sql = "UPDATE <table> SET unique_id = :unique_id WHERE <something true>";
$query = $this->db->prepare($sql);
$query->execute(array(':unique_id' => $unique_id));
$row_count = $query->rowCount();
}
Sure, it may need to try the query more than once, but this way you know it's guaranteed to be unique in your DB. The error_reporting(0) line is in there to suppress any warnings which might be turned on. PHP's uniqid() also isn't the most unique generate there is, but you can easily swap that out for your own or just take the hit of potential collisions here and there.
当然，它可能需要多次尝试查询，但这样你就知道它在你的数据库中是唯一的。error_reporting(0) 行在那里抑制可能打开的任何警告。PHP 的 uniqid() 也不是最独特的生成器，但您可以轻松地将其替换为您自己的，或者只是在这里和那里承受潜在冲突的影响。
回答by gview
Take a look at the uniqidfunction and the pecl uuid extension. Either one can be used as the basis for generating guids, although if you plan to have a cluster, you will want to insure that you have something extra that insures that two servers don't generate the same id. Having a per server configuration that adds as prefix or suffix to the id is sufficient to address that issue.
看看uniqid函数和pecl uuid 扩展。任何一个都可以用作生成 guid 的基础，尽管如果您计划拥有一个集群，您将需要确保您有一些额外的东西来确保两个服务器不会生成相同的 id。将每个服务器配置添加为 id 的前缀或后缀足以解决该问题。
