How to generate unique id in MySQL?

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.

I use UUID() to create a unique value.

example:

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

You may like the way that we do it. I wanted a reversible unique code that looked "random" -a fairly common problem.

• We take an input number such as 1,942.
• Left pad it into a string: "0000001942"
• Put the last two digits onto the front: "4200000019"
• Convert that into a number: 4,200,000,019

We now have a number that varies wildly between calls and is guaranteed to be less than 10,000,000,000. Not a bad start.

• Convert that number to a Base 34 string: "2oevc0b"
• Replace any zeros with 'y' and any ones with 'z': "2oevcyb"
• Upshift: "2OEVCYB"

The reason for choosing base 34 is so that we don't worry about 0/O and 1/l collisions. Now you have a short random-looking key that you can use to look up a LONG database identifier.

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

How you generate the unique_ids is a useful question - but you seem to be making a counter productive assumption about when you generate them!

My point is that you do not need to generate these unique id's at the time of creating your rows, because they are essentially independent of the data being inserted.

What I do is pre-generate unique id's for future use, that way I can take my own sweet time and absolutely guarantee they are unique, and there's no processing to be done at the time of the insert.

For example I have an orders table with order_id in it. This id is generated on the fly when the user enters the order, incrementally 1,2,3 etc forever. The user does not need to see this internal id.

Then I have another table - unique_ids with (order_id, unique_id). I have a routine that runs every night which pre-loads this table with enough unique_id rows to more than cover the orders that might be inserted in the next 24 hours. (If I ever get 10000 orders in one day I'll have a problem - but that would be a good problem to have!)

This approach guarantees uniqueness and takes any processing load away from the insert transaction and into the batch routine, where it does not affect the user.