如何检查DB中的重复条目？ [英] How to check for duplicate entry in DB?
问题描述
我在网站上构建了一个表单，它捕获了一些字段，包括'email'，并将其发布到一个数据库，我使用一个名为'data'的表。我使用jQuery验证插件，以确保所有字段是完整的，虽然我不能在db中有重复的电子邮件地址。
I've built a form on a site which captures a few fields including 'email' and posts it to a DB that I have with a table named 'data.' I'm using the jQuery Validate plugin to make sure all fields are complete, though I can't have duplicate email addresses in the db.
该插件附带了一个名为emails.php的脚本，在验证字段数据时调用此脚本，代码如下：
The plugin came with a script named emails.php which it calls when verifying the field data and here's the code:
<?php
$request = trim(strtolower($_REQUEST['email']));
$emails = array('steam@valve.com', 'bill@gates.com', 'blocked@email.com');
$valid = 'true';
foreach($emails as $email) {
if( strtolower($email) == $request )
$valid = '"Thats already taken."';
}
echo $valid;
?>
此片段仅检查列出的电子邮件。有没有办法查询数据库来搜索表'数据'，并检查电子邮件，看看是否有重复，并希望发布错误消息，如果是？
This snippet is only checking the emails that are listed. Is there a way to query the DB to search through the table 'data' and check the 'emails' to see if there's a duplicate and hopefully post an error message if it is?
此外，脚本似乎工作，当我使用任何示例电子邮件，所以它本质上是工作我只是需要调整它检查数据库。
Also, the script does seem to work when I use any of the example emails so it is essentially 'working' I just need to tweak it to check the DB.
推荐答案
不要使用有缺陷的SELECT before INSERT方法。除非你在一个全局事务中，否则你的SELECT和后续的INSERT之间总会有一个竞争条件，我怀疑是这种情况。
Do not use a flawed "SELECT before INSERT" approach. There will always be a race condition between your "SELECT" and a subsequent "INSERT" unless you're in a global transaction, which I doubt is the case here.
您应该对电子邮件列设置UNIQUE约束，然后尝试插入新记录并捕获唯一性SQL错误。你可以通过检查错误代码来捕获错误，对于MySQL，例如它是1062或23000取决于驱动程序。
You should put a UNIQUE constraint on the email column and then simply attempt to INSERT the new record and catch the uniqueness SQL error. You can catch the error by checking the error code, for MySQL for example it is 1062 or 23000 depending on the driver.
这篇关于如何检查DB中的重复条目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
