Good way of generating guaranteed unique ids?

Posted on 2010-01-03
Last Modified: 2012-05-08

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.


Question by:DJ_AM_Juicebox
    LVL 27

    Expert Comment

    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.
    LVL 27

    Accepted Solution

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

    LVL 27

    Expert Comment

    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.
    LVL 8

    Assisted Solution

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


    Author Comment

    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?

    LVL 4

    Assisted Solution

    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)
    LVL 40

    Expert Comment

    >> 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.
    LVL 27

    Expert Comment

    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.
    LVL 8

    Expert Comment

    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

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now