php 如何在 MySQL 中生成唯一 ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1467581/
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
How to generate unique id in MySQL?
提问by assaqqaf
I'm programming a script using PHP and MySQL and I want to get a
unique id (consisting of a string: capitals and small
letters with numbers) like: gHYtUUi5b.
I found many functions in PHP that can generate such numbers but I'm afraid about how to ensure the id is unique!
我正在使用 PHP 和 MySQL 编写一个脚本，我想获得一个唯一的 id（由一个字符串组成：大写字母和带数字的小写字母组成），例如：gHYtUUi5b. 我发现 PHP 中有很多函数可以生成这样的数字，但我担心如何确保 id 是唯一的！
UPDATE: uuid is long, I mean such id like: (P5Dc) an 11 alphanumeric char.
更新：uuid 很长，我的意思是这样的 id：(P5Dc) 一个 11 个字母数字字符。
采纳答案by drAlberT
A programmatic waycan be to:
一种编程方式可以是：
- add a UNIQUE INDEX to the field
- generate a random string in PHP
- loop in PHP ( while( ! DO_THE_INSERT ) )
- generate another string
- 向字段添加唯一索引
- PHP生成随机字符串
- PHP中的循环（while（！DO_THE_INSERT））
- 生成另一个字符串
Note:
笔记：
- This can be dirty, but has the advantage to be DBMS-agnostic
- Even if you choose to use a DBMS specific unique ID generator function (UUID, etc) it is a best practice to assure the field HAS to be UNIQUE, using the index
- the loop is statistically not executed at all, it is entered only on insert failure
- 这可能很脏，但具有与DBMS 无关的优势
- 即使您选择使用 DBMS 特定的唯一 ID 生成器函数（UUID 等），最好的做法是使用索引确保字段必须是 UNIQUE
- 循环在统计上根本不执行，仅在插入失败时进入
回答by CSharpAtl
I use UUID() to create a unique value.
我使用 UUID() 创建一个唯一值。
example:
例子：
insert into Companies (CompanyID, CompanyName) Values(UUID(), "TestUUID");
回答by RJStanford
You may like the way that we do it. I wanted a reversible unique code that looked "random" -a fairly common problem.
您可能会喜欢我们的做法。我想要一个看起来“随机”的可逆唯一代码 - 一个相当普遍的问题。
- We take an input number such as 1,942.
- Left pad it into a string: "0000001942"
- Put the last two digits onto the front: "4200000019"
- Convert that into a number: 4,200,000,019
- 我们采用一个输入数字，例如 1,942。
- 将其左填充为字符串：“0000001942”
- 将最后两位数字放在前面：“4200000019”
- 将其转换为数字：4,200,000,019
We now have a number that varies wildly between calls and is guaranteed to be less than 10,000,000,000. Not a bad start.
我们现在有一个在调用之间变化很大的数字，并且保证小于 10,000,000,000。不是一个糟糕的开始。
- Convert that number to a Base 34 string: "2oevc0b"
- Replace any zeros with 'y' and any ones with 'z': "2oevcyb"
- Upshift: "2OEVCYB"
- 将该数字转换为 Base 34 字符串：“2oevc0b”
- 用“y”替换任何零，用“z”替换任何零：“2oevcyb”
- 升档：“2OEVCYB”
The reason for choosing base 34 is so that we don't worry about 0/O and 1/l collisions. Now you have a short random-looking key that you can use to look up a LONG database identifier.
选择基数 34 的原因是为了让我们不用担心 0/O 和 1/l 冲突。现在您有一个简短的随机键，可用于查找 LONG 数据库标识符。
回答by Rob Beer
How you generate the unique_ids is a useful question - but you seem to be making a counter productive assumption about whenyou generate them!
您如何生成 unique_ids 是一个有用的问题 - 但您似乎对何时生成它们做出了适得其反的假设！
My point is that you do not need to generate these unique id's at the time of creating your rows, because they are essentially independent of the data being inserted.
我的观点是，您不需要在创建行时生成这些唯一 ID，因为它们本质上与插入的数据无关。
What I do is pre-generate unique id's for future use, that way I can take my own sweet time and absolutely guarantee they are unique, and there's no processing to be done at the time of the insert.
我所做的是预先生成唯一的 id 以备将来使用，这样我就可以享受自己的甜蜜时光并绝对保证它们是唯一的，并且在插入时无需进行任何处理。
For example I have an orders table with order_id in it. This id is generated on the fly when the user enters the order, incrementally 1,2,3 etc forever. The user does not need to see this internal id.
例如，我有一个带有 order_id 的订单表。这个 id 是在用户输入订单时即时生成的，永远递增 1,2,3 等。用户不需要看到这个内部 ID。
Then I have another table - unique_ids with (order_id, unique_id). I have a routine that runs every night which pre-loads this table with enough unique_id rows to more than cover the orders that might be inserted in the next 24 hours. (If I ever get 10000 orders in one day I'll have a problem - but that would be a good problem to have!)
然后我有另一个表 - unique_ids with (order_id, unique_id)。我有一个每天晚上运行的例程，它使用足够的 unique_id 行预加载此表，以涵盖可能在接下来的 24 小时内插入的订单。（如果我在一天内收到 10000 个订单，我就会遇到问题——但这将是一个很好的问题！）
This approach guarantees uniqueness and takes any processing load away from the insert transaction and into the batch routine, where it does not affect the user.
这种方法保证了唯一性，并将任何处理负载从插入事务转移到批处理例程中，在那里它不会影响用户。
回答by zah
If you use MySQL with version higher than 5.7.4, you can use the newly added RANDOM_BYTESfunction:
如果使用5.7.4以上版本的MySQL，可以使用新增的RANDOM_BYTES函数：
SELECT TO_BASE64(RANDOM_BYTES(16));
This will result in a random string such as GgwEvafNLWQ3+ockEST00A==.
这将产生一个随机字符串，例如GgwEvafNLWQ3+ockEST00A==.
回答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 "";
DECLARE newUniqueValue 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 ;
And call the stored procedure as GenerateUniqueValue('tableName','columnName'). This will give you a 8 digit unique character everytime.
并将存储过程调用为 GenerateUniqueValue('tableName','columnName')。这每次都会为您提供一个 8 位的唯一字符。
回答by Lukasz Lysik
Use UUID function.
使用UUID 函数。
I don't know the source of your procedures in PHP that generates unique values. If it is library function they should guarantee that your value is really unique. Check in documentation. You should, hovewer, use this function all the time. If you, for example, use PHP function to generate unique value, and then you decide to use MySQL function, you can generate value that already exist. In this case putting UNIQUE INDEX on the column is also a good idea.
我不知道您在 PHP 中生成唯一值的程序的来源。如果是库函数，他们应该保证你的值真的是独一无二的。签入文档。但是，您应该一直使用此功能。例如，如果您使用 PHP 函数生成唯一值，然后您决定使用 MySQL 函数，则可以生成已存在的值。在这种情况下，将 UNIQUE INDEX 放在列上也是一个好主意。
回答by Sabeen Malik
For uniqueness what I do is I take the Unix timestamp and append a random string to it and use that.
为了唯一性，我所做的是获取 Unix 时间戳并在其中附加一个随机字符串并使用它。
回答by Aravinth
Below is just for reference of numeric unique random id...
以下仅供参考数字唯一随机ID...
it may help you...
它可能会帮助你...
$query=mysql_query("select * from collectors_repair");
$row=mysql_num_rows($query);
$ind=0;
if($row>0)
{
while($rowids=mysql_fetch_array($query))
{
$already_exists[$ind]=$rowids['collector_repair_reportid'];
}
}
else
{
$already_exists[0]="nothing";
}
$break='false';
while($break=='false'){
$rand=mt_rand(10000,999999);
if(array_search($rand,$alredy_exists)===false){
$break='stop';
}else{
}
}
echo "random number is : ".$echo;
and you can add char with the code like -> $rand=mt_rand(10000,999999) .$randomchar; // assume $radomchar contains char;
您可以使用以下代码添加字符 -> $rand=mt_rand(10000,999999) .$randomchar; // assume $radomchar contains char;
回答by imran khan
<?php
$hostname_conn = "localhost";
$database_conn = "user_id";
$username_conn = "root";
$password_conn = "";
$conn = mysql_pconnect($hostname_conn, $username_conn, $password_conn) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_conn,$conn);
// run an endless loop
while(1) {
$randomNumber = rand(1, 999999);// generate unique random number
$query = "SELECT * FROM tbl_rand WHERE the_number='".mysql_real_escape_string ($randomNumber)."'"; // check if it exists in database
$res =mysql_query($query,$conn);
$rowCount = mysql_num_rows($res);
// if not found in the db (it is unique), then insert the unique number into data_base and break out of the loop
if($rowCount < 1) {
$con = mysql_connect ("localhost","root");
mysql_select_db("user_id", $con);
$sql = "insert into tbl_rand(the_number) values('".$randomNumber."')";
mysql_query ($sql,$con);
mysql_close ($con);
break;
}
}
echo "inserted unique number into Data_base. use it as ID";
?>
