DEV Community


Discussion on: Generate a unique random code to store in DB

mmj profile image
MMJ Author • Edited

You gave me something to think about...

Let me ask you this: being in a MySql DB and setting the field unique, instead of looking for the code in the table, what about using a try-catch inserting the code and, if the error code refers to duplicate entry, do that again?
Something like this:

do {

  $res = false;
  $codeError = '';
  $code = generateCode(10);

  try {
    $stmt = $mysqli->prepare("INSERT INTO tablename (code) VALUES (?)");
    $stmt->bind_param("s", $code);
    $res = $stmt->execute();
  catch (exception $e) {
    $codeError = $e->getCode();
    echo "code with value {$code} not inserted <br>";

} while ($codeError === 1062);

if ($res) {
  echo "code with value {$code} inserted";
Enter fullscreen mode Exit fullscreen mode
Thread Thread
darkain profile image
Vincent Milum Jr

So a few things here to consider.

Firstly, if you're dealing with a single database instance, this will work. If you're dealing with a Galera instance, this should work. If you're dealing primary-replica style replication, this will also work. However, if you're doing primary-primary replication with writes to multiple nodes, this could still potentially lead to a race condition, so it is best to know what your replication topology is.

Secondly, using random data for keys works in small to medium scale (hundreds, thousands, maybe even a few million rows), but once you get beyond that, range locking within the database engine for the key starts to become a performance concern.

It is also important to take into consideration the data size. Only 10 characters will eventually fill up. And the closer to full you get, the longer it is going to take to find a unique value. Something to keep in mind as services scale upwards in ways not thought about at creation time. For instance, YouTube published articles about increasing their view counter on their videos from a 32-bit integer to a 64-bit integer, because Gangnam Style broke the 2 billion views mark.

str_shuffle is also listed as not cryptographically secure. These types of pseudo random generators are usually designed to be fast, and as such, may not even be able to produce every single possible value in a given set of constraints, so you may be hitting those collisions even sooner.

Thread Thread
mmj profile image
MMJ Author

Interesting points here, thanks!

Forem Open with the Forem app