問題描述
我的問題將以電子郵件為例，但這適用于任何事情.
My question will use emails as an example, but this could apply to anything.
通常在注冊新用戶(包括插入他/她的電子郵件)之前，我會檢查他/她的電子郵件是否已存在于數(shù)據(jù)庫中，如下所示:
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
}
既然我已經(jīng)將我的用戶表中的 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 應(yīng)該是什么.這些是我能找到的唯一錯誤代碼:
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' 表示未找到".這與游標(biāo)上下文相關(guān)，用于控制游標(biāo)到達(dá)數(shù)據(jù)集末尾時發(fā)生的情況.對于不檢索任何行的 SELECT ... INTO var_list 語句，也會出現(xiàn)這種情況.
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() 來檢查行數(shù)；
with a unique key on email field, then check row count with mysql_affected_rows();
這將導(dǎo)致對數(shù)據(jù)庫的單一查詢并排除 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
這篇關(guān)于在插入之前檢查預(yù)先存在的記錄的最快方法 [mysql_errno()]的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！