How to generate unique id in MySQL?

Question

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!

UPDATE: uuid is long, I mean such id like: (P5Dc) an 11 alphanumeric char.

Answer 1

Is it not possible to just use an auto_increment in a database table?

Answer 2

I use UUID() to create a unique value.

example:

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

Answer 3

A programmatic way can 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

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

Answer 4

Use UUID function.

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.

Answer 5

Does it need to be random? Or can you maybe base the unique id off other information in the row you are inserting?

You could do something like first few letters of field 1, followed by a few letters from field 2 and then an integer.

So you would generate the first part of the ID in PHP (The first few letters, without the integer.)

You could then check the database for similar ids by executing:

SELECT UniqueID FROM someTable WHERE UniqueID LIKE 'generatedString%' ORDER BY UniqueID DESC;

Then you would grab the first row, grab the integer off the end, increment it by one, and append that integer to the end of your generated string and insert.

Answer 6

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

Answer 7

Below is just for reference of numeric unique random 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;

Answer 8

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