MySQL insert unique technique

Question

I have a php application that inserts a data into MySQL, which contains a randomly-generated unique value. The string will have about 1 billion possibilities, with probably no more than 1 or 2 million entries at any one time. Essentially, most combinations will not exist in the database.

I'm trying to find the least expensive approach to ensuring a unique value on insert. Specifically, my two options are:

Have a function that generates this unique ID. On each generation, test if the value exists in the database, if yes then re-generate, if no, return value.
Generate random string and attempt insert. If insert fails, test error is 1062 (MySQL duplicate entry X for key Y), re-generate key and insert with new value.

Is it a bad idea to rely upon the MySQL error for re-trying the insert? As I see it, the value will probably be unique, and it seems the initial (using technique 1) would be unnecessary.

EDIT #1

I should have also mentioned, the value must be a 6 character length string, composed of either uppercase letters and/or numbers. They can't be incremental either - they should be random.

EDIT #2

As a side note, I'm trying to create a redemption code for a gift certificate that is difficult to guess. Using numbers and letters creates 36 possibilities for each character, instead of 10 for just numbers or 26 for just 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 auto incremented. affected_rows() will equal 1 if the insert is successful:

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

Side discussion: I have met this kind of problem too. A nice solution was to use a random string combined with a timestamp. As a result, the string can't be guessed, and you ensure that there is no collision when generating the string/inserting in database. — Matthieu Napoli
– Matthieu Napoli, Commented Feb 18, 2012 at 23:35
A rule of thumb, when deciding how big to make your random unique id. Either, make it so big that there is no point to looking for duplicates (there is a higher chance that the hardware is defective and the check gives the wrong answer, than there is of a duplicate). Or make it small enough that duplicates happen all the time (so you can test your code in finite time), and have a check to deal with it. — ctrl-alt-delor
– ctrl-alt-delor, Commented Apr 5, 2013 at 11:30

Cerad · Accepted Answer · 2012-02-18 23:29:29Z

2

Is it a bad idea to rely upon the MySQL error for re-trying the insert?

Nope. Go ahead an use it if you want. In fact many people think if you check and if it doesn't exist then it's safe to insert. But unless you lock the table it's always possible that another process might slip in and grab the id.

So go ahead generate a random id if it suits your purpose. Just make sure you test your code so it does properly handle dups. Might also be useful to log dups just to ensure your assumptions about how unlikey dups are to occur are correct.

answered Feb 18, 2012 at 23:29

Cerad

49k9 gold badges92 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

biziclop · Accepted Answer · 2012-02-18 23:48:44Z

0

Define your table with unique constraint:

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

answered Feb 18, 2012 at 23:48

biziclop

14.6k3 gold badges52 silver badges66 bronze badges

1 Comment

Matthieu Napoli Over a year ago

He already uses that, see the question (MySQL duplicate entry X for key Y)

ron tornambe · Accepted Answer · 2012-02-18 22:12:47Z

-1

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

answered Feb 18, 2012 at 22:12

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

2 Comments

Matthieu Napoli Over a year ago

Because AUTO_INCREMENT generates following numbers, not random (and not containing letters)

ron tornambe Over a year ago

I guess I just don't understand why you would want to do this?

Collectives™ on Stack Overflow

MySQL insert unique technique

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related