在插入之前检查预先存在的记录的最快方法 [mysql_errno()]

2021-12-26 00:00:00 error-handling insert php mysql unique-constraint

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

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

通常在注册新用户(包括插入他/她的电子邮件)之前，我会检查他/她的电子邮件是否已存在于数据库中，如下所示:

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
}

既然我已经将我的用户表中的 email 字段限制为 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 应该是什么.这些是我能找到的唯一错误代码:

The problem is that I don't know what $insert_unique_error should be. These are the only error codes I could find:

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

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

Class = '00' 表示成功.

Class = '00' indicates success.

Class = '01' 表示警告.

Class = '01' indicates a warning.

Class = '02' 表示未找到".这与游标上下文相关，用于控制游标到达数据集末尾时发生的情况.对于不检索任何行的 SELECT ... INTO var_list 语句，也会出现这种情况.

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.

类 >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();

这将导致对数据库的单一查询并排除 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

相关文章