[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

Good way of generating guaranteed unique ids?


I'm using mysql 5 and php 5. I have a table which will contain max ~100,000 rows. Each row represents some widget a 3rd party can add to the table. So every time a 3rd party adds a row, I want to generate a unique ID for the row which I can hand back to the 3rd party. Something like:

  primary_key | public_id | text
      0                  abc            hello world!
      1                  def             today is monday

what's a good way of generating the public_id value? I need it to be unique. What do people generally use for this? Timestamps?

I thought sqlserver (microsoft?) had some type called "guid" which is supposed to be a guaranteed unique id. I'd ideally like something like that. It can be a string.


3 Solutions
Cornelia YoderArtistCommented:
There are two ways.

1.  Use an auto-increment field for your id.  This will give sequential numbers, so should not be used if a user guessing an id would be a security risk.

2.  Use a query loop to generate a random number, query to see if it is already in the table, and then if it is, generate a different random number.  This has the drawback of potentially generating the same number for two people at the same time, so make your number id field big enough to make the chances of that miniscule.

Then make the id field into a Key, so that it is impossible to insert two identical ones.

If you want to use something other than an integer, a string with letters and numbers for example, you will need to write your own function to randomly generate a string, and then use method 2 above to ensure it is unique.
Cornelia YoderArtistCommented:
Here is a function to generate random strings, with several controls to determine structure of the desired string.


function generate_random_string($nmin,$nmax,$first,$upper,$lower,$numbers,$specials)
      //$nmin = minimum number of characters in string
      //$nmax = maximum number of characters in string
      //$first = 0 for any first character, 1 for alphabetic first character, 2 for numeric first character, 3 for alphanumeric first character
      //$upper = 1 if uppercase letters are to be included, 0 if not
      //$lower = 1 if lowercase letters are to be included, 0 if not
      //$numbers = 1 if numbers are to be included, 0 if not
      //$specials = 1 if special characters are to be included, 0 if not

      //check inputs
            (!is_int($nmin) || !is_int($nmax) || $nmax<$nmin || $nmax<0 || $nmin<0 ||
             !is_int($first) || $first>3 || $first<0 ||
             !is_int($upper) || $upper>1 || $upper<0 ||
             !is_int($lower) || $lower>1 || $lower<0 ||
             !is_int($numbers) || $numbers>1 || $numbers<0 ||
             !is_int($specials) || $specials>1 || $specials<0
            echo "error in inputs<BR>";
            return FALSE;

      //define the base arrays

      //define the specific arrays to be used      
      if ($upper == 1)
      if ($lower == 1)
      if ($numbers == 1)
      if ($specials == 1)

      //create first character of the string
      switch ($first)
      case 0:
            $string = $fullarray[rand(0,count($fullarray)-1)];
      case 1:
            $string = $alphabeticarray[rand(0,count($alphabeticarray)-1)];
      case 2:
            $string = $digitsarray[rand(0,count($digitsarray)-1)];
      case 3:
            $string = $alphanumericarray[rand(0,count($digitsarray)-1)];

      //create the remainder of the string
      $stringlength = rand($nmin,$nmax);
      for ($i=1;$i<$stringlength;$i++)
            $string = $string . $fullarray[rand(0,count($fullarray)-1)];
return $string;

//test the function
$string = generate_random_string(3,7,0,1,1,1,0);
echo "$string<br>";

Cornelia YoderArtistCommented:
Here is a query loop to ensure the generated id is not already in the table:

        $result=mysql_query("Select * from MyTable where id=$id;",$conn);

If you wish to use the random string function, then replace rand() with it in the above.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

UUID is a function in mySql that returns a GUID, if you wish to go that route.

DJ_AM_JuiceboxAuthor Commented:
Hi yodercm,

That all makes sense, but what about the UUID() function available in mysql? Does  it generate guaranteed unique IDs, or just strings that appear to look random?

1.  Use an auto-increment field for your id.
2.  Use a Method to generate a random number, and query to check  it is already in the table or not.

method is-(check this)
using System.Security.Cryptography;

  #region get uniquekey
    public string getUniqueKey()
        DataSet ds = new DataSet();
        int count;
        string strIdCode;
            strIdCode = getRandomKey(2);
            idcode = strIdCode;
            ds = check_idcode(strIdCode);
            count = ds.Tables[0].Rows.Count;
        } while (count > 0);
        return strIdCode;


    #region get rendomkey

    public string getRandomKey(int bytelength)
        byte[] buff = new byte[3];
        RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
        StringBuilder sb = new StringBuilder(bytelength * 2);
        for (int i = 0; i < buff.Length; i++)
            sb.Append(string.Format("{0:X2}", buff[i]));
        return sb.ToString();

    #region check idcode
    public DataSet check_idcode(string code)

        Constents conDB = new Constents();

        int success = 0;
            conDB.SqlCmd.CommandText = "select id from tablename";
            DataSet ds = new DataSet();
            success = conDB.SqlCmd.ExecuteNonQuery();
            return ds;
        catch (SqlException SqlExp)
SharathData EngineerCommented:
>> hat about the UUID() function available in mysql? Does  it generate guaranteed unique IDs, or just strings that appear to look random?

refer the URL.

Cornelia YoderArtistCommented:
The UUID function is designed to generate unique strings, but even with it, there is a very low probability of generating a non-unique value.

The function and loop that I gave you will do about the same, and the longer you make the random string generation, the less chance of duplication.

If you are very concerned about this low chance of non-uniqueness, make the field a Key.
The idea of a unique identifier is not necessary to have all the IDs in a database table at once. For example, Microsoft uses GUID values in the registry for Class IDs. These IDs are being creating by programmers all over the world--with no central repository. Thus, the uniqueness of the GUID cannot be tested (a clash may go undetected because no single registry database may ever contain two GUIDs that happen to be the same because the owner of that computer will never install the twp software products that happen to have the same GUID).

In database, it is possible that a table can be archived or taken offline, but when restored, the IDs must still be unique if they are assigned to a key field or the restore cannot be done successfully. In theory, using a GUID or an always-incrementing numeric value will assure uniqueness.

In this application, if the database itself is always going to run on a single server and will itself generate the unique ID, then it seems overkill to use a GUID-type value. If the table has an auto-increment key field that is of type INT or higher (as needed), then millions or billions (depending on the data type, of course) of unique records can be created without fear of a clash.


Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now