如何在MySQL中生成唯一id ?

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.

更新:uuuid很长，我的意思是:(P5Dc)一个11字母数字字符。

13 个解决方案

#1

A programmatic way can be to:

一种程序化的方法可以是:

• add a UNIQUE INDEX to the field
• 向字段添加唯一索引
• generate a random string in PHP
• 在PHP中生成一个随机字符串
• loop in PHP ( while( ! DO_THE_INSERT ) )
  • generate another string
  • 生成另一个字符串
• 在PHP中循环(while(!DO_THE_INSERT))))生成另一个字符串

Note:

注意:

• This can be dirty, but has the advantage to be DBMS-agnostic
• 这可能很脏，但具有与dba无关的优势
• 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
• 即使您选择使用DBMS特定的惟一ID生成器函数(uid等)，使用索引确保字段必须是惟一的也是一种最佳实践
• the loop is statistically not executed at all, it is entered only on insert failure
• 该循环在统计上根本没有执行，它只在插入失败时输入

#2

I use UUID() to create a unique value.

我使用UUID()创建一个惟一值。

example:

例子:

insert into Companies (CompanyID, CompanyName) Values(UUID(), "TestUUID");

#3

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.
• 我们取一个输入数，比如1942。
• Left pad it into a string: "0000001942"
• 左垫成一串:“0000001942”
• Put the last two digits onto the front: "4200000019"
• 把最后两个数字放在前面:“4200000019”
• Convert that into a number: 4,200,000,019
• 把它转换成一个数字: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"
• 将这个数字转换为一个基本的34字符串:“2oevc0b”
• Replace any zeros with 'y' and any ones with 'z': "2oevcyb"
• 用“y”替换任何零和任何带有“z”的词:“2oevcyb”
• Upshift: "2OEVCYB"
• 加速:“2 oevcyb”

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碰撞。现在您有了一个看上去随机的短键，可以使用它来查找长数据库标识符。

#4

How you generate the unique_ids is a useful question - but you seem to be making a counter productive assumption about when you 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在其中的orders表。这个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 (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.

这种方法保证了惟一性，并将处理负载从insert事务转移到批处理例程中，在批处理例程中不会影响用户。

#5

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中生成唯一值的过程的源。如果是库函数，它们应该保证您的值是唯一的。检查文档。hovewer，你应该一直使用这个函数。例如，如果您使用PHP函数生成惟一值，然后决定使用MySQL函数，则可以生成已经存在的值。在这种情况下，在列上放置唯一索引也是一个好主意。

#6

If you use MySQL with version higher than 5.7.4, you can use the newly added RANDOM_BYTES function:

如果使用的MySQL版本高于5.7.4，可以使用新添加的RANDOM_BYTES函数:

 SELECT TO_BASE64(RANDOM_BYTES(16));

This will result in a random string such as GgwEvafNLWQ3+ockEST00A==.

这将导致一个随机字符串，如GgwEvafNLWQ3+ockEST00A=。

#7

For uniqueness what I do is I take the Unix timestamp and append a random string to it and use that.

为了惟一性，我所做的是使用Unix时间戳并向其附加一个随机字符串并使用它。

#8

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位唯一字符每次。

#9

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)的代码添加char。//假设$radomchar包含char;

#10

You might also consider using crypt()* to generate a [nearly-guaranteed] unique ID inside your contraints.

您也可以考虑使用crypt()*在您的约束中生成[几乎保证]唯一的ID。

#11

 <?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";
   ?>

#12

crypt() as suggested and store salt in some configuration file, Start salt from 1 and if you find duplicate move to next value 2. You can use 2 chars, but that will give you enough combination for salt.

如建议的那样，crypt()将salt存储在某个配置文件中，从1开始使用salt，如果发现重复移动到下一个值2。你可以用2个字符，但是这将给你足够的盐的组合。

You can generate string from openssl_random_pseudo_bytes(8). So this should give random and short string (11 char) when run with crypt().

您可以从openssl_random_pseudo_bytes(8)生成字符串。因此，当使用crypt()运行时，应该会给出随机和短的字符串(11 char)。

Remove salt from result and there will be only 11 chars that should be enough random for 100+ millions if you change salt on every fail of random.

从结果中除去盐，如果你在每一次随机的失败中改变盐，那么将会有11个碳足够1亿多亿人随机使用。

#13

This generates random ids:

这个生成随机id:

CREATE TABLE Persons (
    ID Integer PRIMARY KEY AUTOINCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);