I have a MySQL table which has a field for email addresses which is defined as unique. For this example, let's say that all my form does is allow a user to insert their email address into the table.

Since the email field is unique, the query should fail should they try to enter the same email twice. I'm curious about the trade-offs between between the two scenarios:

1) Run a quick SELECT statement before performing the insert. If the select returns results, inform the user, and do not run the INSERT statement.

2) Run the INSERT statement, and check for a duplicate entry error

// snippet uses PDO
if (!$prep->execute($values))
{
    $err = $prep->errorInfo();
    if (isset($err[1]))
    {
        // 1062 - Duplicate entry
        if ($err[1] == 1062)
            echo 'This email already exists.';
    }
}

Also, please assume normal use, meaning that duplicate entries should be minimal. Therefore, in the first scenario you obviously have the overhead of running an additional query for every insert, whereas in the second you're relying on error handling.

Also, I'm curious to hear thoughts on coding style. My heart says 'Be a defensive programmer! Check the data before you insert!' while my brain says 'Hmmm, maybe it's better to let MySQL take care of checking the data for you'.

EDIT - Please note this isn't a "How do I do this" question, but rather a "Why should I do this a particular way" question. The little code snippet I included works, but what I'm curious about is the best way to solve the problem.

Answer

You could be executing this with a try catch block:

try {
   $prep->execute($values);
   // do other things if successfully inserted
} catch (PDOException $e) {
   if ($e->errorInfo[1] == 1062) {
      // duplicate entry, do something else
   } else {
      // an error other than duplicate entry occurred
   }
}

You could also look into alternatives such as "INSERT IGNORE", and "INSERT... ON DUPLICATE KEY UPDATE" - though I think those are MySQL specific and would go against the portability of using PDO, if that's something you're concerned about.

Edit: To more formally answer your question, to me, solution #1 (the defensive programmer) in full usage effectively eliminates the point of the unique constraint in the first place. So I would agree with your thought of letting MySQL take care of data checking.

  • 56
Reply Report
      • 1
    • @aron.duby: Yes, but like many other cases with the disastrous PHP docs, the PDOException::getCode() method is actually completely undocumented, and this functionality is bizarrely described in the main PDOException class docs, in the description of the protected $code property (which would be completely inaccessible from outside the PDOException class, so nobody would try to look for documentation about it).
      • 1
    • PDOException->errorInfo[1] is also MySQL-specific — it is Driver-specific error code, according to PHP docs. You need to check agains PDOException->errorInfo[0], which is SQLSTATE code, which should be more or less (though often less) portable.

INSERT + check status should be a better approach. With SELECT + INSERT you can have another thread insert the same value between the SELECT and the INSERT, which means you would need to also wrap those two statements in a table lock.

It is easy to err on the side of too much defense in your coding. Python has the saying that "it is easier to ask for forgiveness than to ask for permission", and this philosophy is not really Python-specific.

  • 11
Reply Report

Be aware that if you have an AUTO_INCREMENT column and you are using InnoDB, then a failed INSERT does increment the "next auto-id" value, despite no new row being added. See for example the documentation for INSERT ... ON DUPLICATE KEY and AUTO_INCREMENT Handling in InnoDB. This leads to gaps in AUTO_INCREMENT ids, which may be a concern if you think you might run out of ids.

So if you expect that attempting to insert an already existing row is common, and you want to avoid gaps in AUTO_INCREMENT ids as much as possible, you can do both pre-emptive checking and exception handling:

$already_exists = false;
$stmt = $pdo->prepare("SELECT id FROM emails WHERE email = :email");
$stmt->execute(array(':email' => $email));
if ($stmt->rowCount() > 0) {
    $already_exists = true;
}
else {
    try {
        $stmt = $pdo->prepare("INSERT INTO emails (email) VALUES (:email)");
        $stmt->execute(array(':email' => $email));
    } catch (PDOException $e) {
        if ($e->errorInfo[1] == 1062) {
            $already_exists = true;
        } else {
            throw $e;
        }
    }
}

The first query ensures that we do not attempt to insert the email if we know for sure that it already exists. The second query attempts to insert the email if it seems not to exist yet. We still need to check for exceptions in the second query since a duplicate may still occur in the unlikely case of a race condition (multiple clients or threads running the snippet above in parallel).

This approach makes the code robust, while still avoiding to create gaps in AUTO_INCREMENT ids except in the rare case of race conditions. It is also the fastest approach if attempting to insert an existing email is more common than attempting to insert an new email. If attempting to insert an existing email is rare, and you don't care about gaps in AUTO_INCREMENT ids, then there is no need for the pre-emptive check.

  • 2
Reply Report

A simpler way that works for me is to check the error code against an array of duplicate status codes.That way you dont have to worry what PDO returns.

$MYSQL_DUPLICATE_CODES=array(1062,23000);
try {
   $prep->execute($values);
   // do other things if successfully inserted
} catch (PDOException $e) {
   if (in_array($e->getCode(),$MYSQL_DUPLICATE_CODES)) {
      // duplicate entry, do something else
   } else {
      // an error other than duplicate entry occurred
   }
}

Thanks! :-)

  • 0
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions