MySQL inserts a unique technique

Question

I have a php application that inserts data into MySQL that contains a random generated unique value. A line will have about 1 billion possibilities, possibly no more than 1 or 2 million records at any given time. In fact, most combinations will not exist in the database.

I am trying to find the least expensive approach to providing unique value when pasting. In particular, my two options are:

• Have a function that generates this unique identifier. In each generation, check if a value exists in the database, if so, then regenerate, if not, the return value.
• Generate a random string and insert an attempt. If the insert fails, the test error is 1062 (MySQL duplicates the X record for key Y), regenerates the key, and inserts with the new value.

Is it okay to rely on a MySQL error to retry the insertion? As I see it, the value is likely to be unique, and it seems that the original (using technique 1) is not needed.

EDIT NO. 1

I should also mention, the value should be 6 characters long, consisting of capital letters and / or numbers. They also cannot be incremental - they must be random.

EDIT No. 2

As a side note, I'm trying to create a redemption code for a gift certificate that is hard to guess. Using numbers and letters creates 36 possibilities for each character, instead of 10 for just numbers or 26 for simple letters.

Here's a stripped down version of the solution I created. The first value entered in the table is the primary key, which is automatically incremented. affected_rows () will be 1 if the insert was successful:

$code = $build_code(); while ((INSERT INTO certificates VALUES ('', $code) ON DUPLICATE KEY UPDATE pk = pk) && affected_rows() == 0) $code = $build_code(); 

Answer 1

Is it okay to rely on a MySQL error to retry the insertion?

Nope. Use it if you want. In fact, many people think that if you check, and if it does not exist, then it is safe to insert. But if you do not lock the table, it is always possible that another process can slip through and capture the identifier.

So, keep generating a random identifier if it matches your purpose. Just make sure you check your code so that it handles duplicates correctly. It may also be useful to record duplicates just to make sure your assumptions about how duplexey errors should occur are correct.

Answer 2

Define a table with a unique constraint:

http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html

Answer 3

Why not just use: "YourColName BIGINT AUTO_INCREMENT PRIMARY KEY" to ensure uniqueness?