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.

1
29
2/15/2010 5:21:11 PM

Accepted Answer

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
14
9/24/2009 7:49:11 AM

I use UUID() to create a unique value.

example:

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

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon