Quickest way to check for pre-existing record befo

问题:

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
}

---

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);
}

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

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

Class = '00' indicates success.

Class = '01' indicates a warning.

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' indicates an exception.

回答1:

Use

INSERT IGNORE INTO Users VALUES(...);

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

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

回答2:

I believe that COUNT is one of the quickest methods. Also remember when doing your queries, if you only want to test its existence don't do a

SELECT *

Instead specify a specific field

SELECT id

So you are not retrieving as much data. In the case of using COUNT:

SELECT COUNT(id) FROM Users WHERE email = 'emailaddress'

Please see below link on how to use COUNT in a result set

http://www.tizag.com/mysqlTutorial/mysqlcount.php