php 检查重复条目与使用 PDO errorInfo 结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10774943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
check for duplicate entry vs use PDO errorInfo result
提问by JoshBramlett
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.
我有一个 MySQL 表，它有一个定义为唯一的电子邮件地址字段。在这个例子中，假设我的表单所做的只是允许用户将他们的电子邮件地址插入表格中。
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 SELECTstatement before performing the insert. If the select returns results, inform the user, and do not run the INSERTstatement.
1)SELECT在执行插入之前运行快速语句。如果选择返回结果，请通知用户，并且不要运行该INSERT语句。
2) Run the INSERTstatement, and check for a duplicate entry error
2) 运行该INSERT语句，并检查重复输入错误
// 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 everyinsert, 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'.
另外，我很想知道关于编码风格的想法。我的心在说‘做一个防御性的程序员！插入前检查数据！而我的大脑却在说“嗯，也许让 MySQL 为您检查数据会更好”。
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.
编辑- 请注意，这不是“我该怎么做”的问题，而是“我为什么要以特定方式这样做”的问题。我包含的小代码片段有效，但我很好奇的是解决问题的最佳方法。
采纳答案by lanzz
INSERT+ check status should be a better approach. With SELECT+ INSERTyou can have another thread insert the same value between the SELECTand the INSERT, which means you would need to also wrap those two statements in a table lock.
INSERT+ 检查状态应该是更好的方法。使用SELECT+INSERT您可以让另一个线程在 theSELECT和 the之间插入相同的值INSERT，这意味着您还需要将这两个语句包装在一个表锁中。
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.
编码中的过多防御很容易出错。Python 有句谚语“请求宽恕比请求许可更容易”，这种哲学并不是真正特定于 Python 的。
回答by Dan LaManna
You could be executing this with a try catch block:
您可以使用 try catch 块执行此操作：
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.
您还可以研究诸如“INSERT IGNORE”和“INSERT... ON DUPLICATE KEY UPDATE”之类的替代方案-尽管我认为这些是特定于 MySQL 的，并且会违背使用 PDO 的可移植性，如果这是您所担心的.
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.
编辑：为了更正式地回答你的问题，对我来说，完全使用的解决方案#1（防御性程序员）首先有效地消除了唯一约束的点。所以我同意你让 MySQL 负责数据检查的想法。
回答by Boris Dalstein
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 KEYand 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.
请注意，如果您有一个 AUTO_INCREMENT 列并且您正在使用 InnoDB，那么失败的 INSERT 会增加“下一个自动 ID”值，尽管没有添加新行。例如，请参阅InnoDB 中INSERT ... ON DUPLICATE KEY和AUTO_INCREMENT 处理的文档。这会导致 AUTO_INCREMENT id 中出现间隙，如果您认为可能用完 id，这可能是一个问题。
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:
因此，如果您希望尝试插入已存在的行很常见，并且您希望尽可能避免 AUTO_INCREMENT id 中的间隙，您可以同时进行先发制人检查和异常处理：
$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.
这种方法使代码健壮，同时仍然避免在 AUTO_INCREMENT id 中创建间隙，除非在极少数情况下竞争条件。如果尝试插入现有电子邮件比尝试插入新电子邮件更常见，这也是最快的方法。如果尝试插入现有电子邮件的情况很少见，并且您不关心 AUTO_INCREMENT id 中的间隙，则无需先发制人检查。
回答by ricwambugu
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.
对我有用的一种更简单的方法是根据一组重复的状态代码检查错误代码。这样你就不必担心 PDO 返回什么。
$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! :-)
谢谢！:-)
