插入之前检查预先存在的记录的最快方法[mysql_errno（）] [英] Quickest way to check for pre-existing record before insert [mysql_errno()]

问题描述

我的问题将以电子邮件为例，但这可能适用于任何事情。

My question will use emails as an example, but this could apply to anything.

通常在注册新用户（包括插入他/她的电子邮件）之前，我检查他/她的电子邮件是否已经存在DB这样的东西：

Normally before registering a new user (including inserting his/her email) I check if his/her email already exists in the DB something like this:

$result = mysql_query("SELECT * FROM Users WHERE email = '".mysql_real_escape_string($email)"';");
if(!$result)
{
    die(mysql_error());
}

if(mysql_num_rows($result) > 0)
{
    die('<p>Email already in DB. <a....>Recover Email</a></p>');
}
else
{
    //insert new user data into Users table
}

---

由于我已经将我的Users表中的电子邮件字段限制在要是 UNIQUE 无论如何尝试首先插入，如果它失败，检查错误？这样的东西：

---

Since I'd have constrained the email field in my Users table to be UNIQUE anyway, wouldn't it be quicker to try to insert first and if it fails, check the error? Something like this:

$result = mysql_query(...);//insert new user data into Users table
if(!$result)
{
    if(mysql_errno()==$insert_unique_error)
    {
        $error = '<p>Email already in DB. <a....>Recover Email</a></p>';
    }
    else
    {
        $error = mysql_error();
    }
    die($die_str);
}

问题是我不知道什么 $ insert_unique_error 应该。这些是唯一的错误代码我可以找到：

SQLSTATE值的前两个字符表示错误类：

The first two characters of an SQLSTATE value indicate the error class:

类='00'表示成功。

Class = '00' indicates success.

Class ='01'表示警告。

Class = '01' indicates a warning.

Class = '02 '指示未找到。这在游标的上下文中是相关的，并且用于控制当光标到达数据集的末尾时会发生什么。

Class = '02' indicates "not found." This is relevant within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.

Class>'02'表示异常。

Class > '02' indicates an exception.

推荐答案

使用

INSERT IGNORE INTO Users VALUES(...);

在电子邮件字段上使用唯一键，然后使用mysql_affected_rows（）检查行数;

with a unique key on email field, then check row count with mysql_affected_rows();

这将导致对DB的单个查询，并排除SELECT和INSERT之间的时间窗口的竞争条件

This will result in a single query to the DB and rule out the race condition of the time window between SELECT and INSERT

这篇关于插入之前检查预先存在的记录的最快方法[mysql_errno（）]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！