Good way of generating guaranteed unique ids?

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.

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


do{
        $id=rand()%1000000000;
        $result=mysql_query("Select * from MyTable where id=$id;",$conn);
}while(mysql_num_rows($result)>0);


If you wish to use the random string function, then replace rand() with it in the above.

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?

Thanks

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

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

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.

Regards,
Jon500