插入前检查数据库中是否存在行 [英] Check if row exists in the database before inserting

问题描述

$DBH = new PDO($dsn, $username, $password, $opt);

$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$STH = $DBH->prepare("INSERT INTO requests (id,imdbid,msg) VALUES ('',:imdbid,:msg)");
$STH->bindParam(':imdbid', $_POST['imdbid']);
$STH->bindParam(':msg', $_POST['msg']);

$STH->execute();
echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";

是否有一些 SQL 查询将检查和插入或什么?我需要它来检查用户输入的内容是否已经在数据库中，所以如果用户输入的 imdbid 已经存在，那么它就不会继续插入任何内容.我该怎么做?我知道我可以做一个 fetch_all 并为它做一个 foreach 但这不是只有在你执行后才有效吗?

Is there either some SQL Query that will check and insert or what? I need it to check if whatever the user typed is already in the db so if the user typed in a imdbid that is already there then it wont continue inserting anything. How would I do this? I know I can do a fetch_all and make a foreach for it but doesnt that only work after you execute?

推荐答案

最好在列上设置约束以防止重复数据，而不是检查和插入.

It's better to set a constraint on your columns to prevent duplicate data instead of checking and inserting.

只需在 imdbid 上设置一个 UNIQUE 约束:

Just set a UNIQUE constraint on imdbid:

ALTER TABLE `requests` ADD UNIQUE `imdbid_unique`(`imdbid`);

这样做的原因是您不会遇到竞争条件.

The reason for doing this is so that you don't run into a race condition.

在完成检查和实际插入数据之间有一个小窗口，在那个小窗口中，可能插入的数据会与要插入的数据发生冲突.

There's a small window between finishing the check, and actually inserting the data, and in that small window, data could be inserted that will conflict with the to-be-inserted data.

解决方案?使用约束并检查 $DBH->error() 是否存在插入错误.如果有任何错误，您就知道存在重复项，然后您可以通知您的用户.

Solution? Use constraints and check $DBH->error() for insertion errors. If there are any errors, you know that there's a duplicate and you can notify your user then.

我注意到你正在使用这个，$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);.在这种情况下，您不需要检查 ->error() 因为 PDO 会抛出异常.只需像这样用 try 和 catch 包裹你的执行:

I noticed that you are using this, $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);. In this case, you don't need to check ->error() because PDO will throw an exception. Just wrap your execute with try and catch like this:

$duplicate = false;

try {
    $STH->execute();
} catch (Exception $e) {
    echo "<p>Failed to Request ".$_POST['imdbid']."!</p>";
    $duplicate = true;
}

if (!$duplicate)
    echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";

这篇关于插入前检查数据库中是否存在行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！