检查重复条目与使用PDO errorInfo结果 [英] check for duplicate entry vs use PDO errorInfo result

问题描述

我有一个MySQL表，其中包含一个定义为唯一的电子邮件地址字段.对于此示例，假设我的所有表单所做的就是允许用户将其电子邮件地址插入表格中.

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)在执行插入操作之前，运行快速的SELECT语句.如果select返回结果，请通知用户，不要运行INSERT语句.

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)运行INSERT语句，并检查重复的输入错误

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.

此外，我很好奇听到有关编码样式的想法.我的心说:成为一名防御性程序员！在插入之前检查数据！"而我的大脑说:嗯，也许最好让MySQL为您检查数据".

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.

推荐答案

INSERT +检查状态应该是更好的方法.使用SELECT + INSERT，您可以让另一个线程在SELECT和INSERT之间插入相同的值，这意味着您还需要将这两个语句包装在表锁中.

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.

很容易在编码中犯过多的错误. Python的谚语是:请求宽恕比请求许可容易"，而且这种哲学并不是真正针对Python的.

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.

这篇关于检查重复条目与使用PDO errorInfo结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！